开发者

Using temporary; using filesort.. slow query

i have a very 开发者_运维问答simple query that im trying to optimize, its taking 2~5 secs to execute.

This is my CREATE TABLE

CREATE TABLE `artist` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) character set utf8 NOT NULL,
  `bio` MEDIUMTEXT character set utf8 DEFAULT NULL,
  `hits` INTEGER NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `album` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `artist_id` INTEGER NOT NULL,
  `title` VARCHAR(100) character set utf8 NOT NULL,
  `year` INTEGER,
  `hits` INTEGER NOT NULL,
  PRIMARY KEY (`id`),
KEY (`artist_id`)
);

CREATE TABLE `track` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) character set utf8 NOT NULL,
  `lyric` MEDIUMTEXT character set utf8,
  `album_id` INTEGER NOT NULL,
  `hits` INTEGER NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`),
KEY (`album_id`)
);

ALTER TABLE `album` ADD FOREIGN KEY (artist_id) REFERENCES `artist` (`id`);
ALTER TABLE `track` ADD FOREIGN KEY (album_id) REFERENCES `album` (`id`);

and this is the query im running

SELECT DISTINCT artist.name, track.name
FROM track
LEFT JOIN album ON track.album_id = album.id
LEFT JOIN artist ON album.artist_id = artist.id
ORDER BY track.hits DESC
LIMIT 5 

Explain selects show this:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  track   ALL     NULL    NULL    NULL    NULL    103796  Using temporary; Using filesort
1   SIMPLE  album   eq_ref  PRIMARY     PRIMARY     4   lyrics.track.album_id   1    
1   SIMPLE  artist  eq_ref  PRIMARY     PRIMARY     4   lyrics.album.artist_id  1    

I'm new to MySQL but i guess using Using temporary; Using filesort is bad and thats why the query is very slow, can you guys hint me here? thanks!

update: The main problem here is that the very same song can be 5 times in the DB with different ID's, because the same song can be in different albums. If i dont use distinct, this doesnt happen, bust i must for this reason


This answer isn't 100% an answer for the original question. The original question is what came up when searching using the messages from my problem though, so just in case it helps someone else, I'll leave the solution for a problem that is closely related.

The "using temporary; using filesort" was actually a red herring and the index that was added was never getting used. The index was not getting used because one of the joined tables had a different character encoding on it than the other.

Converting all tables in the query so that they all used the same character encoding fixed it instantly.

(In our case converting a utf8 encoded table to a latin1 encoding)

Hope it helps someone.


You can get it to use an index by adding

create index idx_tracks_on_album_id_name_hits on track(album_id, name, hits);

And since you are doing a DISTINCT across two tables, there will be no index to possibly find the unique rows so it puts it into a temp table to get rid of the duplicates.


I think if you create an index on track.hits, you might get rid of "using temporary; using filesort", the reason for which might be because MySQL cannot find an index to do the sort.

ALTER TABLE `track`
ADD KEY `idx_hits` (`hits`);

Let me know if it worked.


why do you use DISTINCT? why do you use LEFT JOIN (insted of JOIN)?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜