CODEWITHSUNDEEP
mysqlsql
Peter Graf
Peter Graf

Reputation: 23

MySQL query with "not exists" takes too long

I have a table like this:

u

My query is 

SELECT *, 
       week (pdate,3) 
  FROM pubmed
 where not exists (select 1 
                     from screened 
                    where suser=86
                      and ssearch=pubmed.aid)
 order by pdate desc

Screened has only 30000 records, but the query takes several minutes.

Pubmed.aid is the primary index.

I think I have created all the indexes I can use. Any ideas?

Thank you.

Upvotes: 1

Views: 1927

Answers (1)

radar
radar

Reputation: 13425

I couldn't format it in comments, adding it here try LEFT JOIN

   SELECT P.*,week (P.pdate,3) 
   FROM pubmed P
   LEFT JOIN screened S
   ON S.ssearch = P.aid 
   AND S.suser = 86
   WHERE S.ssearch is NULL
   order by P.pdate

Upvotes: 3

Related Questions