left join/inner join以后数据条数比原数据表多的原因及解决办法

 

页面一：

select vendor, application,applicationversion,filename ,fileversion, lastuse from `ASIA`.applications
where INSTR(vendor, "") and  INSTR(application , "") and  INSTR(filename, "") and lastuse< 1597741978 and lastuse> 1595149978
GROUP BY vendor ,application ,applicationversion ,filename
ORDER BY vendor desc LIMIT 10 OFFSET 0;                           # 没有join表

页面二：

select vendor ,application ,applicationversion ,fileversion,filename ,lastuse ,host.deviceid , hostname from `ASIA`.applications as app
left join  `ASIA`.hostinfo as host on app.deviceid=host.deviceid
where INSTR(hostname, "") and INSTR(vendor, "") and  INSTR(application , "") and  INSTR(applicationversion, "") and  INSTR(filename, "")
GROUP BY vendor ,application ,applicationversion ,filename
ORDER BY vendor desc LIMIT 10 OFFSET 0;              # 有join表

问题：第一个页面比第二个页面快，页面二查询太慢了

 

 

 

 

分析：

页面1聚合：耗时0.19s

select vendor, application,applicationversion,filename ,fileversion, lastuse from `ASIA`.applications
                            where INSTR(vendor, "") and  INSTR(application , "") and  INSTR(filename, "") and lastuse< 1