$query = "SELECT * FROM $database1 WHERE userid!='$userid' AND mediaid NOT IN (SELECT mediaid FROM $database2 WHERE uid='$userid') ORDER BY active ASC LIMIT 80";
This query was working very well until now.
That it suddenly takes 0.5 to sometimes even 3s to execute, sometimes it also goes down to 0.1 which is acceptable.
database2 has around 3 millionn rows and
database1 around 500, but it also sometimes is slow when only 100 items in
database1. I'm worried because
database2 is getting around 30k new rows each day.
uid are all indexes.
Server? 8cores x 3,2, 16gb ram. Scalable cloud. Average load is fine. not more than 20% cpus
EDIT: Explain Select Returned following data:
1 PRIMARY database1 index userid active 4 NULL 80 Using where 2 DEPENDENT SUBQUERY database2 index_subquery uid,mediaid mediaid 130 func 93 Using where
You could change the query to a left join
d1.* from $database1 d1
left join $database2 d2 on d2.mediaid = d1.mediaid
and d2.mediaid is null
ORDER BY d1.active LIMIT 80
The following indexes would be needed as well, change the
database2 to real table names
alter table `database1` add index ua_idx(userid,active);
alter table `database1` add index mid_idx(mediaid);
alter table `database2` add index u_mid_idx(mediaid,uid);
Finally, i have re-writed query to download both data for current user id. Then calculate it in php. Now it's quite fast. x10 faster. For now it's fine.