MySQL Forums
Forum List  »  Performance

'better' explain but slower query
Posted by: Clément Nodet
Date: April 23, 2008 05:43AM

Hi there,

I'm currently trying to optimize some queries spotted on the slow query log and
I'm having a bad time understanding why, even with an overall "better" explain,
the old query is always faster.



Server version: 5.0.22-Debian_0ubuntu6.06.3-log
mysql> show create table ACCOUNT_MESSAGES_X;

| ACCOUNT_MESSAGES_X | CREATE TABLE `ACCOUNT_MESSAGES_X` (
`ID` bigint(20) NOT NULL auto_increment,
`ID_ACCOUNT` varchar(50) collate latin1_bin NOT NULL,
`ID_CENTER` int(11) NOT NULL,
`ID_TIME` int(11) NOT NULL,
`TYPE` tinyint(4) NOT NULL,
`ACTION` tinyint(4) NOT NULL,
`TIME_DIALOGUE` varchar(50) collate latin1_bin default NULL,
`TIME_WAITING` varchar(50) collate latin1_bin default NULL,
`ID_MESSAGE` int(11) default NULL,
`ID_MESSAGE_MO` int(11) default '0',
`CONVERTED_MT` tinyint(3) default '0',
`CONTENT_MESSAGE` varchar(255) collate latin1_bin NOT NULL,
`ID_DIALOG` int(11) default NULL,
`ID_SERVICE` varchar(20) collate latin1_bin default NULL,
`ID_VP` int(11) NOT NULL,
`COMMENT` varchar(255) collate latin1_bin NOT NULL,
`DATE_SEND` datetime NOT NULL,
PRIMARY KEY (`ID`),
KEY `TIME_DIMENSION_ACCOUNT_MESSAGES_FK2` (`ID_TIME`),
KEY `ID_DIALOG` (`ID_DIALOG`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin ROW_FORMAT=COMPRESSED |


mysql> show index from ACCOUNT_MESSAGES_X;

+---------------------+------------+-------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------------+------------+-------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| ACCOUNT_MESSAGES_X | 0 | PRIMARY | 1 | ID | A | 1300106 | NULL | NULL | | BTREE | |
| ACCOUNT_MESSAGES_X | 1 | TIME_DIMENSION_ACCOUNT_MESSAGES_FK2 | 1 | ID_TIME | A | 7065 | NULL | NULL | | BTREE | |
| ACCOUNT_MESSAGES_X | 1 | ID_DIALOG | 1 | ID_DIALOG | A | 68426 | NULL | NULL | YES | BTREE | |
+---------------------+------------+-------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.12 sec)


mysql> show create table TIME_DIMENSION;

| TIME_DIMENSION | CREATE TABLE `TIME_DIMENSION` (
`ID` int(11) NOT NULL auto_increment,
`DAY` tinyint(4) NOT NULL default '0',
`WEEK` tinyint(4) NOT NULL default '0',
`MONTH` tinyint(4) NOT NULL default '0',
`QUARTER` tinyint(4) NOT NULL default '0',
`YEAR` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`),
UNIQUE KEY `YEAR` (`YEAR`,`MONTH`,`DAY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci |


mysql> show index from TIME_DIMENSION;

+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| TIME_DIMENSION | 0 | PRIMARY | 1 | ID | A | 313 | NULL | NULL | | BTREE | |
| TIME_DIMENSION | 0 | YEAR | 1 | YEAR | A | 4 | NULL | NULL | | BTREE | |
| TIME_DIMENSION | 0 | YEAR | 2 | MONTH | A | 22 | NULL | NULL | | BTREE | |
| TIME_DIMENSION | 0 | YEAR | 3 | DAY | A | 313 | NULL | NULL | | BTREE | |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.01 sec)



And now, here is the original query :



mysql> EXPLAIN SELECT *
-> FROM ACCOUNT_MESSAGES_X
-> WHERE ( ID_MESSAGE<>0 AND
-> TYPE=1 AND
-> ID_TIME IN (SELECT ID FROM TIME_DIMENSION WHERE
-> DAY = 13 AND
-> WEEK = 23 AND
-> MONTH = 6 AND
-> QUARTER = 2 AND
-> YEAR = 2007
-> )
-> );
+----+--------------------+---------------------+-------+---------------+------+---------+-------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------------------+-------+---------------+------+---------+-------------------+---------+-------------+
| 1 | PRIMARY | ACCOUNT_MESSAGES_X | ALL | NULL | NULL | NULL | NULL | 1300106 | Using where |
| 2 | DEPENDENT SUBQUERY | TIME_DIMENSION | const | PRIMARY,YEAR | YEAR | 6 | const,const,const | 1 | |
+----+--------------------+---------------------+-------+---------------+------+---------+-------------------+---------+-------------+
2 rows in set (0.00 sec)



As expected (had this problem on previous queries), mysql doesn't use the
TIME_DIMENSION_ACCOUNT_MESSAGES_FK2 index on "IN"and do a full
table scan. So, for this, I used a join for triggering TIME_DIMENSION_ACCOUNT_MESSAGES_FK2
usage, giving the following query :



mysql> EXPLAIN SELECT ACCOUNT_MESSAGES_X.*
-> FROM ( SELECT ID FROM TIME_DIMENSION
-> WHERE DAY = 13 AND MONTH = 6 AND YEAR = 2007 ) TIME
-> JOIN ACCOUNT_MESSAGES_X ON TIME.ID = ID_TIME
-> WHERE ( ID_MESSAGE<>0 AND TYPE=1 );

+----+-------------+---------------------+--------+-------------------------------------+-------------------------------------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+--------+-------------------------------------+-------------------------------------+---------+-------+-------+-------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | ACCOUNT_MESSAGES_X | ref | TIME_DIMENSION_ACCOUNT_MESSAGES_FK2 | TIME_DIMENSION_ACCOUNT_MESSAGES_FK2 | 4 | const | 74492 | Using where |
| 2 | DERIVED | TIME_DIMENSION | const | YEAR | YEAR | 6 | | 1 | Using index |
+----+-------------+---------------------+--------+-------------------------------------+-------------------------------------+---------+-------+-------+-------------+
3 rows in set (0.00 sec)



But, even though the explain is better (IMO), the 2nd query is slower than the
original one (10 second vs 8 second). I tried to test these queries on a quiet
(but lower-perf) server (running 5.0.38) and is still the same problem.
Interestingly enough, the explain for the 2nd query is different for rows
on the join :



mysql> EXPLAIN SELECT ACCOUNT_MESSAGES_X.*
-> FROM ( SELECT ID FROM TIME_DIMENSION
-> WHERE DAY = 13 AND MONTH = 6 AND YEAR = 2007 ) TIME
-> JOIN ACCOUNT_MESSAGES_X ON TIME.ID = ID_TIME
-> WHERE ( ID_MESSAGE<>0 AND TYPE=1 );
+----+-------------+---------------------+--------+-------------------------------------+-------------------------------------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+--------+-------------------------------------+-------------------------------------+---------+-------+--------+-------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | ACCOUNT_MESSAGES_X | ref | TIME_DIMENSION_ACCOUNT_MESSAGES_FK2 | TIME_DIMENSION_ACCOUNT_MESSAGES_FK2 | 4 | const | 650053 | Using where |
| 2 | DERIVED | TIME_DIMENSION | const | YEAR | YEAR | 6 | | 1 | Using index |
+----+-------------+---------------------+--------+-------------------------------------+-------------------------------------+---------+-------+--------+-------------+
3 rows in set (0.00 sec)



In brief, I'm kinda lost. Am I missing something crucial here?

Thanks in advance for any help provided.
Clément.

Options: ReplyQuote


Subject
Views
Written By
Posted
'better' explain but slower query
3374
April 23, 2008 05:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.