SQL Query Looking for optimisation for complex query with indexes

$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.

Now 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.

Both mediaid, userid, 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

-------------Problems Reply------------

You could change the query to a left join

d1.* from $database1 d1
left join $database2 d2 on d2.mediaid = d1.mediaid
and d2.uid='$userid'
where d1.userid!='$userid'
and d2.mediaid is null
ORDER BY d1.active LIMIT 80

The following indexes would be needed as well, change the database1 and 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.

Category:php Views:2 Time:2018-03-10
Tags: php mysql sql

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.288 (s). 11 q(s)