Strange mysql query execution time behavior

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)
This question and answers originated from www.stackoverflow.com
Question by (1/25/2011 2:04:35 AM)

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...

Answer by

Find More Answers
Related Topics  mysql  sql
Related Questions
  • MYSQL query execution time

    I have a question for you. I have this database with 250.000 recordings, with 2 text fields each containing up to 300 words. And I want do select all the data that meets some criteria and put it in …
  • MySql query execution time

    I am using MySQL Workbench on Windows. How do I find out the time taken to execute a query like Select * from employers where employerid > 200 Is there a statement I can enter that returns…
  • Anyway to Limit MySQL Query Execution time?

    Hi I'm having issues on my server at the minute because some of the MySQL queries are taking a very long time to run, and hogging the resources on the server. I'm already in the process of optimi…
  • mysql query execution time | reduce query

    I have a query which let me to change users order. here is my query: update test set orderID = case orderID when (select orderID from ( select * from test where orderID > ( select …
  • How to controll Mysql query execution time?

    I have several EVENTS executing every two seconds, I would like to kill those which are running longer than 1 second. Are there way to control query execution time on MySQL 5.5? cheers Arman.…
  • MySQL IN() strange behavior

    If i do a select like this: SELECT `id` FROM `table` WHERE `id` IN (1,2,3) it works but if i do like this: SELECT `id` FROM `table` WHERE `id` IN (`column`) where column is an internal…
  • mysql execution time

    Is there a way to get the execution time of the last executed query in mysql?
  • Strange behavior of MySQL UPDATE query in PHP?

    When I am executing following query then its not updating views column by 1 instead sometimes its updating it by 2 or 3. Say currently views count is 24 then after executing this query it becomes 26…
  • How to set a maximum execution time for a mysql query?

    I would like to set a maximum execution time for sql queries like set_time_limit() in php. How can I do ?
  • Strange behavior of mysql_connect in PHP

    This is how a connection is created to MySQL server at localhost $Connection = mysql_connect("localhost","root",""); But MySQL seems ignoring my username and password, even if I create the co…