Need help with SQL statement

Anon

Can someone look at this sql statement posted to MySql, there should be fewer then 200 records returned and it is taking forever.

Two Tables

images:

index on imageid - primary
index on org_name - normal

Image_cat
Index on imageid = normal
Image_cat is a many to many table

Query

Select Distinct images.image_name, images.imagid, images.inks,images.org_name,image_cat.catid

FROM images_cat, images

WHERE

image_cat.catid='3' AND image_cat.imageid=images.imageid

OR images.org_name='Delta';

IF I change the OR to and AND the response snaps back, chaning to OR is slow and returns incorrect values.

Any guidence is appreciated
Keith

calebsg

I'm not totally sure here, but I think you may get some benefit from using some brackets around your AND OR statements so that the db knows exactly what the OR refers to. Could be the ambiguity is slowing things down...

Try that, see if it helps.

CalebSG

Anon

Do you mean like this?

Select images.image_name,images.imageid,images.inks, images.org_name, image_cat.catid
From image_cat, images
where (image_cat.catid='3' AND image_cat.imageid = images.imageid)
OR( images.org_name ='Delta Delta Delta');

This generates the same response SLOW and incorrect

tomhath

CalebSG was on the right track. The OR is evaluated after the AND, so there is no matching on imageid. What you have is called the cartesian product of every in images_cat with every row in images with an org_name of Delta. This is probably what you want instead:

Select Distinct images.image_name, images.imagid, images.inks,
images.org_name,image_cat.catid
FROM images_cat, images

WHERE image_cat.imageid=images.imageid
AND (image_cat.catid='3' OR images.org_name='Delta');

Anon

On the MySQL console, you can type "EXPLAIN SELECT etc. etc." and it gives you a nice explanation of what's happening when it runs your query, which can help you out in problems like this.