Mysql – Query goes in Copying to tmp table stauts in MySQL and never returns

MySQLperformancequery-performance

I am getting some strange behaviors for my MySQL query status. Here is my query code:

SELECT a.id as art_id, b.album_artist_id,
s.song_title,s.song_seo,a.artist_seo,a.artist_name,
b.album_title, b.album_picture, s.id,s.picture 
FROM tbl_artist_album b, tbl_artists a, tbl_songs_artist_album saa, tbl_songs s
WHERE 1=1
  AND s.id = saa.song_id AND a.id = saa.artist_id 
  AND b.id = saa.album_id AND saa.artist_id != 67 
  AND ( MATCH (s.song_title) AGAINST ('taylor swift*' IN BOOLEAN MODE)) 
  AND (CONCAT(s.song_title) LIKE '%taylor swift%') 
  AND s.song_status = 1 
group by s.id 
ORDER BY s.song_title = 'taylor swift' desc 
LIMIT 50

When I execute above query in Phpmyadmin panel it works fine and returns 0 results with in few seconds.
But When the query is executed from browser by search form on a page , the query goes directly in the "Copy to tmp table" status and never returns anything. 

| 79 | | | | |  632 | Copying to tmp table | select a.id as art_id,b.album_artist_id,s.song_title,s.song_seo,a.artist_seo,a.artist_name,b.album_title, b.album_picture, s.id,s.picture from tbl_artist_album b, tbl_artists a, tbl_songs_artist_album saa, tbl_songs s where 1=1 AND s.id = saa.song_id AND a.id = saa.artist_id AND b.id = saa.album_id AND saa.artist_id != 67 AND s.song_title like 'taylor swift%'  AND s.song_status = 1 group by s.id order by s.song_title = 'taylor swift' desc limit 50 |

I have also attached the screenshot of query execution for referencehere in Phpmyadmin and it work in few seconds but I am confused why it is showing "copy to tmp table" when executed from browser.

Best Answer

(I can't explain why one call was 'fast' and the other was 'slow'.)

The "copying to tmp table" provides virtually no useful info. It may be saying that it is busy shoveling around more than 50 (re: LIMIT) rows collected from the 4 tables. My fix, below, mitigates that.

Try the following rewrite as an attempt to speed things up.

First -- Find the ids for "Taylor Swift" songs. (But would that be in the title??)
Second -- Get the rest of the data and do the JOINs.

The rationale -- Get the complex, slow, parts of the query over with before rummaging around in multiple tables.

SELECT ...
    FROM ( SELECT id FROM tbl_songs
        WHERE ( MATCH (song_title) AGAINST ('taylor swift*' IN BOOLEAN MODE)) 
          AND (CONCAT(song_title) LIKE '%taylor swift%') 
          AND song_status = 1
        ORDER BY s.song_title = 'taylor swift' desc
        LIMIT 50 ) AS s1
    JOIN tbl_songs AS s2  ON s1.id = s2.id
    JOIN ... the-other-tables
        ON ...
    WHERE  ...  the-filtering
    ORDER BY s.song_title = 'taylor swift' desc 
    LIMIT 50

Yes, the ORDER BY and LIMIT are needed twice.

The hope is that MATCH is performed first, leading to much fewer rows to apply the LIKE to. Please provide EXPLAIN SELECT ....

The 1=1 is a common technique for building the WHERE clause. It is harmless, but it would be better to gather an array of AND clauses and put them together at the end, not building a string as you go.

Check innodb_buffer_pool_size -- is it about 70% of available RAM?

Related Question