The table is 'software' and not 'softwares', so OCS >= 2.8.0.
In my case, we have ~7.000 hardware with a contact by day, ~12.000 hardware in table and ~9 M software. I confirm a delay between login and main page ... sometimes. If you leave and login again, it's immediate.
I suppose the delay is needed for this query and sometimes the data are in temp table (cache). I'm not a DBA (DataBase Administrator) but 3 (bad) ideas :
- a crontab with each hour a similar sql query search (without any usage) : could keep data in temp tables
- create a field in table 'software' and populate with 'concat(NAME_ID,'_',VERSION_ID)' with an index on this field, and modify the sql search : I think it could speed up the search
- count only for NAME_ID, means you doesn't count for each version of NAME_ID software