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:

[18 May 2012 12:41] wiliam brana
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
[18 May 2012 13:39] wiliam brana
select takes 85 ms with 5.5.22
[18 May 2012 13:46] Valerii Kravchuk
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).
[18 May 2012 14:29] wiliam brana
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)
[18 May 2012 21:40] wiliam brana
I forgot to remove unnecessary "forum_id =1", which seems to cause bad performance.
[22 May 2012 13:24] Valerii Kravchuk
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.
[22 May 2012 13:51] wiliam brana
analyze table didn't help
[30 May 2012 17:01] Sveta Smirnova
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.