Optimise mysql subquery for multiple columns

Question

I have one show table which have 25 lakh records in it. Columns in show tables is id, showid, artist_id, cityid, created.

If same show is happening for a period of 4 days. Then in system data will be like :

id, showid, artist_id, cityid, created for each show, showid will remain same.

1 245 1 3 2011-12-30 15:00:00

2 245 1 3 2011-12-30 13:00:00

3 245 1 3 2011-12-30 19:00:00

SELECT UT.user_id,UT.type_id, UT.type ,UT.track_time, 
(SELECT showid FROM shows WHERE FIND_IN_SET(UT.type_id,artist_id) ORDER BY created DESC LIMIT 1)   as mainshowid,
(SELECT cityid FROM shows WHERE FIND_IN_SET(UT.type_id,artist_id) ORDER BY created DESC LIMIT 1)   as maincityid
FROM user_details as UT  WHERE UT.type='artist' ORDER BY UT.track_time desc limit 20;

Query is giving the desire output But how i can get multiple columns in single SUB query or by any other way.

score 0 · Accepted Answer · answered Jan 24 '12 at 21:19

0

You can:

SELECT 
    UT.user_id,
    UT.type_id, 
    UT.type,
    UT.track_time, 
    s.showid AS mainshowid,
    s.cityid AS maincityid
FROM 
      user_details AS UT 
  JOIN
      shows AS s
    ON s.showid =
       ( SELECT showid 
         FROM shows 
         WHERE FIND_IN_SET(UT.type_id, artist_id)
         ORDER BY created DESC 
         LIMIT 1
       ) 
WHERE UT.type='artist' 
ORDER BY UT.track_time DESC 
LIMIT 20 ;

answered Jan 24 '12 at 21:19

ypercubeᵀᴹ

113,259
19
174
235

Thanks it works. But can we optimize it. SELECT showid FROM shows WHERE FIND_IN_SET(UT.type_id, artist_id) ORDER BY created DESC LIMIT 1 In shows table i have 15 million rows. Because if i run it after removing LIMIT 20 it takes lot of time – metalfight - user868766 Jan 25 '12 at 06:15
The use of `FIND_IN_SET()` suggests that you have a comma separated column. That is not good. Read here why: [Is storing a comma separated list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad). – ypercubeᵀᴹ Jan 25 '12 at 12:15
If you want performance, then you should normalize the tables. – ypercubeᵀᴹ Jan 25 '12 at 12:15

Optimise mysql subquery for multiple columns

1 Answers1