MySQL Bugs: #52030: WHERE clause with != primary key check slows a query from 0.06 sec to 4 min

Bug #52030	WHERE clause with != primary key check slows a query from 0.06 sec to 4 min
Submitted:	13 Mar 2010 12:35	Modified:	16 Mar 2010 2:27
Reporter:	Greg Hazel	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.1.44	OS:	Any
Assigned to:	Chaithra Marsur Gopala Reddy	CPU Architecture:	Any

Description:
Adding a simple WHERE clause to exclude records with a given id slows a query down from 0.06 seconds to 3 min 35.39 sec.

Using InnoDB. Checking another unique, indexed column in the WHERE clause instead performs very quickly as expected.

mysql> EXPLAIN SELECT SQL_NO_CACHE `users`.* FROM `users` INNER JOIN friendships ON (users.id = friendships.friend_id AND friendships.user_id IN (4)) OR (users.id = friendships.user_id AND friendships.friend_id IN (4)) WHERE (accepted_at IS NOT NULL);
+----+-------------+-------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------+--------+------------------------------------------------+
| id | select_type | table       | type  | possible_keys                                                                                                                                                                                      | key                                                        | key_len | ref  | rows   | Extra                                          |
+----+-------------+-------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------+--------+------------------------------------------------+
|  1 | SIMPLE      | friendships | index | index_friendships_on_friend_id_and_user_id,index_friendships_on_user_id,index_friendships_on_friend_id,index_friendships_on_accepted_at,index_friendships_on_friend_id_and_user_id_and_accepted_at | index_friendships_on_friend_id_and_user_id_and_accepted_at | 17      | NULL | 118367 | Using where; Using index                       |
|  1 | SIMPLE      | users       | ALL   | PRIMARY                                                                                                                                                                                            | NULL                                                       | NULL    | NULL | 100542 | Range checked for each record (index map: 0x1) |
+----+-------------+-------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------+--------+------------------------------------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE `users`.* FROM `users` INNER JOIN friendships ON (users.id = friendships.friend_id AND friendships.user_id IN (4)) OR (users.id = friendships.user_id AND friendships.friend_id IN (4)) WHERE (accepted_at IS NOT NULL) AND `users`.id != 4;
+----+-------------+-------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------+--------+---------------------------------------------+
| id | select_type | table       | type  | possible_keys                                                                                                                                                                                      | key                                                        | key_len | ref  | rows   | Extra                                       |
+----+-------------+-------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------+--------+---------------------------------------------+
|  1 | SIMPLE      | users       | range | PRIMARY                                                                                                                                                                                            | PRIMARY                                                    | 4       | NULL |  50274 | Using where                                 |
|  1 | SIMPLE      | friendships | index | index_friendships_on_friend_id_and_user_id,index_friendships_on_user_id,index_friendships_on_friend_id,index_friendships_on_accepted_at,index_friendships_on_friend_id_and_user_id_and_accepted_at | index_friendships_on_friend_id_and_user_id_and_accepted_at | 17      | NULL | 118367 | Using where; Using index; Using join buffer |
+----+-------------+-------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------+--------+---------------------------------------------+
2 rows in set (0.01 sec)

How to repeat:
mysql> SELECT SQL_NO_CACHE `users`.* FROM `users` INNER JOIN friendships ON (users.id = friendships.friend_id AND friendships.user_id IN (4)) OR (users.id = friendships.user_id AND friendships.friend_id IN (4)) WHERE (accepted_at IS NOT NULL);
...
20 rows in set (0.06 sec)

mysql> SELECT SQL_NO_CACHE `users`.* FROM `users` INNER JOIN friendships ON (users.id = friendships.friend_id AND friendships.user_id IN (4)) OR (users.id = friendships.user_id AND friendships.friend_id IN (4)) WHERE (accepted_at IS NOT NULL) AND `users`.id != 4;
...
20 rows in set (3 min 35.39 sec)

Thank you for the problem report. Can you upload dump of the tables used or, at least, SHOW CREATE TABLE and SHOW TABLE STATUS results for them?

mysql> SHOW TABLE STATUS like 'users';
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-----------------------+
| Name  | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment               |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-----------------------+
| users | InnoDB |      10 | Compact    | 105163 |            254 |    26804224 |               0 |     57917440 |         0 |         106768 | 2010-03-12 06:37:03 | NULL        | NULL       | utf8_general_ci |     NULL |                | InnoDB free: 64512 kB |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-----------------------+
1 row in set (0.65 sec)

mysql> SHOW TABLE STATUS like 'friendships';
+-------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-----------------------+
| Name        | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment               |
+-------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-----------------------+
| friendships | InnoDB |      10 | Compact    | 125359 |             62 |     7880704 |               0 |     30539776 |         0 |         170922 | 2010-03-12 06:08:43 | NULL        | NULL       | utf8_general_ci |     NULL |                | InnoDB free: 64512 kB |
+-------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-----------------------+
1 row in set (0.22 sec)

Attaching the output of SHOW CREATE TABLE in a file since this ticket system says it's too big for a comment.

SHOW CREATE TABLE for users and friendships

Attachment: schema.txt (text/plain), 13.46 KiB.

Thank you for the feedback.

I can not repeat described behavior with test data. Please provide dump of these 2 tables demonstrating the problem.

Uploaded a dump of the friendships table and an example users row to ftp://ftp.mysql.com/pub/mysql/upload/bug-data-52030.tar.gz

In case it's relevant, I'm attaching my my.cnf

my.cnf

Attachment: my.cnf (application/octet-stream, text), 1.09 KiB.

Thank you for the feedback.

Now problem is repeatable for me and looks like bug #19330 with same workaround: select  `users`.* FROM  friendships STRAIGHT_JOIN    users  ....

Set to "Verified", because this case uses only 2 tables and not complex query like in bug #19330.

See private comment for reduced test.

Thank you. Using "friendships STRAIGHT_JOIN users" does cause the query to return in 1 second instead of 4 minutes, but not in the 0.06 seconds I would expect.

This effectively makes this query performance with and without the "users.id != 4" similar to #52029. Much less severe, but still odd.

There is a workaround that seems to help the execution time of this query: Rewrite the OR in the JOIN clause with a UNION of two SELECT queries:

SELECT SQL_NO_CACHE `users`.*
FROM `users` INNER JOIN friendships ON
     (users.id = friendships.friend_id AND friendships.user_id IN (4))
WHERE accepted_at IS NOT NULL AND `users`.id != 4
UNION
SELECT SQL_NO_CACHE `users`.*
FROM `users` INNER JOIN friendships ON
     (users.id = friendships.user_id AND friendships.friend_id IN (4))
WHERE accepted_at IS NOT NULL AND `users`.id != 4;

Otherwise, it might be possible to wrap the original query as a derived query, and then apply `users`.id != 4 to this query.
Like:
SELECT * FROM
 (original_query) as original
WHERE id <> 4;

In this case, it seems that it is the last inequality that confuses the optimizer.

BUG#70002 was marked a duplicate of this bug report. Please verify that both issues are solved when this bug is fixed.