Hello,
That kind of queries usually run faster using a LEFT JOIN, like this:
select u.id
from users u
left join notes n on u.id = n.user_id
where n.id is null;
That query will give you the ids of the users without notes. Make sure
to have an index on notes.user_id to let the LEFT JOIN use it and run
faster.
Hope that helps, regards,
Jonathan
On Sun, Dec 20, 2009 at 6:41 PM, דניאל דנון <danondaniel@xxxxxxxxx> wrote:
> Sorry for the double-post, forgot to add up the way I thought about using:
>
> Simple sql query:
>
> SELECT * FROM `users` as u WHERE (SELECT COUNT(id) FROM notes WHERE user_id
> = u.id LIMIT 0,1) = 0
>
> Problem is I have about 450,000 "users" and about 90% don't have "notes",
> and it takes LOADS of times even with I limit it:
>
> SELECT * FROM `users` as u WHERE (SELECT COUNT(id) FROM notes WHERE user_id
> = u.id LIMIT 0,1) = 0 LIMIT 0,10
>
> Takes about 10 seconds which is too much time... Any way to optimize it?
>
> On Sun, Dec 20, 2009 at 11:30 PM, דניאל דנון <danondaniel@xxxxxxxxx> wrote:
>
>> Hey, Lets assume I got a table named "users".
>> It contains id & name.
>>
>> I have another table called "notes" - which contains id, user_id, contents
>>
>>
>> I want to delete all users from table "users" that don't have notes (SELECT
>> ... FROM notes WHERE user_id=ID) returns empty result.
>>
>>
>> What is the fastest way to do it?
>>
>> --
>> Use ROT26 for best security
>>
>
>
>
> --
> Use ROT26 for best security
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
