MySql performance problems

Question

This query I have takes a whooping 45 seconds to execute. I have indexes on all fields that are being search.

SELECT SQL_CALC_FOUND_ROWS g.app_group_id, g.id as g_id, p.`id` as form_id, 
       a.`user`   as activity_user,a.`activity` as app_act ,a.* 
FROM grouped g 
INNER JOIN 
(SELECT max(id) as id, app_group_id FROM grouped GROUP BY app_group_id) g1 
ON g1.app_group_id = g.app_group_id AND g.id = g1.id 
INNER JOIN form p 
on p.id = g.id 
    INNER JOIN 
    (SELECT a.id, a.date_time, a.user, a.activity FROM log a) a 
    ON g.id = a.id 
WHERE p.agname like '%blahblah%' and p.`save4later` != 'y' 
      and a.activity = 'APP       Submitted' or a.activity = 'InstaQUOTE' 
ORDER BY app_group_id DESC limit 0, 100

In my explain it shows im using Using temporary; Using filesort

Indexes are:

activity table: PRIMARY activity_id INDEX date_time INDEX id INDEX activity INDEX user

form table: PRIMARY id INDEX id_md5 INDEX dateadd INDEX dateu INDEX agent_or_underwriter INDEX

grouped table: UNIQUE id INDEX app_group_id INDEX agent_or_underwriter save4later

Any advice is much appreciated

Thank you very much

Eh, try executing subqueries, remove joins/selectors and see where the bottleneck is. My guess is that one of your tables has a lot of records, isn't it? — Mihnea Simian
– Mihnea Simian, Commented Apr 19, 2012 at 15:04
Using filesort is slow. Can you show what indexes there are on the tables? — Dmytro Shevchenko
– Dmytro Shevchenko, Commented Apr 19, 2012 at 15:07
By the way, SQL_CALC_FOUND_ROWS is normally slower than two separate queries. — Dmytro Shevchenko
– Dmytro Shevchenko, Commented Apr 19, 2012 at 15:08
@user583576 also, for clarity, please edit your question and add your indexes there, rather than leaving them in the comments. — richsage
– richsage, Commented Apr 19, 2012 at 15:22

Dmytro Shevchenko · Accepted Answer · 2012-04-19 15:19:13Z

2

To start with, try this one:

SELECT
  g.app_group_id,
  g.id AS g_id,
  p.id AS form_id,
  a.user AS activity_user,
  a.activity AS app_act,
  a.id,
  a.date_time
FROM grouped g 
INNER JOIN 
  (SELECT MAX(id) AS id, app_group_id FROM grouped GROUP BY app_group_id) g1 
  ON g1.app_group_id = g.app_group_id AND g.id = g1.id 
INNER JOIN form p 
  ON p.id = g.id 
INNER JOIN log a 
  ON g.id = a.id 
WHERE p.agname LIKE '%blahblah%'
  AND p.save4later != 'y'
  AND a.activity IN('APP       Submitted', 'InstaQUOTE')
LIMIT 0, 100

I removed an unnecessary subquery. Also removed ORDER BY. I guess you could do without sorting, and that must speed the query up a lot.

I also removed SQL_CALC_FOUND_ROWS, because, as I mentioned earlier, it should be faster to issue a separate COUNT(*) query.

answered Apr 19, 2012 at 15:19

Dmytro Shevchenko

34.9k6 gold badges56 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user583576 Over a year ago

works great. executes in 1.8 seconds now. Thank you very much

user583576 Over a year ago

Upon further review it gives back duplicate records

user583576 Over a year ago

no i had no duplicate records in original query. it just took 45 seconds to execute

Dmytro Shevchenko Over a year ago

@user583576 try adding back parts I removed one by one and find out what makes the difference.

Dmytro Shevchenko Over a year ago

@user583576 actually I kind of hoped that you would write here what went wrong. I personally don't see how my changes could lead to duplicates showing up. So I'm interested in what you find in the end.

DRapp · Accepted Answer · 2012-04-19 15:36:17Z

Your where clause has an "Or" on the "a.Activity". Without ( ) around both activity, it is going through EVERYTHING, all P, G, G1 aliases. I am guessing that might be your bigger issue.

Additionally, I would ensure you have an index on your "Form" table with the ( Save4Later ) column indexed

I would update the query like this:

SELECT STRAIGHT_JOIN SQL_CALC_FOUND_ROWS 
      g.app_group_id, 
      g.id as g_id, 
      QualifyPages.Form_id, 
      a.`user` as activity_user,
      a.`activity` as app_act,
      a.* 
   FROM
      ( select p.ID as Form_ID
           from  FORM p
           WHERE p.`save4later` != 'y' 
             AND p.agname like '%blahblah%' ) QualifyPages

         JOIN Grouped g
            on QualifyPages.Form_ID = g.ID

            INNER JOIN 
            ( SELECT app_group_id,
                     max(id) as MaxIDPerGroup
                 FROM 
                    grouped 
                 GROUP BY 
                    app_group_id ) g1 
             ON g.app_group_id = g1.app_group_id
            AND g.id = g1.MaxIDPerGroup 

           INNER JOIN 
            ( SELECT a.id, a.date_time, a.user, a.activity 
                 FROM log a
                 WHERE a.activity = 'APP       Submitted' 
                    or a.activity = 'InstaQUOTE' ) a 
               ON g.id = a.id 
   ORDER BY 
      g.app_group_id DESC 
   limit 
      0, 100

Collectives™ on Stack Overflow

MySql performance problems

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related