MySQL Bugs: #65359: bad select performance

Bug #65359	bad select performance
Submitted:	18 May 2012 12:41	Modified:	30 May 2012 17:01
Reporter:	wiliam brana	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.62	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Following select takes 95 ms if table posts uses InnoDB, but only 5 ms if MyISAM is used. Table posts has 1 200 000 rows. Table users has 100 000 rows.

SELECT * FROM v_posts WHERE forum_id =1 AND thread_id=100 AND order_id < 3001 AND order_id > 2990 ORDER BY order_id LIMIT 10

ALTER ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_posts` AS 
select `posts`.`forum_id` AS `forum_id`,`posts`.
`thread_id` AS `thread_id`,
`posts`.`text` AS `post_text`,
`posts`.`created` AS `post_created`,
`posts`.`modified` AS `post_modified`,
`users`.`login` AS `user_login`,
`users`.`post_count` AS `user_post_count`,
`posts`.`id` AS `post_id`,
`posts`.`order_id` AS `order_id` 
from (`posts` join `users`) 
where (`posts`.`user_id` = `users`.`id`) 
order by `posts`.`forum_id`,`posts`.`thread_id`,`posts`.`id`

CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text` mediumtext NOT NULL,
  `modified` datetime NOT NULL,
  `thread_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `forum_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `forum_id` (`forum_id`),
  KEY `thread_id` (`thread_id`),
  KEY `user_id` (`user_id`),
  KEY `order_id` (`order_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1200001 ;

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(32) NOT NULL,
  `email` varchar(32) NOT NULL,
  `pwd` varchar(64) NOT NULL,
  `date_reg` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `date_login` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `active` tinyint(1) NOT NULL,
  `confirmation_code` varchar(32) DEFAULT NULL,
  `post_count` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `login` (`login`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=100000 ;

How to repeat:
SELECT * FROM v_posts WHERE forum_id =1 AND thread_id=100 AND order_id < 3001 AND order_id > 2990 ORDER BY order_id LIMIT 10

select takes 85 ms with 5.5.22

Please, send the output of:

explain SELECT * FROM v_posts WHERE forum_id =1 AND thread_id=100 AND order_id < 3001 AND order_id > 2990 ORDER BY order_id LIMIT 10;

for cases when tables referred to by the view are both MyISAM (fast) and both InnoDB (slow).

InnoDB:
mysql> EXPLAIN SELECT * FROM forum.v_posts WHERE forum_id =1 AND thread_id =100 AND order_id <3001 AND order_id >2990 ORDER BY order_id LIMIT 10;
+----+-------------+-------+-------------+-------------------------------------+--------------------+---------+---------------------+------+------------------------------------------------------------------+
| id | select_type | table | type        | possible_keys                       | key                | key_len | ref                 | rows | Extra                                                            |
+----+-------------+-------+-------------+-------------------------------------+--------------------+---------+---------------------+------+------------------------------------------------------------------+
|  1 | SIMPLE      | posts | index_merge | forum_id,thread_id,user_id,order_id | thread_id,forum_id | 4,4     | NULL                | 1499 | Using intersect(thread_id,forum_id); Using where; Using filesort |
|  1 | SIMPLE      | users | eq_ref      | PRIMARY                             | PRIMARY            | 4       | forum.posts.user_id |    1 |                                                                  |
+----+-------------+-------+-------------+-------------------------------------+--------------------+---------+---------------------+------+------------------------------------------------------------------+
2 rows in set (0.00 sec)

MyISAM:
mysql> EXPLAIN SELECT * FROM forum.v_posts WHERE forum_id =1 AND thread_id =100 AND order_id <3001 AND order_id >2990 ORDER BY order_id LIMIT 10;
+----+-------------+-------+--------+-------------------------------------+-----------+---------+---------------------+------+-----------------------------+
| id | select_type | table | type   | possible_keys                       | key       | key_len | ref                 | rows | Extra                       |
+----+-------------+-------+--------+-------------------------------------+-----------+---------+---------------------+------+-----------------------------+
|  1 | SIMPLE      | posts | ref    | forum_id,thread_id,user_id,order_id | thread_id | 4       | const               | 2964 | Using where; Using filesort |
|  1 | SIMPLE      | users | eq_ref | PRIMARY                             | PRIMARY   | 4       | forum.posts.user_id |    1 |                             |
+----+-------------+-------+--------+-------------------------------------+-----------+---------+---------------------+------+-----------------------------+
2 rows in set (0.00 sec)

I forgot to remove unnecessary "forum_id =1", which seems to cause bad performance.

Looks like problem may be related to outdated/wrong statistics for InnoDB table. Can you, please, try to run ANALYZE TABLE for InnoDB table several times and check if EXPLAIN results for the problematic query ever change to NOT use index on forum_id.

analyze table didn't help

Thank you for the feedback.

this seems to be duplicate of bug #65274. Please use FORCE/IGNORE INDEX or set optimizer switch, so it does not suppor index merge optimization until it is fixed.