Why two mysql selects executed separately are much faster than one combined?

Question

I want to understand the case when I run two queries separately i takes around 400ms in total, but when I combined them using sub-select it takes around 12 seconds. I have two InnoDB tables:

event: 99 914 rows
even_prizes: 24 540 770 rows

Below are my queries:

SELECT 
  id
FROM 
  event e
WHERE
  e.status != 'SCHEDULED';
 -- takes 130ms, returns 2406 rows

SELECT 
 id, count(*) 
FROM 
 event_prizes 
WHERE event_id in (
    -- 2406 ids returned from the previous query
) 
GROUP BY
 id;
-- takes 270ms, returns the same amount of rows

From the other side when I run the query from below:

SELECT 
 id, count(*) 
FROM 
 event_prizes 
WHERE event_id in (
    SELECT 
      id
    FROM 
      event e
    WHERE
      e.status != 'SCHEDULED'
) 
GROUP BY
 id;
-- takes 12seconds

I guess in the second case MySQL makes the full-scan of the event_prizes table ? Is there any better way to create a single query for this case ?

As well as SHOW CREATE TABLE statements for all relevant tables, questions about query performance always require the EXPLAIN for the given query — Strawberry, Apr 17 '20 at 07:14
Please check comments in: https://stackoverflow.com/questions/5018284/mysql-in-queries-terribly-slow-with-subquery-but-fast-with-explicit-values — Jatinder Kumar, Apr 17 '20 at 07:16
Does this answer your question? [MySQL "IN" queries terribly slow with subquery but fast with explicit values](https://stackoverflow.com/questions/5018284/mysql-in-queries-terribly-slow-with-subquery-but-fast-with-explicit-values) — Jatinder Kumar, Apr 17 '20 at 08:00

Sebastian Brosch · Answer 1 · 2020-04-17T07:29:30.770

0

You can use a INNER JOIN instead of a sub-select:

SELECT ep.id, COUNT(*) 
FROM event_prizes ep INNER JOIN event e ON ep.event_id = e.id 
WHERE e.status <> 'SCHEDULED'
GROUP BY ep.id

Make sure you are using

a PRIMARY KEY on event.id
a PRIMARY KEY on event_prizes.id
a FOREIGN KEY on event_prizes.event_id

You can also try the following indices at least:

event(status)

edited Apr 17 '20 at 07:29

answered Apr 17 '20 at 07:08

Sebastian Brosch

42,106
15
72
87

But as always, the key to this is indexes – Strawberry Apr 17 '20 at 07:13
I've tried that and to get the end result it takes around 9 seconds - still It cannot be compare to 400ms .. – tomgal Apr 17 '20 at 07:14
@tomgal - Please provide `SHOW CREATE TABLE` for each table so we can dig deeper. – Rick James Apr 18 '20 at 20:26

Why two mysql selects executed separately are much faster than one combined?

1 Answers1