Description:
I have several (though I am using only one for a test) queries that go from workable to unusable when I change the table type from MyISAM to InnoDB.
Essentially it appears that MySQL loses the ability to choose the correct query plan for the SQL given under InnoDB. Under MyISAM it's just fine. The end result of this is that whilst the query takes ~0.0s under MyISAM, it can take >5m with InnoDB (>30s if the data is in the InnoDB buffer - ie query is run a 2nd time).
Using FORCE INDEX on the InnoDB query, though, restores performance to where it should be. This is unworkable, though, as the query is dynamically created and, so, it can get quite hairy trying to figure out the index for this and other queries in PHP.
This leads to a loss of functionality for the app in question to the extent that it becomes essentially unusable.
The OS is Debian, Wheezy. MySQL 5.5.43 is the standard Debian package. 5.6.25 is a local build of MySQL.
How to repeat:
Essentially, I can run these queries and no matter what, the result is the same:
MYISAM 5.6:
mysql> EXPLAIN SELECT SQL_NO_CACHE t.id as t_id, t.mask as t_mask, t.subject as t_subject, t.is_waiting as t_is_waiting, t.is_closed as t_is_closed, t.is_deleted as t_is_deleted, t.first_wrote_address_id as t_first_wrote_address_id, t.last_wrote_address_id as t_last_wrote_address_id, t.first_message_id as t_first_message_id, t.last_message_id as t_last_message_id, a1.email as t_first_wrote, a1.first_name as a1_first_name, a1.last_name as a1_last_name, a1.num_spam as t_first_wrote_spam, a1.num_nonspam as t_first_wrote_nonspam, a2.email as t_last_wrote, a2.first_name as a2_first_name, a2.last_name as a2_last_name, a1.contact_org_id as t_first_contact_org_id, t.created_date as t_created_date, t.updated_date as t_updated_date, t.spam_training as t_spam_training, t.spam_score as t_spam_score, t.last_action_code as t_last_action_code FROM ticket t INNER JOIN address a1 ON (a1.id=t.first_wrote_address_id) INNER JOIN address a2 ON (a2.id=t.last_wrote_address_id) WHERE 1 ORDER BY t_updated_date DESC LIMIT 0,10;
+----+-------------+-------+--------+----------------------------------------------+--------------+---------+-------------------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------------------------------+--------------+---------+-------------------------------+------+-----------------------+
| 1 | SIMPLE | t | index | first_wrote_address_id,last_wrote_address_id | updated_date | 5 | NULL | 10 | NULL |
| 1 | SIMPLE | a1 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.first_wrote_address_id | 1 | Using index condition |
| 1 | SIMPLE | a2 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.last_wrote_address_id | 1 | Using index condition |
+----+-------------+-------+--------+----------------------------------------------+--------------+---------+-------------------------------+------+-----------------------+
3 rows in set (0.00 sec)
INNODB 5.6:
mysql> EXPLAIN SELECT SQL_NO_CACHE t.id as t_id, t.mask as t_mask, t.subject as t_subject, t.is_waiting as t_is_waiting, t.is_closed as t_is_closed, t.is_deleted as t_is_deleted, t.first_wrote_address_id as t_first_wrote_address_id, t.last_wrote_address_id as t_last_wrote_address_id, t.first_message_id as t_first_message_id, t.last_message_id as t_last_message_id, a1.email as t_first_wrote, a1.first_name as a1_first_name, a1.last_name as a1_last_name, a1.num_spam as t_first_wrote_spam, a1.num_nonspam as t_first_wrote_nonspam, a2.email as t_last_wrote, a2.first_name as a2_first_name, a2.last_name as a2_last_name, a1.contact_org_id as t_first_contact_org_id, t.created_date as t_created_date, t.updated_date as t_updated_date, t.spam_training as t_spam_training, t.spam_score as t_spam_score, t.last_action_code as t_last_action_code FROM ticket t INNER JOIN address a1 ON (a1.id=t.first_wrote_address_id) INNER JOIN address a2 ON (a2.id=t.last_wrote_address_id) WHERE 1 ORDER BY t_updated_date DESC LIMIT 0,10;
+----+-------------+-------+--------+----------------------------------------------+------------------------+---------+------------------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------------------------------+------------------------+---------+------------------------------+--------+---------------------------------+
| 1 | SIMPLE | a1 | ALL | PRIMARY | NULL | NULL | NULL | 380923 | Using temporary; Using filesort |
| 1 | SIMPLE | t | ref | first_wrote_address_id,last_wrote_address_id | first_wrote_address_id | 4 | cerb.a1.id | 13 | Using index condition |
| 1 | SIMPLE | a2 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.last_wrote_address_id | 1 | Using where |
+----+-------------+-------+--------+----------------------------------------------+------------------------+---------+------------------------------+--------+---------------------------------+
3 rows in set (0.01 sec)
INNODB with FORCE INDEX:
mysql> EXPLAIN SELECT t.id as t_id, t.mask as t_mask, t.subject as t_subject, t.is_waiting as t_is_waiting, t.is_closed as t_is_closed, t.is_deleted as t_is_deleted, t.first_wrote_address_id as t_first_wrote_address_id, t.last_wrote_address_id as t_last_wrote_address_ t_last_wrote, a2.first_name as a2_first_name, a2.last_name as a2_last_name, a1.contact_org_id as t_first_contact_org_id, t.created_date as t_created_date, t.updated_date as t_updated_date, t.spam_training as t_spam_training, t.spam_score as t_spam_score, t.last_action_code as t_last_action_code FROM ticket t FORCE INDEX (updated_date) INNER JOIN address a1 ON (a1.id=t.first_wrote_address_id) INNER JOIN address a2 ON (a2.id=t.last_wrote_address_id) WHERE 1 ORDER BY t_updated_date DESC LIMIT 0,10;
+----+-------------+-------+--------+---------------+--------------+---------+-------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+--------------+---------+-------------------------------+------+-------------+
| 1 | SIMPLE | t | index | NULL | updated_date | 5 | NULL | 10 | |
| 1 | SIMPLE | a2 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.last_wrote_address_id | 1 | Using where |
| 1 | SIMPLE | a1 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.first_wrote_address_id | 1 | Using where |
+----+-------------+-------+--------+---------------+--------------+---------+-------------------------------+------+-------------+
3 rows in set (0.00 sec)
CREATEs:
CREATE TABLE `ticket` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`mask` varchar(32) NOT NULL DEFAULT '',
`subject` varchar(255) NOT NULL DEFAULT '',
`is_closed` tinyint(4) NOT NULL DEFAULT '0',
`is_deleted` tinyint(4) NOT NULL DEFAULT '0',
`team_id` int(11) NOT NULL DEFAULT '0',
`category_id` int(11) NOT NULL DEFAULT '0',
`first_message_id` int(11) NOT NULL DEFAULT '0',
`created_date` int(11) DEFAULT NULL,
`updated_date` int(11) DEFAULT NULL,
`due_date` int(11) DEFAULT NULL,
`first_wrote_address_id` int(11) NOT NULL DEFAULT '0',
`last_wrote_address_id` int(11) NOT NULL DEFAULT '0',
`spam_score` double NOT NULL DEFAULT '0',
`spam_training` varchar(1) NOT NULL DEFAULT '',
`interesting_words` varchar(255) NOT NULL DEFAULT '',
`is_waiting` tinyint(4) NOT NULL DEFAULT '0',
`last_action_code` varchar(1) NOT NULL DEFAULT 'O',
`last_message_id` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `first_message_id` (`first_message_id`),
KEY `mask` (`mask`),
KEY `is_waiting` (`is_waiting`),
KEY `team_id` (`team_id`),
KEY `created_date` (`created_date`),
KEY `updated_date` (`updated_date`),
KEY `first_wrote_address_id` (`first_wrote_address_id`),
KEY `last_wrote_address_id` (`last_wrote_address_id`),
KEY `is_closed` (`is_closed`),
KEY `category_id` (`category_id`),
KEY `due_date` (`due_date`),
KEY `is_deleted` (`is_deleted`),
KEY `last_action_code` (`last_action_code`),
KEY `spam_score` (`spam_score`),
KEY `last_message_id` (`last_message_id`),
KEY `subject` (`subject`)
) ENGINE=InnoDB AUTO_INCREMENT=9145625 DEFAULT CHARSET=utf8;
CREATE TABLE `address` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(255) NOT NULL DEFAULT '',
`first_name` varchar(32) NOT NULL DEFAULT '',
`last_name` varchar(32) NOT NULL DEFAULT '',
`contact_org_id` int(11) NOT NULL DEFAULT '0',
`num_spam` int(11) NOT NULL DEFAULT '0',
`num_nonspam` int(11) NOT NULL DEFAULT '0',
`is_banned` tinyint(4) NOT NULL DEFAULT '0',
`last_autoreply` int(11) NOT NULL DEFAULT '0',
`is_registered` tinyint(4) NOT NULL DEFAULT '0',
`pass` varchar(32) NOT NULL DEFAULT '',
`service_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
KEY `contact_org_id` (`contact_org_id`),
KEY `num_spam` (`num_spam`),
KEY `num_nonspam` (`num_nonspam`),
KEY `is_banned` (`is_banned`),
KEY `last_autoreply` (`last_autoreply`),
KEY `is_registered` (`is_registered`),
KEY `first_name` (`first_name`(4)),
KEY `last_name` (`last_name`(4))
) ENGINE=InnoDB AUTO_INCREMENT=385084 DEFAULT CHARSET=utf8;
============================================================================
A different query that exhibits the same issues:
PLAIN QUERY ON INNODB:
mysql> EXPLAIN SELECT SQL_NO_CACHE t.id as t_id, t.mask as t_mask, t.subject as t_subject, t.is_waiting as t_is_waiting, t.is_closed as t_is_closed, t.is_deleted as t_is_deleted, t.first_wrote_address_id as t_first_wrote_address_id, t.last_wrote_address_id as t_last_wrote_address_id, t.first_message_id as t_first_message_id, t.first_outgoing_message_id as t_first_outgoing_message_id, t.last_message_id as t_last_message_id, a1.email as t_first_wrote, a1.first_name as a1_first_name, a1.last_name as a1_last_name, a1.num_spam as t_first_wrote_spam, a1.num_nonspam as t_first_wrote_nonspam, a2.email as t_last_wrote, a2.first_name as a2_first_name, a2.last_name as a2_last_name, a1.contact_org_id as t_first_contact_org_id, t.created_date as t_created_date, t.updated_date as t_updated_date, t.closed_at as t_closed_at, t.reopen_at as t_reopen_at, t.spam_training as t_spam_training, t.spam_score as t_spam_score, t.last_action_code as t_last_action_code, t.num_messages as t_num_messages, t.elapsed_response_first as t_elapsed_response_first, t.elapsed_resolution_first as t_elapsed_resolution_first, t.owner_id as t_owner_id, t.importance as t_importance, t.group_id as t_group_id, t.bucket_id as t_bucket_id, t.org_id as t_org_id ,(SELECT field_value FROM custom_field_stringvalue WHERE t.id=context_id AND field_id=7 ORDER BY field_value LIMIT 1) AS cf_7 FROM ticket t INNER JOIN address a1 ON (t.first_wrote_address_id=a1.id) INNER JOIN address a2 ON (t.last_wrote_address_id=a2.id) WHERE t.is_closed = '0' AND t.group_id IN ('734','735') ORDER BY t_updated_date ASC LIMIT 0,10;
+----+--------------------+--------------------------+--------+----------------------------------------------------------------+--------------+---------+-------------------------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------------------------+--------+----------------------------------------------------------------+--------------+---------+-------------------------------+------+-----------------------------+
| 1 | PRIMARY | t | index | team_id,first_wrote_address_id,last_wrote_address_id,is_closed | updated_date | 5 | NULL | 594 | Using where |
| 1 | PRIMARY | a1 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.first_wrote_address_id | 1 | Using where |
| 1 | PRIMARY | a2 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.last_wrote_address_id | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | custom_field_stringvalue | ref | field_id,source_id | source_id | 4 | cerb.t.id | 1 | Using where; Using filesort |
+----+--------------------+--------------------------+--------+----------------------------------------------------------------+--------------+---------+-------------------------------+------+-----------------------------+
4 rows in set (0.01 sec)
WITH FORCED INDEX:
+---------+---------------+--------------------------------------------------------------------------------------------------------------+--------------+-------------+--------------+--------------------------+-------------------------+--------------------+-------------------+----------------------------------+-------------------+--------------+--------------------+-----------------------+----------------------------------+-------------------+--------------+-----mysql> EXPLAIN SELECT SQL_NO_CACHE t.id as t_id, t.mask as t_mask, t.subject as t_subject, t.is_waiting as t_is_waiting, t.is_closed as t_is_closed, t.is_deleted as t_is_deleted, t.first_wrote_address_id as t_first_wrote_address_id, t.last_wrote_address_id as t_last_wrote_address_id, t.first_message_id as t_first_message_id, t.first_outgoing_message_id as t_first_outgoing_message_id, t.last_message_id as t_last_message_id, a1.email as t_first_wrote, a1.first_name as a1_first_name, a1.last_name as a1_last_name, a1.num_spam as t_first_wrote_spam, a1.num_nonspam as t_first_wrote_nonspam, a2.email as t_last_wrote, a2.first_name as a2_first_name, a2.last_name as a2_last_name, a1.contact_org_id as t_first_contact_org_id, t.created_date as t_created_date, t.updated_date as t_updated_date, t.closed_at as t_closed_at, t.reopen_at as t_reopen_at, t.spam_training as t_spam_training, t.spam_score as t_spam_score, t.last_action_code as t_last_action_code, t.num_messages as t_num_messages, t.elapsed_response_first as t_elapsed_response_first, t.elapsed_resolution_first as t_elapsed_resolution_first, t.owner_id as t_owner_id, t.importance as t_importance, t.group_id as t_group_id, t.bucket_id as t_bucket_id, t.org_id as t_org_id ,(SELECT field_value FROM custom_field_stringvalue WHERE t.id=context_id AND field_id=7 ORDER BY field_value LIMIT 1) AS cf_7 FROM ticket t FORCE INDEX (is_closed, team_id) INNER JOIN address a1 ON (t.first_wrote_address_id=a1.id) INNER JOIN address a2 ON (t.last_wrote_address_id=a2.id) WHERE t.is_closed = '0' AND t.group_id IN ('734','735') ORDER BY t_updated_date ASC LIMIT 0,10;
+----+--------------------+--------------------------+--------+--------------------+-----------+---------+-------------------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------------------------+--------+--------------------+-----------+---------+-------------------------------+--------+-----------------------------+
| 1 | PRIMARY | t | ref | team_id,is_closed | is_closed | 1 | const | 146840 | Using where; Using filesort |
| 1 | PRIMARY | a1 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.first_wrote_address_id | 1 | Using where |
| 1 | PRIMARY | a2 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.last_wrote_address_id | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | custom_field_stringvalue | ref | field_id,source_id | source_id | 4 | cerb.t.id | 1 | Using where; Using filesort |
+----+--------------------+--------------------------+--------+--------------------+-----------+---------+-------------------------------+--------+-----------------------------+
4 rows in set (0.06 sec)
Suggested fix:
No idea. It seems to me that the query planner is broken and that is beyond my ken.
Description: I have several (though I am using only one for a test) queries that go from workable to unusable when I change the table type from MyISAM to InnoDB. Essentially it appears that MySQL loses the ability to choose the correct query plan for the SQL given under InnoDB. Under MyISAM it's just fine. The end result of this is that whilst the query takes ~0.0s under MyISAM, it can take >5m with InnoDB (>30s if the data is in the InnoDB buffer - ie query is run a 2nd time). Using FORCE INDEX on the InnoDB query, though, restores performance to where it should be. This is unworkable, though, as the query is dynamically created and, so, it can get quite hairy trying to figure out the index for this and other queries in PHP. This leads to a loss of functionality for the app in question to the extent that it becomes essentially unusable. The OS is Debian, Wheezy. MySQL 5.5.43 is the standard Debian package. 5.6.25 is a local build of MySQL. How to repeat: Essentially, I can run these queries and no matter what, the result is the same: MYISAM 5.6: mysql> EXPLAIN SELECT SQL_NO_CACHE t.id as t_id, t.mask as t_mask, t.subject as t_subject, t.is_waiting as t_is_waiting, t.is_closed as t_is_closed, t.is_deleted as t_is_deleted, t.first_wrote_address_id as t_first_wrote_address_id, t.last_wrote_address_id as t_last_wrote_address_id, t.first_message_id as t_first_message_id, t.last_message_id as t_last_message_id, a1.email as t_first_wrote, a1.first_name as a1_first_name, a1.last_name as a1_last_name, a1.num_spam as t_first_wrote_spam, a1.num_nonspam as t_first_wrote_nonspam, a2.email as t_last_wrote, a2.first_name as a2_first_name, a2.last_name as a2_last_name, a1.contact_org_id as t_first_contact_org_id, t.created_date as t_created_date, t.updated_date as t_updated_date, t.spam_training as t_spam_training, t.spam_score as t_spam_score, t.last_action_code as t_last_action_code FROM ticket t INNER JOIN address a1 ON (a1.id=t.first_wrote_address_id) INNER JOIN address a2 ON (a2.id=t.last_wrote_address_id) WHERE 1 ORDER BY t_updated_date DESC LIMIT 0,10; +----+-------------+-------+--------+----------------------------------------------+--------------+---------+-------------------------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+----------------------------------------------+--------------+---------+-------------------------------+------+-----------------------+ | 1 | SIMPLE | t | index | first_wrote_address_id,last_wrote_address_id | updated_date | 5 | NULL | 10 | NULL | | 1 | SIMPLE | a1 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.first_wrote_address_id | 1 | Using index condition | | 1 | SIMPLE | a2 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.last_wrote_address_id | 1 | Using index condition | +----+-------------+-------+--------+----------------------------------------------+--------------+---------+-------------------------------+------+-----------------------+ 3 rows in set (0.00 sec) INNODB 5.6: mysql> EXPLAIN SELECT SQL_NO_CACHE t.id as t_id, t.mask as t_mask, t.subject as t_subject, t.is_waiting as t_is_waiting, t.is_closed as t_is_closed, t.is_deleted as t_is_deleted, t.first_wrote_address_id as t_first_wrote_address_id, t.last_wrote_address_id as t_last_wrote_address_id, t.first_message_id as t_first_message_id, t.last_message_id as t_last_message_id, a1.email as t_first_wrote, a1.first_name as a1_first_name, a1.last_name as a1_last_name, a1.num_spam as t_first_wrote_spam, a1.num_nonspam as t_first_wrote_nonspam, a2.email as t_last_wrote, a2.first_name as a2_first_name, a2.last_name as a2_last_name, a1.contact_org_id as t_first_contact_org_id, t.created_date as t_created_date, t.updated_date as t_updated_date, t.spam_training as t_spam_training, t.spam_score as t_spam_score, t.last_action_code as t_last_action_code FROM ticket t INNER JOIN address a1 ON (a1.id=t.first_wrote_address_id) INNER JOIN address a2 ON (a2.id=t.last_wrote_address_id) WHERE 1 ORDER BY t_updated_date DESC LIMIT 0,10; +----+-------------+-------+--------+----------------------------------------------+------------------------+---------+------------------------------+--------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+----------------------------------------------+------------------------+---------+------------------------------+--------+---------------------------------+ | 1 | SIMPLE | a1 | ALL | PRIMARY | NULL | NULL | NULL | 380923 | Using temporary; Using filesort | | 1 | SIMPLE | t | ref | first_wrote_address_id,last_wrote_address_id | first_wrote_address_id | 4 | cerb.a1.id | 13 | Using index condition | | 1 | SIMPLE | a2 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.last_wrote_address_id | 1 | Using where | +----+-------------+-------+--------+----------------------------------------------+------------------------+---------+------------------------------+--------+---------------------------------+ 3 rows in set (0.01 sec) INNODB with FORCE INDEX: mysql> EXPLAIN SELECT t.id as t_id, t.mask as t_mask, t.subject as t_subject, t.is_waiting as t_is_waiting, t.is_closed as t_is_closed, t.is_deleted as t_is_deleted, t.first_wrote_address_id as t_first_wrote_address_id, t.last_wrote_address_id as t_last_wrote_address_ t_last_wrote, a2.first_name as a2_first_name, a2.last_name as a2_last_name, a1.contact_org_id as t_first_contact_org_id, t.created_date as t_created_date, t.updated_date as t_updated_date, t.spam_training as t_spam_training, t.spam_score as t_spam_score, t.last_action_code as t_last_action_code FROM ticket t FORCE INDEX (updated_date) INNER JOIN address a1 ON (a1.id=t.first_wrote_address_id) INNER JOIN address a2 ON (a2.id=t.last_wrote_address_id) WHERE 1 ORDER BY t_updated_date DESC LIMIT 0,10; +----+-------------+-------+--------+---------------+--------------+---------+-------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+--------------+---------+-------------------------------+------+-------------+ | 1 | SIMPLE | t | index | NULL | updated_date | 5 | NULL | 10 | | | 1 | SIMPLE | a2 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.last_wrote_address_id | 1 | Using where | | 1 | SIMPLE | a1 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.first_wrote_address_id | 1 | Using where | +----+-------------+-------+--------+---------------+--------------+---------+-------------------------------+------+-------------+ 3 rows in set (0.00 sec) CREATEs: CREATE TABLE `ticket` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `mask` varchar(32) NOT NULL DEFAULT '', `subject` varchar(255) NOT NULL DEFAULT '', `is_closed` tinyint(4) NOT NULL DEFAULT '0', `is_deleted` tinyint(4) NOT NULL DEFAULT '0', `team_id` int(11) NOT NULL DEFAULT '0', `category_id` int(11) NOT NULL DEFAULT '0', `first_message_id` int(11) NOT NULL DEFAULT '0', `created_date` int(11) DEFAULT NULL, `updated_date` int(11) DEFAULT NULL, `due_date` int(11) DEFAULT NULL, `first_wrote_address_id` int(11) NOT NULL DEFAULT '0', `last_wrote_address_id` int(11) NOT NULL DEFAULT '0', `spam_score` double NOT NULL DEFAULT '0', `spam_training` varchar(1) NOT NULL DEFAULT '', `interesting_words` varchar(255) NOT NULL DEFAULT '', `is_waiting` tinyint(4) NOT NULL DEFAULT '0', `last_action_code` varchar(1) NOT NULL DEFAULT 'O', `last_message_id` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `first_message_id` (`first_message_id`), KEY `mask` (`mask`), KEY `is_waiting` (`is_waiting`), KEY `team_id` (`team_id`), KEY `created_date` (`created_date`), KEY `updated_date` (`updated_date`), KEY `first_wrote_address_id` (`first_wrote_address_id`), KEY `last_wrote_address_id` (`last_wrote_address_id`), KEY `is_closed` (`is_closed`), KEY `category_id` (`category_id`), KEY `due_date` (`due_date`), KEY `is_deleted` (`is_deleted`), KEY `last_action_code` (`last_action_code`), KEY `spam_score` (`spam_score`), KEY `last_message_id` (`last_message_id`), KEY `subject` (`subject`) ) ENGINE=InnoDB AUTO_INCREMENT=9145625 DEFAULT CHARSET=utf8; CREATE TABLE `address` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(255) NOT NULL DEFAULT '', `first_name` varchar(32) NOT NULL DEFAULT '', `last_name` varchar(32) NOT NULL DEFAULT '', `contact_org_id` int(11) NOT NULL DEFAULT '0', `num_spam` int(11) NOT NULL DEFAULT '0', `num_nonspam` int(11) NOT NULL DEFAULT '0', `is_banned` tinyint(4) NOT NULL DEFAULT '0', `last_autoreply` int(11) NOT NULL DEFAULT '0', `is_registered` tinyint(4) NOT NULL DEFAULT '0', `pass` varchar(32) NOT NULL DEFAULT '', `service_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), KEY `contact_org_id` (`contact_org_id`), KEY `num_spam` (`num_spam`), KEY `num_nonspam` (`num_nonspam`), KEY `is_banned` (`is_banned`), KEY `last_autoreply` (`last_autoreply`), KEY `is_registered` (`is_registered`), KEY `first_name` (`first_name`(4)), KEY `last_name` (`last_name`(4)) ) ENGINE=InnoDB AUTO_INCREMENT=385084 DEFAULT CHARSET=utf8; ============================================================================ A different query that exhibits the same issues: PLAIN QUERY ON INNODB: mysql> EXPLAIN SELECT SQL_NO_CACHE t.id as t_id, t.mask as t_mask, t.subject as t_subject, t.is_waiting as t_is_waiting, t.is_closed as t_is_closed, t.is_deleted as t_is_deleted, t.first_wrote_address_id as t_first_wrote_address_id, t.last_wrote_address_id as t_last_wrote_address_id, t.first_message_id as t_first_message_id, t.first_outgoing_message_id as t_first_outgoing_message_id, t.last_message_id as t_last_message_id, a1.email as t_first_wrote, a1.first_name as a1_first_name, a1.last_name as a1_last_name, a1.num_spam as t_first_wrote_spam, a1.num_nonspam as t_first_wrote_nonspam, a2.email as t_last_wrote, a2.first_name as a2_first_name, a2.last_name as a2_last_name, a1.contact_org_id as t_first_contact_org_id, t.created_date as t_created_date, t.updated_date as t_updated_date, t.closed_at as t_closed_at, t.reopen_at as t_reopen_at, t.spam_training as t_spam_training, t.spam_score as t_spam_score, t.last_action_code as t_last_action_code, t.num_messages as t_num_messages, t.elapsed_response_first as t_elapsed_response_first, t.elapsed_resolution_first as t_elapsed_resolution_first, t.owner_id as t_owner_id, t.importance as t_importance, t.group_id as t_group_id, t.bucket_id as t_bucket_id, t.org_id as t_org_id ,(SELECT field_value FROM custom_field_stringvalue WHERE t.id=context_id AND field_id=7 ORDER BY field_value LIMIT 1) AS cf_7 FROM ticket t INNER JOIN address a1 ON (t.first_wrote_address_id=a1.id) INNER JOIN address a2 ON (t.last_wrote_address_id=a2.id) WHERE t.is_closed = '0' AND t.group_id IN ('734','735') ORDER BY t_updated_date ASC LIMIT 0,10; +----+--------------------+--------------------------+--------+----------------------------------------------------------------+--------------+---------+-------------------------------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+--------------------------+--------+----------------------------------------------------------------+--------------+---------+-------------------------------+------+-----------------------------+ | 1 | PRIMARY | t | index | team_id,first_wrote_address_id,last_wrote_address_id,is_closed | updated_date | 5 | NULL | 594 | Using where | | 1 | PRIMARY | a1 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.first_wrote_address_id | 1 | Using where | | 1 | PRIMARY | a2 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.last_wrote_address_id | 1 | Using where | | 2 | DEPENDENT SUBQUERY | custom_field_stringvalue | ref | field_id,source_id | source_id | 4 | cerb.t.id | 1 | Using where; Using filesort | +----+--------------------+--------------------------+--------+----------------------------------------------------------------+--------------+---------+-------------------------------+------+-----------------------------+ 4 rows in set (0.01 sec) WITH FORCED INDEX: +---------+---------------+--------------------------------------------------------------------------------------------------------------+--------------+-------------+--------------+--------------------------+-------------------------+--------------------+-------------------+----------------------------------+-------------------+--------------+--------------------+-----------------------+----------------------------------+-------------------+--------------+-----mysql> EXPLAIN SELECT SQL_NO_CACHE t.id as t_id, t.mask as t_mask, t.subject as t_subject, t.is_waiting as t_is_waiting, t.is_closed as t_is_closed, t.is_deleted as t_is_deleted, t.first_wrote_address_id as t_first_wrote_address_id, t.last_wrote_address_id as t_last_wrote_address_id, t.first_message_id as t_first_message_id, t.first_outgoing_message_id as t_first_outgoing_message_id, t.last_message_id as t_last_message_id, a1.email as t_first_wrote, a1.first_name as a1_first_name, a1.last_name as a1_last_name, a1.num_spam as t_first_wrote_spam, a1.num_nonspam as t_first_wrote_nonspam, a2.email as t_last_wrote, a2.first_name as a2_first_name, a2.last_name as a2_last_name, a1.contact_org_id as t_first_contact_org_id, t.created_date as t_created_date, t.updated_date as t_updated_date, t.closed_at as t_closed_at, t.reopen_at as t_reopen_at, t.spam_training as t_spam_training, t.spam_score as t_spam_score, t.last_action_code as t_last_action_code, t.num_messages as t_num_messages, t.elapsed_response_first as t_elapsed_response_first, t.elapsed_resolution_first as t_elapsed_resolution_first, t.owner_id as t_owner_id, t.importance as t_importance, t.group_id as t_group_id, t.bucket_id as t_bucket_id, t.org_id as t_org_id ,(SELECT field_value FROM custom_field_stringvalue WHERE t.id=context_id AND field_id=7 ORDER BY field_value LIMIT 1) AS cf_7 FROM ticket t FORCE INDEX (is_closed, team_id) INNER JOIN address a1 ON (t.first_wrote_address_id=a1.id) INNER JOIN address a2 ON (t.last_wrote_address_id=a2.id) WHERE t.is_closed = '0' AND t.group_id IN ('734','735') ORDER BY t_updated_date ASC LIMIT 0,10; +----+--------------------+--------------------------+--------+--------------------+-----------+---------+-------------------------------+--------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+--------------------------+--------+--------------------+-----------+---------+-------------------------------+--------+-----------------------------+ | 1 | PRIMARY | t | ref | team_id,is_closed | is_closed | 1 | const | 146840 | Using where; Using filesort | | 1 | PRIMARY | a1 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.first_wrote_address_id | 1 | Using where | | 1 | PRIMARY | a2 | eq_ref | PRIMARY | PRIMARY | 4 | cerb.t.last_wrote_address_id | 1 | Using where | | 2 | DEPENDENT SUBQUERY | custom_field_stringvalue | ref | field_id,source_id | source_id | 4 | cerb.t.id | 1 | Using where; Using filesort | +----+--------------------+--------------------------+--------+--------------------+-----------+---------+-------------------------------+--------+-----------------------------+ 4 rows in set (0.06 sec) Suggested fix: No idea. It seems to me that the query planner is broken and that is beyond my ken.