MySQL Forums
Forum List  »  Performance

Another slow IN query
Posted by: Peter Carlson
Date: March 15, 2011 08:11PM

Ok, so I moved my IN query to a UNION (thanks to help from the forum!!!) and it's much better. However now I have a NOT IN query and I am lost (sigh) again.

The basic structure is a table of Homes (with various attributes, etc)
A second table contains only Pictures of Homes
`id` int(8) NOT NULL AUTO_INCREMENT,
`Home` int(8) DEFAULT NULL,
`p1` mediumblob,
`p2` mediumblob,
`p3` mediumblob,
`p4` mediumblob,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1081 DEFAULT CHARSET=latin1 |

So I need to do a query of Homes.* where there is either no record in HomePhotos, or there is a record in HomePhotos but p1 or p2 or p3 is null (missing a photo)

The current query I use is:
select * from Homes where id not in (select home from HomePhotos where p1 is not null and p2 is not null and p3 is not null)

But this is brutally slow. I dont understand UNION enough to be able to put this query together.

Thanks for your help!

Peter

Options: ReplyQuote


Subject
Views
Written By
Posted
Another slow IN query
1750
March 15, 2011 08:11PM
685
March 16, 2011 12:38AM
593
March 16, 2011 07:41AM
612
March 16, 2011 09:13AM
648
March 17, 2011 08:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.