MySQL Bugs: #45559: InnoDB Query not using indexes properly?

Bug #45559	InnoDB Query not using indexes properly?
Submitted:	17 Jun 2009 15:59	Modified:	19 Jun 2009 6:02
Reporter:	Jon Baker	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1.32, 5.1.35	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb indexing slow query

Description:
I've noticed some sluggishness in my system (using Best Practical's RT) and saw multiple instances of the following query (with different Parent values) in the slow query log:

# Query_time: 160.388061  Lock_time: 0.000103 Rows_sent: 1
Rows_examined: 930035
SET timestamp=1245189464;
SELECT main.* FROM Attachments main  WHERE (main.Content IS NOT NULL
AND main.Content != '') AND (main.Parent = '964022') AND
(main.ContentType = 'text/plain')  ORDER BY main.id ASC;

I have indexes set for Content, Parent, and ContentType column.  Running this query from the CLI gives a result in 43.73 seconds.  Running explain gives:

+----+-------------+-------+-------+---------------------------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys                   | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------------------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | main  | index | Attachments3,Parent,ContentType | PRIMARY | 4       | NULL | 1310872 | Using where | 
+----+-------------+-------+-------+---------------------------------+---------+---------+------+---------+-------------+

even though running this query:

select count(*) from Attachments where Parent = '964022';

gives this result:

+----------+
| count(*) |
+----------+
|        2 | 
+----------+
1 row in set (0.00 sec)

Am I incorrect in expecting the optimizer to only look at the 2 rows that match the Parent value, rather than the full 1310872 rows (and 2.6 GiB) of the Attachments table?

How to repeat:
The query in question always takes between 30 and 180 seconds.

Suggested fix:
I would expect the optimizer to be able to handle this query very quickly.

Please, send the results of

SHOW CREATE TABLE Attachments\G
SHOW INDEXES FROM Attachments;

This can be a duplicate of 
http://bugs.mysql.com/bug.php?id=44969 or result of bad statistics.

Yes, looks like it is the same issue.  Removing the "order by main.id asc" performs the query in 0.02 sec and the explain shows that it looks at only 2 rows.

mysql> SHOW CREATE TABLE Attachments;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Attachments | CREATE TABLE `Attachments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `TransactionId` int(11) NOT NULL DEFAULT '0',
  `Parent` int(11) NOT NULL DEFAULT '0',
  `MessageId` varchar(160) DEFAULT NULL,
  `Subject` varchar(255) DEFAULT NULL,
  `Filename` varchar(255) DEFAULT NULL,
  `ContentType` varchar(80) DEFAULT NULL,
  `ContentEncoding` varchar(80) DEFAULT NULL,
  `Content` longblob,
  `Headers` longtext,
  `Creator` int(11) NOT NULL DEFAULT '0',
  `Created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Attachments2` (`TransactionId`),
  KEY `Attachments3` (`Parent`,`TransactionId`),
  KEY `Parent` (`Parent`),
  KEY `ContentType` (`ContentType`),
  KEY `Content` (`Content`(255))
) ENGINE=InnoDB AUTO_INCREMENT=964264 DEFAULT CHARSET=latin1 | 
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

mysql> SHOW INDEXES FROM Attachments;
+-------------+------------+--------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name     | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+--------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Attachments |          0 | PRIMARY      |            1 | id            | A         |     1294651 |     NULL | NULL   |      | BTREE      |         | 
| Attachments |          1 | Attachments2 |            1 | TransactionId | A         |     1294651 |     NULL | NULL   |      | BTREE      |         | 
| Attachments |          1 | Attachments3 |            1 | Parent        | A         |          17 |     NULL | NULL   |      | BTREE      |         | 
| Attachments |          1 | Attachments3 |            2 | TransactionId | A         |     1294651 |     NULL | NULL   |      | BTREE      |         | 
| Attachments |          1 | Parent       |            1 | Parent        | A         |          17 |     NULL | NULL   |      | BTREE      |         | 
| Attachments |          1 | ContentType  |            1 | ContentType   | A         |          17 |     NULL | NULL   | YES  | BTREE      |         | 
| Attachments |          1 | Content      |            1 | Content       | A         |      431550 |      255 | NULL   | YES  | BTREE      |         | 
+-------------+------------+--------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+

Please, send the results of:

select count(distinct `Parent`) from `Attachments`;

If the result is very different from 17, please, try to run SHOW INDEXES FROM ... several times and check if the value in Cardinality column will become more realistic. If it will not this may be a known bug that should be fixed soon...

mysql> select count(distinct `Parent`) from `Attachments`;
+--------------------------+
| count(distinct `Parent`) |
+--------------------------+
|                     6981 | 
+--------------------------+

Very interesting, running SHOW INDEXES multiple times repeatedly shows different values for Parent and ContentType, all three the same every time, but ranging from 15 to 22 - never higher than 22 (in 41 repeated calls, the last 6 showed 18 for the cardinality)

As cardinality estimation is seriously incorrect, this may be related to (result of) bug #36513, bug #41133 or bug #43660. The last one is fixed in 5.0.82 and 5.1.35 already, so, can you, please, check with 5.1.35 and inform about the results?

5.1.35 does seem to have helped the show indexes:

mysql> SHOW INDEXES FROM Attachments;+-------------+------------+--------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name     | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+--------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Attachments |          0 | PRIMARY      |            1 | id            | A         |     1400129 |     NULL | NULL   |      | BTREE      |         | 
| Attachments |          1 | Attachments2 |            1 | TransactionId | A         |     1400129 |     NULL | NULL   |      | BTREE      |         | 
| Attachments |          1 | Attachments3 |            1 | Parent        | A         |          17 |     NULL | NULL   |      | BTREE      |         | 
| Attachments |          1 | Attachments3 |            2 | TransactionId | A         |     1400129 |     NULL | NULL   |      | BTREE      |         | 
| Attachments |          1 | Parent       |            1 | Parent        | A         |      127284 |     NULL | NULL   |      | BTREE      |         | 
| Attachments |          1 | ContentType  |            1 | ContentType   | A         |         327 |     NULL | NULL   | YES  | BTREE      |         | 
| Attachments |          1 | Content      |            1 | Content       | A         |      466709 |      255 | NULL   | YES  | BTREE      |         | 
+-------------+------------+--------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+

Still not fixing the use of the indexing in the query in question:

mysql> explain SELECT main . * FROM Attachments main WHERE ( main.Content IS NOT NULL AND main.Content != '' ) AND ( main.Parent = '964022' ) AND ( main.ContentType = 'text/plain' ) ORDER BY main.id ASC;
+----+-------------+-------+-------+-----------------------------------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys                           | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+-----------------------------------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | main  | index | Attachments3,Parent,ContentType,Content | PRIMARY | 4       | NULL | 1400131 | Using where | 
+----+-------------+-------+-------+-----------------------------------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT main . * FROM Attachments main WHERE ( main.Content IS NOT NULL AND main.Content != '' ) AND ( main.Parent = '964022' ) AND ( main.ContentType = 'text/plain' );
+----+-------------+-------+------+-----------------------------------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys                           | key          | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------------------------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | main  | ref  | Attachments3,Parent,ContentType,Content | Attachments3 | 4       | const |    2 | Using where | 
+----+-------------+-------+------+-----------------------------------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

OK, so this is not about statistics. Looks like it is a duplicate of http://bugs.mysql.com/bug.php?id=44969 then. Please, try to redefine a table so that index on Parent is declared immediately after PRIMARY KEY and check if this will change the plan.

Ok, I dropped all the indexes (except primary) added just Parent and it does solve the "explain" issue - both with the "order by" and without are only pulling the two rows.  So it is definitely the same bug.

The problem I have now is that there are probably just as many queries using the other indexes as were using the Parent index - so I'm eagerly looking forward to a fix!

Duplicate of Bug #44969.