Hi,
We have been working on a links service and currently testing the system with 30,000 records…
This query uses group by to find most vouched links and it is very slow…takes up to 10 sec. We have tried everything to optimize it but no help. The problem is putting order by on the calculated fields as we want to display mostvouched
select sum(vouch_count) as vouch_count,linkvouchcountsortingdisplay.url as turl,linkuserId,linkId,title,linkvouchcountsortingdisplay.ur l as url from linkvouchcountsortingdisplay INNER JOIN sharelinkuser ON linkvouchcountsortingdisplay.url=sharelinkuser.url where vouch_date>=DATE_ADD(CURRENT_DATE,INTERVAL -7 DAY) and vouch_date<=CURRENT_DATE group by url ORDER BY vouch_count DESC
As soon as we put order by it takes a long time. Without order by it works fine…
All the indexes are placed properly and they work with other queries