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
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.
You can try something like this, should be faster. This is not the best method, but may be easiest to implement into your PHP.
SELECT subscriber. * , track . *
FROM track, subscriber
JOIN (
SELECT 2803 as logged_in
UNION
SELECT 2806
UNION
SELECT 54
UNION
SELECT 64
) list
ON track.logged_in = list.logged_in
WHERE track.type =1 AND
subscriber.post_id = track.post_id AND
track.nmade = subscriber.nmade AND
subscriber.userid = '54'
ORDER BY track.date_created DESC
LIMIT 5