MySQL Query sometime OK, other times takes 5 min!

Question

Hi, I have a table I use for high scores of an iPhone game. As of now the query is disabled so gamers are not able to see scores :-(

Table has the following fields:

Type    Collation   Attributes  Null    Default Extra   Action
id  int(11)         No      auto_increment                          
date    date            No                                  
timestamp   timestamp           No  CURRENT_TIMESTAMP                               
game_id tinyint(4)          No                                  
game_size   tinyint(4)          No                                  
game_level  tinyint(4)          No                                  
score   float           No                                  
score_string    char(11)    utf8_general_ci     No                                   
name    varchar(50) utf8_general_ci     No                                   
place   varchar(50) utf8_general_ci     No      
device  varchar(128)    utf8_general_ci     Yes NULL                

I added a two-field index for game_id and game_size This may be the problem, but I can't figure out why searches will take 5 minutes...

This is the query that takes all that time. Other, simpler queries also took long time.

SELECT SQL_CALC_FOUND_ROWS name, MIN(score_string) AS Expr1, place FROM
scores WHERE  game_id="1" AND game_size = "5" AND game_level = "1"
AND date>  "2005-04-14" GROUP BY name, place ORDER BY
MIN(score_string) Limit 0, 100;

When I test it in phpMyAdmin, it returns 1 second, then a few 0.0015 second, then 1 second again.

Any help would be more than welcome. Thanks! Hanaan

Below is EXPLAIN for proposed query:

EXPLAIN SELECT name, score_string, place
FROM scores s
WHERE game_id =1
AND game_size =15
AND game_level =1
AND id = (

SELECT id
FROM scores si
WHERE si.game_id =1
AND si.game_size =15
AND si.game_level =1
AND si.name = s.name
AND si.place = s.place
AND si.date >  '2005-04-14'
ORDER BY si.game_id, si.game_size, si.game_level, si.name, si.place, si.score_string, si.id
LIMIT 1
)
ORDER BY game_id, game_size, game_level, score_string, id

LIMIT 100

d   select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY s   ref game_id,indx1,game_id_2 game_id_2   3   const,const,const   14034   Using where
2   DEPENDENT SUBQUERY  si  ref game_id,indx1,game_id_2 game_id 307 const,const,const,tmi_hcg.s.name,tmi_hcg.s.place    13  Using where

SHOW CREATE TABLE scores

CREATE TABLE `scores` (
 `id` int(11) NOT NULL auto_increment,
 `date` date NOT NULL,
 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
 `game_id` tinyint(4) NOT NULL,
 `game_size` tinyint(4) NOT NULL,
 `game_level` tinyint(4) NOT NULL,
 `score` float NOT NULL,
 `score_string` char(11) NOT NULL,
 `name` varchar(50) NOT NULL,
 `place` varchar(50) NOT NULL,
 `device` varchar(128) default NULL,
 PRIMARY KEY  (`id`),
 KEY `game_id` (`game_id`,`game_size`,`game_level`,`name`,`place`,`score_string`,`id`),
 KEY `indx1` (`game_id`,`game_size`,`game_level`,`date`,`id`),
 KEY `game_id_2` (`game_id`,`game_size`,`game_level`,`score_string`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=81564 DEFAULT CHARSET=utf8

It also appears that the closer the dat is to today, the longer the response:

SELECT name, score_string, place
FROM scores s
WHERE game_id =1
AND game_size =15
AND game_level =1
AND id = ( 
SELECT id
FROM scores si
WHERE si.game_id =1
AND si.game_size =15
AND si.game_level =1
AND si.name = s.name
AND si.place = s.place
AND si.date >  "2010-10-01"
ORDER BY si.game_id, si.game_size, si.game_level, si.name, si.place, si.score_string, si.id
LIMIT 1 ) 
ORDER BY game_id, game_size, game_level, score_string, id
LIMIT 100

This took a whopping 49 seconds!

Answer 1

Minimal: All fields in the where clause should have one index (not combined).

Maximal: Minimal + name, place, score_string as well.

It is very hard to tell without more information about number of rows, distribution of the values on the indexed fields etc.

Keep in mind: try to index the most selective field or that with an even distribution first.

Work on your accceptance rate!

Answer 2

That's a lot of work for an SQL query to do. Find the minimum value in one column, match other columns and get a range within another, not to mention sorting.

If you don't have any indexing, this will take a lot of time once you get a lot of records in the table. Of course, putting in too many indexes will make insert take a long time, so you don't want to overdo it.

If you run a query, and immediately rerun it, mysql will likely have the information cached, making the second (and all subsequent queries) lightning fast. I think that's why you're seeing long queries, and then they speed up. You need to use the long time to access how much new indexes will help. If you need to tell mysql to get rid of it's cache, you can use "flush tables" (but be prepared for your query to take a long time).

Hope that helps.

Answer 3

Make sure you have the following indexes:

scores (game_id, game_size, game_level, date, id)
scores (game_id, game_size, game_level, name, place, score_string, id)

(either of them could be efficient depending on the data distribution).

Also, it may be useful to add the following index:

scores (game_id, game_size, game_level, score_string, id)

and rewrite the query as this:

SELECT  name, score_string, place
FROM    scores s
WHERE   game_id = 1
        AND game_size = 5
        AND game_level = 1
        AND id =
        (
        SELECT  id
        FROM    scores si
        WHERE   si.game_id = 1
                AND si.game_size = 5
                AND si.game_level = 1
                AND si.name = s.name
                AND si.place = s.place
                AND si.date > '2005-04-14'
        ORDER BY
                si.game_id, si.game_size, si.game_level, si.name, si.place, si.score_string, si.id
        LIMIT 1
        )
ORDER BY
        game_id, game_size, game_level, score_string, id
LIMIT 100

These query is identical to the first one but is more useful if your conditions are not selective.

These two articles explain how the query works:

• 20 latest unique records
• OLAP in MySQL: four ways to filter on higher level dimensions