MySQL Query taking over 6 seconds

Question

Awhile back I got some help with a specific query. Here's the link: SQL Group BY using strings in new columns

My query looks similar to this:

SELECT    event_data, class_40_winner, class_30_winner
FROM      events e
LEFT JOIN (SELECT result_event, name AS class_40_winner
       FROM   results 
       WHERE  class = 40 AND position = 1) c40 ON e.id = c40.result_event
LEFT JOIN (SELECT result_event, name AS class_30_winner
       FROM   results 
       WHERE  class = 30 AND position = 1) c30 ON e.id = c30.result_event

I have now entered enough data in my database (22,000 rows) that this query is taking over 6 seconds to complete. (My actual query is bigger than the above, in that it now has 4 joins in it.)

I used the "Explain" function on my query to take a look. Each of the queries from the "results" table is pulling in the 22,000 rows, so this seems to be the problem.

I have done some research and it sounds like I should be able to INDEX the relevant column on the "results" table to help speed things up. But when I did that, it actually slowed my query down to about 10 seconds.

Any suggestions for what I can do to improve this query?

This solution conflates an issue of data retrieval with an issue with of data display. Handle the display issue in application code. — Strawberry
– Strawberry, Commented Jan 14, 2017 at 7:27

shA.t · Accepted Answer · 2017-01-14 09:27:40Z

2

AFAIK, you are pivoting your data and I think using max(case ...) ... group by has good performance in pivoting data.
I can suggest you to use this query instead:

select event_date
    , max(case when r.class = 40 then name end) `Class 40 Winner`
    , max(case when r.class = 30 then name end) `Class 30 Winner`
from events e
left join results r on e.event_id = r.result_event and r.position = 1
group by event_date;

[SQL Fiddle Demo]

edited Jan 14, 2017 at 9:27

answered Jan 14, 2017 at 9:21

shA.t

17k5 gold badges59 silver badges121 bronze badges

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

6 Comments

RichieW13 Over a year ago

This one looks like it might work. What is the "max" function doing in this expression? Also, if I have another restriction in the query that I want to apply to all the results, would I just add it before the "group by" statement? Such as: . where extract(year from e.event_date)='2016'

shA.t Over a year ago

The combination of max and case do the trick, case results will have some null values then max will discard them - and you can add any where statement before group by - HTH ;).

RichieW13 Over a year ago

Hopefully last question. "name" is actually on a separate table of "entrants". So instead of "name" I have "entrant_id" in the query. Where do I put the JOIN to join the results.entrant_id field to the entrants.id field?

shA.t Over a year ago

Simply add it to query after from events e ;).

RichieW13 Over a year ago

I tried that, but it only grabs the name from one of the columns. SQL Fiddle: sqlfiddle.com/#!9/1135a/2

|

codeforester · Accepted Answer · 2017-01-15 03:15:06Z

1

Try this query:

SELECT
  e.event_date,
  r1.name as class_40_winner,
  r2.name as class_30_winner
FROM
  events e,
  results r1,
  results r2
WHERE
  r1.class = 40 AND
  r2.class = 30 AND
  r1.position = 1 AND
  r2.position = 1 AND
  r1.result_event = e.id AND
  r2.result_event = e.id

edited Jan 15, 2017 at 3:15

answered Jan 14, 2017 at 7:21

codeforester

43.8k21 gold badges122 silver badges159 bronze badges

1 Comment

RichieW13 Over a year ago

Thanks. This seems to get close to what I want. But if the "40" class is empty for an event, then no row is returned for that event. I tried changing the "AND" between 40 and 30 to an "OR" but that causes the query to freeze.

Strawberry · Accepted Answer · 2017-01-14 07:33:26Z

0

SELECT e.event_data
     , r.class
     , r.name winner
  FROM events e
  JOIN results r
     ON r.result_event = e.id 
 WHERE  class IN (30,40) 
   AND position = 1

The rest of this problem is a simple display issue, best resolved in application code.

answered Jan 14, 2017 at 7:33

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Collectives™ on Stack Overflow

MySQL Query taking over 6 seconds

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related