SQL query with LEFT JOIN ... AS d WHERE d.id IS NULL seems so slow

[jamiesensei]

Hi all,

 

I have a db with 7000 user records (in mdl_user) in it and around 7,000 records from another table of 28,000 records of related data (in mdl_user_info_data). I want to do a query to find all users with no related data in the second table. I tried two queries using phpmyadmin. First :

 

SELECT * FROM `mdl_user` AS u LEFT JOIN `mdl_user_info_data` as d ON (u.id=d.userid AND d.fieldid=1) WHERE d.id IS NULL

 

Then I tried a using a sub select :

 

SELECT *

FROM `mdl_user` AS u

WHERE NOT

EXISTS (

 

SELECT *

FROM `mdl_user_info_data` AS d

WHERE (

u.id = d.userid

AND d.fieldid =1

)

)

LIMIT 0 , 30

 

The first query took 229 seconds and the second took about the same.

 

Is it normal that these queries should take so long. Is there some way that I can improve the queries so that mysql can optimize them better??

 

 

[aschk]

Yes, if you're not got any indexes defined...