SQL Queries

--0016368e2a67324d23047b2fb0cd
Content-Type: text/plain; charset=UTF-8

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

--0016368e2a67324d23047b2fb0cd--

Re: SQL Queries

--00163691fbedd9132c047b2fd6bf
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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_i=
d
=3D u.id LIMIT 0,1) =3D 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_i=
d
=3D u.id LIMIT 0,1) =3D 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, דניאל ד=D7=
 ×•×=9F 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, content=
s
>
>
> I want to delete all users from table "users" that don't have notes (SELE=
CT
> ... FROM notes WHERE user_id=3DID) returns empty result.
>
>
> What is the fastest way to do it?
>
> --
> Use ROT26 for best security
>



--=20
Use ROT26 for best security

--00163691fbedd9132c047b2fd6bf--

Re: Re: SQL Queries

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 =3D 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, דניאל ד=D7=
 ×•×=9F 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
> =3D u.id LIMIT 0,1) =3D 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
> =3D u.id LIMIT 0,1) =3D 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, דניאל ד=
נון 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, conten=
ts
>>
>>
>> I want to delete all users from table "users" that don't have notes (SEL=
ECT
>> ... FROM notes WHERE user_id=3DID) 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

Re: SQL Queries

You should be able to do this from within the query. Try the following =
query:

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

Take care,
Floyd

On Dec 20, 2009, at 4:30 PM, דניאל דנ=D7=
•×=9F wrote:

> Hey, Lets assume I got a table named "users".
> It contains id & name.
>=20
> I have another table called "notes" - which contains id, user_id, =
contents
>=20
>=20
> I want to delete all users from table "users" that don't have notes =
(SELECT
> ... FROM notes WHERE user_id=3DID) returns empty result.
>=20
>=20
> What is the fastest way to do it?
>=20
> --=20
> Use ROT26 for best security


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php