0

I have a query with a subquery, both are fast but when combined they take forever (meaning whatever time above what is acceptable in my application). I tried using 'explain' to see what's going on, it didn't help me much. What other ways are there to investigate slow queries?

Below are the specific details of my problem. I have a list of products, I want to find the date of the first vendor event for each product. If I get the list of event IDs, the query goes fast (each product might have multiple events, they are created in chronological order so min event ID is the earliest; ProdID is indexed); then I use this list of event IDs to get the date, this second query goes fast too (event ID is the primary key, therefore indexed). But if I use the first query as a subquery of the second, it takes forever.

Query 1:

mysql> select min(VendorEventID) from Vendor_Events where ProdID in (598446938, 598446984, 598447024, 598447054, 598447311, 598447523, 598447764, 598447778, 
598448000, 598448048) and vendorid = 12 group by ProdID ;
+--------------------+
| min(VendorEventID) |
+--------------------+
|        11217790121 |
|        11217792453 |
|        11217793912 |
|        11217793894 |
|        11217794825 |
|        11217815018 |
|        11217813148 |
|        11217828936 |
|        11217830215 |
|        11217829202 |
+--------------------+
10 rows in set (0.00 sec)

Query 2:

mysql> select * from Vendor_Events where VendorEventID in ( 11217790121, 11217792453, 11217793912, 11217793894, 11217794825, 11217815018, 11217813148, 11217828936, 11217830215, 11217829202 ) ;
+---------------+----------+-----------+---------------------+------------+--------+
| VendorEventID | VendorID | ProdID    | LoggedDate          | VendorType | NameID |
+---------------+----------+-----------+---------------------+------------+--------+
|   11217790121 |       12 | 598446938 | 2016-12-07 16:33:58 |          2 |     32 |
|   11217792453 |       12 | 598446984 | 2016-12-07 16:34:14 |          2 |     32 |
|   11217793894 |       12 | 598447054 | 2016-12-07 16:34:29 |          2 |     32 |
|   11217793912 |       12 | 598447024 | 2016-12-07 16:34:29 |          2 |     32 |
|   11217794825 |       12 | 598447311 | 2016-12-07 16:34:33 |          2 |     32 |
|   11217813148 |       12 | 598447764 | 2016-12-07 16:37:10 |          2 |     32 |
|   11217815018 |       12 | 598447523 | 2016-12-07 16:37:24 |          2 |     32 |
|   11217828936 |       12 | 598447778 | 2016-12-07 16:39:54 |          2 |     32 |
|   11217829202 |       12 | 598448048 | 2016-12-07 16:39:55 |          2 |     32 |
|   11217830215 |       12 | 598448000 | 2016-12-07 16:40:04 |          2 |     32 |
+---------------+----------+-----------+---------------------+------------+--------+
10 rows in set (0.04 sec)

Explain them combined:

mysql> explain  select * from Vendor_Events where VendorEventID in ( select min(VendorEventID) from Vendor_Events where ProdID in (598446938, 598446984, 598447024, 598447054, 598447311, 598447523, 598447764, 598447778, 598448000, 598448048) and vendorid = 12 group by ProdID ) ;
+----+--------------------+---------------+-------+-----------------+----------+---------+------+----------+--------------------------+
| id | select_type        | table         | type  | possible_keys   | key      | key_len | ref  | rows     | Extra                    |
+----+--------------------+---------------+-------+-----------------+----------+---------+------+----------+--------------------------+
|  1 | PRIMARY            | Vendor_Events | ALL   | NULL            | NULL     | NULL    | NULL | 78536406 | Using where              |
|  2 | DEPENDENT SUBQUERY | Vendor_Events | range | ProdID,ProdID_2 | ProdID_2 | 16      | NULL |       15 | Using where; Using index |
+----+--------------------+---------------+-------+-----------------+----------+---------+------+----------+--------------------------+
2 rows in set (0.00 sec)

Of course if anyone can help me figure out what's causing the query to be super slow, I'd appreciate. But if people want to tell me what other ways are there to investigate slow queries, I'd like that too, since we all face slow queries every now and then. ;-)

1

Why the query is slow:

explain's output shows the following:

The query will scan ALL rows from first table, 78536406, and for each of these rows, it is will scan an average of 15 rows from the second table. In total, (78536406*15) rows will be read. This is by itself is an indicator of slowness.

Taking into consideration the size of the tables, there might be some memory swap, which cause slowness as well (Reading from disk to RAM, repeatedly.)

How to improve this query:

One possible way would be to create a table out of the sub-query, and use INNER JOIN between the first table, and the table from the previous step.

CREATE TABLE tmp_ve as select min(VendorEventID) as VendorEventID from Vendor_Events where ProdID in (598446938, 598446984, 598447024, 598447054, 598447311, 598447523, 598447764, 598447778, 598448000, 598448048) and vendorid = 12 group by ProdID;
-- No need to add indexes to this table, as it would contain only 10 rows.
SELECT Vendor_Events.* FROM Vendor_Events INNER JOIN tmp_ve USING (VendorEventID);

I didn't try it, but if you explain this second query, it will start by the tmp_ve table, then for each value, it will use the index on Vendor_Events to fetch the corresponding row

HTH

  • 1
    I accepted your answer because it probably works, and it led me to my final solution anyway, but I didn't do what you said. Your comment led me to find out about 'subquery materialization', where mysql automagically does what you said. But that's 5.6 onwards, and I'm on 5.5. Anyway, with more thought and investigation I realized I could move my subquery to the 'FROM' part and join them, and that's what I did, and it works like a charm. Thanks for the help! – msb Dec 13 '16 at 19:06
1

Turn it inside-out.

Don't use IN ( SELECT ... ); it optimizes poorly.

Instead, use the subquery ("derived table") to get the few ids, then JOIN to the other table:

select *
    FROM ( select min(VendorEventID) AS minVE
              from Vendor_Events
              where ProdID in (598446938, 598446984, 598447024,
                               598447054, 598447311, 598447523,
                               598447764, 598447778, 598448000, 598448048)
         ) AS a
    JOIN Vendor_Events AS ve  ON ve.VendorEventID = a.minVE
    WHERE ve.vendorid = 12
    group by ve.ProdID;

You will need INDEX(vendorid, VendorEventID) (in either order).

  • lol, this is just what I said I did, in my comment to the other answer... :p I'll upvote anyway. :) – msb Dec 14 '16 at 20:35
  • (It is possible to move the 'accepted' flag to a better answer.) – Rick James Dec 14 '16 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.