Prev: Checking for internet connection.
Next: MySQL Increment/Decrement
From: =?UTF-8?B?15PXoNeZ15DXnCDXk9eg15XXnw==?= on 20 Dec 2009 16:30
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
From: =?UTF-8?B?15PXoNeZ15DXnCDXk9eg15XXnw==?= on 20 Dec 2009 16:41
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(a)gmail.com> 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
From: Jonathan Tapicer on 20 Dec 2009 17:07
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(a)gmail.com> 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(a)gmail.com> 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
>
From: Floyd Resler on 21 Dec 2009 08:46
You should be able to do this from within the query. Try the following query:

DELETE users.*
FROM users
LEFT JOIN notes
USING(user_id)
WHERE notes.note_id IS NULL

Take care,
Floyd

On Dec 20, 2009, at 4:30 PM, דניאל דנון 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

 | 
Pages: 1
Prev: Checking for internet connection.
Next: MySQL Increment/Decrement