CODEWITHSUNDEEP
mysqlsqlquery-optimization
stil
stil

Reputation: 5556

INNER JOIN causes query executes very long

I have quite big table with millions of records. The query below executes in just 0.2s, which is fine.

SELECT ch1.*
FROM citizens_history ch1
WHERE ch1.update_id_to = ( 
    SELECT MAX( ch2.update_id_to ) 
    FROM citizens_history ch2
    WHERE ch2.id = ch1.id
)
ORDER BY ch1.experience DESC
LIMIT 100

However, when I tried to add INNER JOIN, like this

SELECT ch1.*, upd.*
FROM citizens_history ch1
INNER JOIN updates upd ON upd.id = ch1.update_id_to
WHERE ch1.update_id_to = ( 
    SELECT MAX( ch2.update_id_to ) 
    FROM citizens_history ch2
    WHERE ch2.id = ch1.id
)
ORDER BY ch1.experience DESC
LIMIT 100

It takes forever to execute. In first situation I think MySQL is doing this:

  1. Orders by experience using index
  2. Applies WHERE
  3. Finishes when there is 100 records found

In second situation, I assume this happens:

  1. Orders by experience using index
  2. Joins for every single record out of the millions of rows <-- slow
  3. Applies WHERE
  4. Finishes when there is 100 records found

Do you have any advices how can I optimize this?

Edit: EXPLAIN for fast and slow query:

+----+--------------------+-------+-------+---------------+------------+---------+--------------+------+-------------+
| id | select_type        | table | type  | possible_keys | key        | key_len | ref                 | rows | Extra       |
+----+--------------------+-------+-------+---------------+------------+---------+--------------+------+-------------+
|  1 | PRIMARY            | ch1   | index | NULL          | experience | 3       | NULL         |  100 | Using where |
|  2 | DEPENDENT SUBQUERY | ch2   | ref   | id            | id         | 3       | db.ch1.id    |    1 | Using index |
+----+--------------------+-------+-------+---------------+------------+---------+--------------+------+-------------+

+----+--------------------+-------+-------+-------------------------------------+----------+---------+--------------+------+----------------------------------------------+
| id | select_type        | table | type  | possible_keys                       | key      | key_len | ref          | rows | Extra                                        |
+----+--------------------+-------+-------+-------------------------------------+----------+---------+--------------+------+----------------------------------------------+
|  1 | PRIMARY            | upd   | index | PRIMARY                             | datetime | 5       | NULL         |  389 | Using index; Using temporary; Using filesort |
|  1 | PRIMARY            | ch1   | ref   | PRIMARY,update_id_to,update_id_to_2 | PRIMARY  | 4       | db.upd.id    |  112 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | ch2   | ref   | id                                  | id       | 3       | db.ch1.id    |    1 | Using index                                  |
+----+--------------------+-------+-------+-------------------------------------+----------+---------+--------------+------+----------------------------------------------+

Upvotes: 2

Views: 109

Answers (3)

Justin
Justin

Reputation: 9724

You can try:

  SELECT ch1.*
  FROM citizens_history ch1
  LEFT JOIN citizens_history ch2
    ON ch2.id = ch1.id
    AND ch1.max_update_id_to > ch2.update_id_to
  WHERE ch1.max_update_id_to is null 
  ORDER BY ch1.experience DESC
  LIMIT 100

Upvotes: 0

Kickstart
Kickstart

Reputation: 21513

Performing the first stage as a JOIN to a sub query, and then joining on the updates table:-

SELECT ch1.*, upd.*
FROM citizens_history ch1
INNER JOIN 
(
    SELECT id, MAX( update_id_to ) AS max_update_id_to
    FROM citizens_history 
    GROUP BY id
) ch2 ON ch1.id = ch2.id AND ch1.update_id_to = ch2.max_update_id_to
INNER JOIN updates upd ON upd.id = ch1.update_id_to
ORDER BY ch1.experience DESC
LIMIT 100

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

Rewrite the first query as follows, and then modify as required...

SELECT ch1.*
  FROM citizens_history ch1
  JOIN 
     ( SELECT id
            , MAX(update_id_to) max_update_id_to 
         FROM citizens_history 
        GROUP 
           BY id
     ) ch2
    ON ch2.id = ch1.id
   AND ch2.max_update_id_to = ch1.update_id_to
 ORDER 
    BY ch1.experience DESC
 LIMIT 100

If you're still struggling (performancewise), provide an EXPLAIN for the above together with proper DDLS of all relevant tables.

Upvotes: 2

Related Questions