MySQL Query sometime OK, other times takes 5 min!
I have a table I use for high scores of an iPhone game. As of now the query is disabled so gamers are not ab开发者_如何学编程le 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!
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
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!
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.
精彩评论