Illogical 1

[DarkConsultant]

Hi,

I have two tables, one contains contact information (count = 4500 records) and has a Primary Key of UIN. The other is a members table containing contacts' UIN's (count = 2000 records).

I needed to get all the UINs from the contacts table where the same UIN is NOT in the members table so ...

The quickest query was "SELECT UIN FROM contact WHERE UIN NOT IN (SELECT DISTINCT MemberUIN FROM members)" with a response time of .. wait for it .. 12 seconds.

So then I tried removing the subquery and creating the 'IN' part myself ...

$temp=array();
while($row=mysql_fetch_array($temp,MYSQL_ASSOC)){$x[]=$row['MemberUIN'];}

New query -
"SELECT UIN FROM contact WHERE UIN NOT IN (".implode(",",$x)")";

Response time .. wait for it .. less than .2 seconds (cant measure smaller).

Am I doing something very wrong here?

TIA

David


DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//

 

[r937]

Am I doing something very wrong here?

probably... neglecting to declare an index on MemberUIN

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[DarkConsultant]

Boom:

Cheers buddy ....

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//

 

[ChrisHunt]

Also, you don't need the DISTINCT in the subquery.

An alternative way to do it would be this:

SELECT uin FROM contact MINUS SELECT memberuin FROM members

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[r937]

chris, i don't believe mysql supports the MINUS operator

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon