Hi,
We have a large deployment and with 2.6 version, we have a latency issue while searching through the inventory.
The default SQL request is:
SELECT SQL_CALC_FOUND_ROWS a.fields_3, a.TAG, h.lastdate, h.name, h.ID, h.userid, h.osname, h.memory as capa, h.processors, h.workgroup, h.osversion, h.oscomments, h.processort, h.processorn, h.swap, h.lastcome, h.quality, h.fidelity, h.description, h.wincompany, h.winowner, h.useragent, e.smanufacturer, e.bmanufacturer, e.ssn, e.smodel, e.bversion, GROUP_CONCAT(DISTINCT CASE WHEN n.ipaddress != 0x20 THEN n.ipaddress ELSE NULL END SEPARATOR 0x2D) as ipaddress, h.userdomain, h.ARCH, e.bdate, v.name as vname, ac.category_name, n.macaddr, n.ipmask, n.ipgateway, n.ipsubnet
from hardware h LEFT JOIN accountinfo a ON a.hardware_id=h.id
LEFT JOIN networks n ON n.hardware_id=h.id
LEFT JOIN bios e ON e.hardware_id=h.id
LEFT JOIN memories m ON m.hardware_id=h.id
LEFT JOIN assets_categories ac ON ac.ID = h.CATEGORY_ID
LEFT JOIN videos v ON v.hardware_id=h.id
where deviceid<>'_SYSTEMGROUP_' AND deviceid<>'_DOWNLOADGROUP_' AND n.STATUS = 'up'
group by h.id
order by a.fields_3 asc limit 0 , 10
This takes 8 to 12 seconds and mainly due to this step
| Copying To Tmp Table | 8.7 s |
If I remove the memories for the SQL request, the result is lower than 1 second.
Can you improve the default SQL request?
