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
Looks like this query creates a very huge temporary table on a disk. Could you please provide us with an EXPLAIN for this query for both - query with the “ORDER BY” and without it. Also, show index for both tables would help.
In general i have seen, that group by takes a long time with order by.
What alternatives are there. I don’t want to use group by and one of the scenario is similar to mention here For e.g. If there is Table 1 and Table 2
Table 1 has 3 unique URL (for e.g. hotmail.com) and Table 2 has 4 records of (hotmail.com)…The only way to display the combination for a particular URL is to use group by.
If I use inner join from Table 1 to table 2 ON URL, it still shows 4 records of that url, though i only want to show the first one if repeat record occur.
Regarding the query mention…
Explain with both ORDER BY and without is same
It uses index on sharelinkuser but doesn’t uses the index on
linkvouchcountsortingdisplay table