Strange mysql query execution time behavior
العربية
български
català
中文
čeština
dansk
Nederlands
eesti
suomi
français
Deutsch
Ελληνικά
עברית
हिंदी
magyar
Bahasa Indonesia
italiano
日本語
한국어
latviešu
lietuvių
norsk
polski
Português
română
русский
slovenčina
slovenski
español
svenska
ไทย
Türkçe
українська
Tiếng Việt
hello i'm having a strange execution time behavior over almost same sql queries
q1:
SELECT `t1`.`id`, `t1`.`key`, `t1`.`module`, `t2`.`value`
FROM `translates` AS `t1`
LEFT JOIN `translates_i18n` AS `t2` ON (`t2`.`id` = `t1`.`id` AND `t2`.`culture` = 'en')
WHERE `t1`.`module` IN ('GLOBAL','AJAX','FORMS', .... about 15 items) LIMIT 9000;
0.10 sec
q2:
SELECT `t1`.`id`, `t1`.`key`, `t2`.`value`
FROM `translates` AS `t1`
LEFT JOIN `translates_i18n` AS `t2` ON (`t2`.`id` = `t1`.`id` AND `t2`.`culture` = 'en')
WHERE `t1`.`module` IN ('GLOBAL','AJAX','FORMS', .... about 15 items) LIMIT 9000;
0.000... sec
tables definition:
CREATE TABLE IF NOT EXISTS `translates` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`key` varchar(255) NOT NULL,
`module` varchar(255) NOT NULL,
`system` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `key` (`key`,`module`),
KEY `module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
CREATE TABLE IF NOT EXISTS `translates_i18n` (
`id` int(11) unsigned NOT NULL,
`culture` varchar(2) NOT NULL,
`value` text NOT NULL,
PRIMARY KEY (`id`,`culture`),
KEY `culture` (`culture`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `translates_i18n`
ADD CONSTRAINT `translates_i18n_ibfk_1` FOREIGN KEY (`id`) REFERENCES `translates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
difference between q1 & q2 is in t1.module column, that is also in where statement
i just cant see where is the problem, please, anyone poit me
update
q1:
mysql> EXPLAIN SELECT SQL_NO_CACHE `t1`.`id` , `t1`.`key` , `t1`.`module` , `t2`.`value`
-> FROM `translates` AS `t1`
-> LEFT JOIN `translates_i18n` AS `t2` ON ( `t2`.`id` = `t1`.`id`
-> AND `t2`.`culture` = 'en' )
-> WHERE `t1`.`module`
-> IN (
-> 'GLOBAL', 'AJAX', 'FORMS', 'ROOTMENU', 'LANGSWITCHER', 'AUTHORIZATION', 'MENU', 'MINIFY', 'SIMPLESHOP'
-> )
-> LIMIT 100500
-> ;
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+--------------------------+
| 1 | SIMPLE | t1 | index | module | key | 1534 | NULL | 627 | Using where; Using index |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY,culture | PRIMARY | 12 | theloom.t1.id,const | 1 | |
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+--------------------------+
2 rows in set (0.00 sec)
q2:
mysql> EXPLAIN SELECT SQL_NO_CACHE `t1`.`id` , `t1`.`key` , `t2`.`value`
-> FROM `translates` AS `t1`
-> LEFT JOIN `translates_i18n` AS `t2` ON ( `t2`.`id` = `t1`.`id`
-> AND `t2`.`culture` = 'en' )
-> WHERE `t1`.`module`
-> IN (
-> 'GLOBAL', 'AJAX', 'FORMS', 'ROOTMENU', 'LANGSWITCHER', 'AUTHORIZATION', 'MENU', 'MINIFY', 'SIMPLESHOP'
-> )
-> LIMIT 100500;
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+--------------------------+
| 1 | SIMPLE | t1 | index | module | key | 1534 | NULL | 627 | Using where; Using index |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY,culture | PRIMARY | 12 | theloom.t1.id,const | 1 | |
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+--------------------------+
2 rows in set (0.00 sec)
Answer |
Looking at that, I think the reason is like due to the INNODB Buffer Pool. The first query starts with a clean buffer, so before it can process the query, it needs to read the index into memory. Then when you run the second query, it's already in memory, so it runs much faster.
Try adding a FLUSH TALBES command between each query.
You can also try using the Benchmark() function to test this.
The other thing that could be causing the difference is the size of the data to be transfered. I see the additional column is declared VARCHAR(255). Is it perhaps that the column has a lot of data, and 9000 rows really does increase the network overhead that significantly...
Some things to investigate at least...