php - SQL Query Looking for optimisation for complex query with indexes -

$query = "select *            $database1            userid!='$userid'              , mediaid not in (select mediaid                                  $database2                                  uid='$userid')           order active asc limit 80"; 

this query working until now.

that takes 0.5 3s execute, goes down 0.1 acceptable.

now database2 has around 3 millionn rows , database1 around 500, slow when 100 items in database1. i'm worried because database2 getting around 30k new rows each day.

both mediaid, userid, uid indexes.

server? 8cores x 3,2, 16gb ram. scalable cloud. average load fine. not more 20% cpus

edit: explain select returned following data:

1   primary database1   index   userid  active  4   null    80  using 2   dependent subquery  database2   index_subquery  uid,mediaid mediaid 130 func    93  using 

you change query left join

select d1.* $database1 d1 left join $database2 d2 on d2.mediaid = d1.mediaid , d2.uid='$userid' d1.userid!='$userid'  , d2.mediaid null order d1.active limit 80 

the following indexes needed well, change database1 , database2 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);