Announcement Module
No announcement yet.

Performance Issue with Group by

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Performance Issue with Group by


    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,linkvouchcountsorting display.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

  • #2
    Hi, ppanwar

    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.



    • #3
      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. and Table 2 has 4 records of ( 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