CODEWITHSUNDEEP
mysqloptimization
Peibol
Peibol

Reputation: 147

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

Upvotes: 2

Views: 5488

Answers (4)

Richard
Richard

Reputation: 5880

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.

Upvotes: 5

Nothus
Nothus

Reputation: 1167

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.

Upvotes: 1

Karoly Horvath
Karoly Horvath

Reputation: 96258

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

Upvotes: 0

Abhay
Abhay

Reputation: 6645

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.

Upvotes: 0

Related Questions