1

Two almost identical queries, but one is extremely slow. All tables are innodb. Explain statements attached, but I don't know how to resolve it.

userHits (table structure)

-id      - int - primary
-userId2 - int - indexed
-userId  - int - indexed

25 seconds:

SELECT  *
    FROM  usersMobile
    LEFT JOIN  
      ( SELECT  *
            FROM  userHits
      ) tx1 ON tx1.id=
      ( SELECT  id
            FROM  userHits
            WHERE  userId2=usersMobile.id
            ORDER BY  id DESC
            LIMIT  1
      ); 
+----+--------------------+-------------+-------+---------------+-------------+---------+------+--------+-------------+
| id | select_type        | table       | type  | possible_keys | key         | key_len | ref  | rows   | Extra       |
+----+--------------------+-------------+-------+---------------+-------------+---------+------+--------+-------------+
|  1 | PRIMARY            | usersMobile | ALL   | NULL          | NULL        | NULL    | NULL |    131 | NULL        |
|  1 | PRIMARY            | <derived2>  | ref   | <auto_key0>   | <auto_key0> | 8       | func |   1820 | Using where |
|  3 | DEPENDENT SUBQUERY | userHits    | index | userId2       | PRIMARY     | 8       | NULL |      2 | Using where |
|  2 | DERIVED            | userHits    | ALL   | NULL          | NULL        | NULL    | NULL | 238528 | NULL        |
+----+--------------------+-------------+-------+---------------+-------------+---------+------+--------+-------------+

1.8 seconds:

SELECT  *
    FROM  Users
    LEFT JOIN  
      ( SELECT  *
            FROM  userHits
      ) tx1 ON tx1.id=
      ( SELECT  id
            FROM  userHits
            WHERE  userId=Users.id
            ORDER BY  id DESC
            LIMIT  1
      ); 
+----+--------------------+------------+------+---------------+-------------+---------+---------------------------+--------+------------------------------------------+
| id | select_type        | table      | type | possible_keys | key         | key_len | ref                       | rows   | Extra                                    |
+----+--------------------+------------+------+---------------+-------------+---------+---------------------------+--------+------------------------------------------+
|  1 | PRIMARY            | Users      | ALL  | NULL          | NULL        | NULL    | NULL                      |   1042 | NULL                                     |
|  1 | PRIMARY            | <derived2> | ref  | <auto_key0>   | <auto_key0> | 8       | func                      |    228 | Using where                              |
|  3 | DEPENDENT SUBQUERY | userHits   | ref  | userId        | userId      | 5       | Users.id                  |    134 | Using where; Using index; Using filesort |
|  2 | DERIVED            | userHits   | ALL  | NULL          | NULL        | NULL    | NULL                      | 238528 | NULL                                     |
+----+--------------------+------------+------+---------------+-------------+---------+---------------------------+--------+------------------------------------------+

Edit: Also, if I change userId2 in the slow query to userId, it's done in under a second. MYSQL is acting like userId2 isn't indexed, but it is.

I added the index lookup as well:

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| userHits |          0 | PRIMARY  |            1 | id          | A         |      238553 |     NULL | NULL   |      | BTREE      |         |               |
| userHits |          1 | userId   |            1 | userId      | A         |        1780 |     NULL | NULL   | YES  | BTREE      |         |               |
| userHits |          1 | userId2  |            1 | userId2     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8
  • 1
    I ended up copying all userId to userId2, dropped the index and adding it again which changed the Cardinality. I believe MySQL was choosing not to use the index due to its low Cardinality, which then caused a 10-20x increase in select speed. Not really a solution to the problem though. :( Jul 2 '15 at 0:08
  • what does the userid2 used for? probably you need to redesign your table relations.
    – Masoud
    Jul 2 '15 at 1:47
  • 1
    Please provide SHOW CREATE TABLE for the three tables. SHOW INDEXES is not as useful.
    – Rick James
    Jul 2 '15 at 5:37
  • How many rows in each table?
    – Rick James
    Jul 2 '15 at 5:40
  • 1
    Please try to rewrite the query with JOINs, not subqueries.
    – Rick James
    Jul 2 '15 at 5:41
0

I will try to answer the question, but I suggest you listen to comments saying you should rethink your design a bit.

By my guess it is "order by .. limit" optimization gone wild - when you check userId2, mysql sees low cardinality, so thinks that going by the defined order through primary key will find one matching item really fast, but because some skew on real values it is not true and it has to scan big parts of the table to find the value (that skew cannot be seen in statistics), where in the fast case it knows there are only a handful of the right values so takes them all out and sorts them, which is fast enough for some hundred rows.

You could try "use index (userId2)" in the subquery to hint mysql about the better way, but dependent subquery is still bad for performance. If I understand it correctly, you try to show "last" match for a given user. The solution with LEFT JOIN as described in manual is the one I prefer in most cases.

0

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.