MySQL Performance: MySQL => Please suggest me how to improve this search query

Please suggest me how to improve this search query [message #3623]

Sat, 11 October 2008 23:01

yellow1912
Messages: 2
Registered: October 2008

Junior Member

Here is the situation:
Lets assume we have a very simple containing products_id and options_id, each pair is unique, but of course 1 products_id can associate with multiple options_id (many to many relationship)
Example:
products_id - options_id
1 - 2
1 - 4
1 - 5
2 - 2
2 - 1

Now I want to get all products id that contain options 2 AND 4, so I write a query like this:

SELECT products_id from TESTTABLE WHERE products_id IN(SELECT products_id from TESTTABLE WHERE options_id = 2) AND options_id = 4

The problem with this is, if I want to filter by say 4 or options_id I will have 4 SELECT IN statements, and my query can run for many seconds.

I wonder which is the best approach to solve this problem? Maybe a mix of php (Im using php) and mysql? Or maybe there is a better sql query for this situation?

Thank you so much!

Report message to a moderator