another slow query

[thompom]

Hi,

This a follow on from thread

http://www.tek-tips.com/viewthread.cfm?qid=1585242

Have a query that takes >30secs.
The response table has 50K records, event 8K and action has 11K

SELECT count(response.responseid) AS responsecount, event.userid 
FROM event 
  INNER JOIN action ON action.eventid = event.eventid 
  INNER JOIN response ON response.actionid = action.actionid 
  INNER JOIN 
    (SELECT eventid FROM action 
    GROUP BY eventid 
    HAVING 
      COUNT(CASE WHEN typeid = '1' THEN 'count me' END) > 0 
      AND COUNT(CASE WHEN typeid = '2' THEN 'no, me!!' END) > 0 ) 
  AS TA ON TA.eventid = event.eventid 
WHERE action.typeid = '1' 
AND date(action.actiondate) BETWEEN '20100801' AND '20100831' 
GROUP BY event.eventid 
ORDER BY event.eventid

can anyone see a way to improve this? please let me know if you need to see table structure

 

[Gruuuu]

I'm confused by your subquery. It asks for a list of eventids that have a count of typeid '1' and '2' that is greater than 0. Then your main WHERE clause only asks for events that have typeid of '1'.

If I am assuming correctly, the events in the action table can have rows with typeids of both 1 and 2?

Instead, have the subquery find events with typeid of '2', and join with events that have typeid of '1'.

Also you are grouping by a column which is not in your result set. Why?

SELECT count(response.responseid) AS responsecount, event.userid 
FROM event 
  INNER JOIN action ON action.eventid = event.eventid 
  INNER JOIN response ON response.actionid = action.actionid 
  INNER JOIN 
    (SELECT eventid FROM action 
    GROUP BY eventid 
    WHERE typeid = '2') 
  AS TA ON TA.eventid = event.eventid 
WHERE action.typeid = '1' 
AND date(action.actiondate) BETWEEN '20100801' AND '20100831'
GROUP BY event.userid 
ORDER BY event.userid

Does this give you the results you're looking for?

 

[thompom]

Hi - thanks for your reply

I did try this way but still takes more than 30secs to run

  INNER JOIN 
    (SELECT eventid FROM action 
    GROUP BY eventid 
    WHERE typeid = '2')

this query isnt grouped by userid as needs to be grouped by each event.

 

[Gruuuu]

this query isnt grouped by userid as needs to be grouped by each event.

Ok, but you aren't selecting the eventid? So how will you know which event has which number of responses? Ultimately not important to your query, but it just doesn't make sense.

Neither of these queries should be taking this long. Perhaps someone more well-versed in MySQL can shed some light.

My only other suggestion is to use IN() instead of BETWEEN for your dates, and try letting MySQL do the date conversion

AND action.actiondate IN ('20100801', '20100802', '20100803' ...etc

It's kind of clunky, but sometimes enumerating your date list can greatly improve performance.
If you need, you can keep an excel file or perhaps some other script that will convert all the days of some month to these strings and paste them in as you need.

 

[thompom]

So how will you know which event has which number of responses?

fot this report i dont need to know which event have the responses - just how many each event has

My only other suggestion is to use IN() instead of BETWEEN for your dates, and try letting MySQL do the date conversion

good idea - but the date selection appears to make no difference. if i remove:

  INNER JOIN 
    (SELECT eventid FROM action 
    WHERE typeid = '2'
    GROUP BY eventid)

then the query takes 1 sec!

 

[thompom]

after thinking about this, the only way (I can think of) to improve performance is to add another column in table event that flags if an order action has been created.
then I wouldnt need the INNER JOIN if action.typeid=2 statement.