Single INNER JOIN of two well-indexed tables takes more than a minute to run

Question

I have a query that takes about 90 seconds to run even though the tables should have the right indexes. I don't understand why.

I am using MySQL and the tables are InnoDB.

This is the query:

SELECT count(*)
FROM `following_lists` fl INNER JOIN users u 
ON fl.user_uuid = u.user_uuid
WHERE fl.following_query_id = 1000010 AND u.status <= 2

I expect this query to start on the table following_lists, grab about 4K records as per the WHERE condition, join these records to the table users by its primary key, check the value of a field in the users table, and return the count of the resulting records. Why does it take so long? Could it be because the two fields I'm joining the tables by are CHAR(40) and not integers?

These are the tables involved and their indexes:

CREATE TABLE `users` ( 
  `user_uuid` CHAR(40) NOT NULL, 
  `status` TINYINT UNSIGNED NOT NULL, 
  ...

  PRIMARY KEY (`user_uuid`), 
  ...
)

CREATE TABLE `following_lists` ( 
  `following_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `following_query_id` INT UNSIGNED NOT NULL,
  `user_uuid` CHAR(40) NOT NULL,

  PRIMARY KEY (`following_id`), 
  KEY `query_id` (`following_query_id`),
  KEY `user_uuid` (`user_uuid`)
)

And this is the output of the explain query:

+----+-------------+-------+--------+--------------------+----------+---------+--------------+------+-------------+
| id | select_type | table |  type  |   possible_keys    |   key    | key_len |     ref      | rows |    Extra    |
+----+-------------+-------+--------+--------------------+----------+---------+--------------+------+-------------+
|  1 | SIMPLE      | fl    | ref    | query_id,user_uuid | query_id |       4 | const        | 3718 |             |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY            | PRIMARY  |     160 | fl.user_uuid |    1 | Using index |
+----+-------------+-------+--------+--------------------+----------+---------+--------------+------+-------------+

Further details:

The table following_lists has about 25k rows, but only 3718 have fl.following_query_id = 1000010.
The table users has about 160k rows, but only 3718 should be selected in the join. Only 40 records meet both conditions fl.following_query_id = 1000010 AND u.status <= 2.
The query is slow even if I remove the condition AND u.status <= 2.

I would not be surprised if it is the CHAR(40). With an integer value, 1 comparison is needed; with that CHAR(40), up to 40 comparisons are needed. How many are actually needed depends on the actual values in your CHAR(40), if the values all have the first X chars the same, it'll take at least that many. Also, if I am not mistaken, MySQL's CHAR indexes do not cover the entire string either, only a certain number if leading characters. — Uueerdo, Dec 21 '17 at 17:25
See the accepted answer to this other SO question. It might give you some clues on ways to improve performance with UUID. https://stackoverflow.com/questions/2365132/uuid-performance-in-mysql/2365176 — Sam M, Dec 21 '17 at 18:36
@SamM yes, both tables use `CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci`. Thanks for the link, I think the problem is definitely with the UUIDs. When I get the time I might try to replicate the database using auto_increment fields and see if the problem persists. — Daniel, Dec 21 '17 at 20:26

score 0 · Answer 1 · answered Dec 27 '17 at 00:58

"have the right indexes" -- dead give away.

If you are using MyISAM, don't. Instead, switch to InnoDB.

Do you need following_lists.id for anything? Is (following_query_id, user_uuid) Unique? If so, make them the PRIMARY KEY.

If you can't do the above, change

KEY `query_id` (`following_query_id`)

to

INDEX(following_query_id, user_uuid)

UUIDs are terrible inefficient, especially when unnecessarily declared utf8mb4, or CHAR with a larger than necessary size. Change to CHAR(36) CHARACTER SET ascii. (Notice the "160" in the `EXPLAIN shrink significantly.)

More on why UUIDs are bad for performance: http://mysql.rjweb.org/doc.php/uuid

How much RAM do you have? What is the setting for innodb_buffer_pool_size? (Sounds like it is too low.)

More on indexing: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Single INNER JOIN of two well-indexed tables takes more than a minute to run

1 Answers1