MYSQL：WHERE IN（）速度问题，用于Query中的大比较

Question

I have a serious issue with WhERE IN () condition in mysql query. I am fetching RESULT fron two tables which have not more than 10 fields. I created primary fields in both.

I need to use WHERE IN () to see logged_in IDs which can be more than 500 or long. So it creates a heavy query and it fetches records too slow. When there are logged_in IDs are less than 20 or so its fine but when they are more than 50 or 100 or more it becomes more slow and takes much time to process.

So, my problm is how to make it fast? Am i using wrong technique? I mean should I use something else on place of where IN()? Any idea would be highly appreciated.

SELECT subscriber. * , track . * 
FROM track, subscriber 
WHERE track.type =1 AND 
      track.logged_in IN ( 2803, 2806, 54, 54, 64, 383, 833, 2808, 2809, 2810, 56, 2811, 2812, 2813, 2814, 2815, 2816, 2817, 2818, 2819, 2820, 2821, 2822, 2823, 2824, 2825, 2826, 2827, 2828, 2829, 2830, 2831, 2832 ) AND 
      subscriber.post_id = track.post_id AND 
      track.nmade = subscriber.nmade AND 
      subscriber.userid =  '54' 
ORDER BY track.date_created DESC 
LIMIT 5

Answer 1

The IN clause in mysql isn't famous by its speed ... maybe if you create a temporal table with the id's that you want and you do a JOIN with track table the operation will be faster.

The temporaries tables are per session, therefore you don't need spend effort dropping them after use, when you close the connection to mysql, they will be removed by the mysql.

Anyway if you want have reasonable performance in the JOIN operation you will need index the logged_in field.