MySQL performance issues using ORDER by

1
votes

I am having performance issues, whereby the following is executing in about 2.5 seconds to return only 40 rows:

SELECT DISTINCT song.song_id, song.title, song.length, song.bpm, song.keysig 
FROM song 
INNER JOIN (
    SELECT song_id 
    FROM song_genre 
    WHERE genre_id IN ('25')
) genre1 ON genre1.song_id = song.song_id 
INNER JOIN (
    SELECT song_id 
    FROM song_production 
    WHERE production_id IN ('8')
) production1 ON production1.song_id = song.song_id 
WHERE approved='1' 
ORDER by song.priority DESC, song.song_id DESC 
LIMIT 0, 40

Running the query discarding the ORDER BY executes in 0.01 seconds etc.

I understand the issue is possibly related to the way the information is being counted, due to the JOINS I am using, so perhaps need to nest the query, but I am not 100% how I would go about doing this?

id   select_type   table             type     possible_keys   key       key_len   ref                   rows   Extra
 1   PRIMARY       <derived3>        ALL      NULL            NULL      NULL      NULL                   321   Using temporary; Using filesort
 1   PRIMARY       <derived2>        ALL      NULL            NULL      NULL      NULL                  3424   Using join buffer
 1   PRIMARY       song              eq_ref   PRIMARY         PRIMARY   4         production1.song_id      1   Using where
 3   DERIVED       song_production   ref      PRIMARY         PRIMARY   4                                339   Using index
 2   DERIVED       song_genre        index    NULL            PRIMARY   8         NULL                  3424   Using where; Using index

Table song:

CREATE TABLE `song` (
`song_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` tinytext,
`length` varchar(5) DEFAULT NULL,
`Type` varchar(1) DEFAULT NULL,
`Vocals` varchar(10) DEFAULT NULL,
`Themes` varchar(10) DEFAULT NULL,
`Explicit` varchar(10) DEFAULT NULL,
`timesig` varchar(3) DEFAULT NULL,
`keysig` varchar(250) NOT NULL,
`bpm` int(3) DEFAULT NULL,
`speed` varchar(7) DEFAULT NULL,
`Era` varchar(10) DEFAULT NULL,
`Language` varchar(10) DEFAULT NULL,
`Keywords` varchar(10) DEFAULT NULL,
`description` mediumtext,
`search_description` longtext NOT NULL,
`key` varchar(25) NOT NULL,
`priority` int(2) NOT NULL,
`approved` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`song_id`),
FULLTEXT KEY `description` (`description`),
FULLTEXT KEY `search_description` (`search_description`),
FULLTEXT KEY `title` (`title`),
FULLTEXT KEY `head_desc` (`title`,`search_description`)
) ENGINE=MyISAM 
  AUTO_INCREMENT=1388 
  DEFAULT CHARSET=utf8 ;

Table song_genre:

CREATE TABLE `song_genre` (
`genre_id` int(10) NOT NULL,
`song_id` int(10) NOT NULL, 
PRIMARY KEY (`genre_id`,`song_id`)
) ENGINE=MyISAM 
  DEFAULT CHARSET=latin1 ;

Table song_production:

CREATE TABLE `song_production` (
`production_id` int(10) NOT NULL,
`song_id` int(10) NOT NULL,
PRIMARY KEY (`production_id`,`song_id`)
) ENGINE=MyISAM 
  DEFAULT CHARSET=latin1 ;
3
mysqlperformance
derived table does the damage. Try MySQL view. - Gowri
LIKE '%' ?? Why do you have that? And why do you need the derived tables? - ypercubeᵀᴹ
@ypercube, the derived tables are used in response to a previous question - also, the LIKE '%' has been left in there by mistake, but isn't affecting performance. - Sharpedges
@gowri please can you explain further? - Sharpedges
@Sharpedges: The link shows a different query. Please edit your question with a query that makes sense. As it is, the whole (1st) derived table can be removed altogether. It would also be good if you added the tables' definitions. Knowing what are the Primary Keys and Foreign keys helps. - ypercubeᵀᴹ

3 Answers

2
votes

First steps:

I guess the genre_id, production_id and approved are integer columns and not char, so it's no use to have quotes around numbers like '25'. Unquote them: 25

What happens if you remove the DISTINCT? (we can't know without the tables' structure, Primary, Foreign Keys and Unique constraints). Do you get multiple identical rows in the result? If no, just remove the DISTINCT. If yes, remove it and add GROUP BY song.song_id: 

SELECT song.song_id, song.title, song.length, song.bpm, song.keysig 
FROM song 
  INNER JOIN (
    SELECT song_id 
    FROM song_genre 
    WHERE genre_id IN (25)
  ) genre1 ON genre1.song_id = song.song_id 
  INNER JOIN (
    SELECT song_id 
    FROM song_production 
    WHERE production_id IN (8)
  ) production1 ON production1.song_id = song.song_id 
WHERE approved = 1 

----- GROUP BY song.song_id           --- not needed at all (with these tables)
                                                        --- (and structure)
ORDER BY song.priority DESC, song.song_id DESC 
LIMIT 0, 40 ;

Second steps:

Add useful indexes. An index on (approved, priority, song_id) may help for the query.

You also have the song.song_id column defined as UNSIGNED INT while the song_genre.song_id and song_production.song_id are defined as SIGNED INT. It would be good if you converted those to UNSIGNED INT as well.

I would also add (unique) indexes on (song_id, genre_id) and (song_id, production_id) as well. They may not be useful for this query but you'll most certainly need such indexes in other situations.

Third step:

Try rewriting the query in other ways. Without derived tables for example:

SELECT song.song_id, song.title, song.length, song.bpm, song.keysig 
FROM song 
  INNER JOIN
    song_genre AS genre1 
        ON genre1.song_id = song.song_id 
  INNER JOIN 
    song_production AS production1
        ON production1.song_id = song.song_id 
WHERE song.approved = 1
  AND genre1.genre_id IN (25)
  AND production1.production_id IN (8)
ORDER BY song.priority DESC
       , song.song_id DESC 
LIMIT 0, 40 ;

or with EXISTS:

SELECT song.song_id, song.title, song.length, song.bpm, song.keysig 
FROM song 
WHERE song.approved = 1
  AND EXISTS
      ( SELECT *
        FROM song_genre AS genre1 
        WHERE genre1.song_id = song.song_id 
          AND genre1.genre_id IN (25)
      ) 
  AND EXISTS
      ( SELECT *
        FROM song_production AS production1
        WHERE production1.song_id = song.song_id
          AND production1.production_id IN (8)
      )
ORDER BY song.priority DESC
       , song.song_id DESC 
LIMIT 0, 40 ;

and test which one performs faster.

1
votes

MySQL will run the ORDER BY on all the rows before applying the limit, so if your song table is large, and not properly indexed, then it will still be slow. There is a post on MySQL Performance Blog about some methods you can use to speed up ORDER BY ... LIMIT queries.

I will note that the sub-queries are a bit superfluous, the join will take care of that. You can re-write the query like this:

SELECT DISTINCT song.song_id, song.title, song.length, song.bpm, song.keysig 
FROM song 
JOIN song_genre g
  ON g.song_id = song.song_id 
JOIN song_production p
  ON p.song_id = song.song_id 
WHERE approved='1' 
  AND g.genre_id IN ('25')
  AND p.production_id IN ('8')
ORDER by priority DESC, song_id DESC 
LIMIT 0, 40

And I'm not even sure if you need the DISTINCT on the SELECT, unless you can have multiple songs with the exactly same id/title/length/bpm/keysig values.

-1
votes

Ok, I just rewrite the query :

    SELECT DISTINCT song.song_id, song.title, song.length, song.bpm, song.keysig
FROM song
    INNER JOIN (
        SELECT song_id
        FROM song_genre
        WHERE genre_id LIKE '%'
    ) genre1
        ON genre1.song_id = song.song_id
    INNER JOIN (
        SELECT song_id
        FROM song_production
        WHERE production_id IN ('5')
    ) production1
        ON production1.song_id = song.song_id
WHERE approved='1'
ORDER by song.priority DESC, song.song_id DESC
LIMIT 0, 40

First, I apply the order by on the table song, who have song_id in an index. and I hope priority too. If not, add an index on this, and the order by will not pass by a tempory table and a filesort.

If you don't specific a table for the order by, MySQL can choose the wrong table for doing the order by.