# Please suggest me how to improve this search query

**URL:** <https://forums.percona.com/t/please-suggest-me-how-to-improve-this-search-query/959>\
**Category:** Other MySQL® Questions\
**Created:** [October 11, 2008, 9:31pm UTC](https://forums.percona.com/t/please-suggest-me-how-to-improve-this-search-query/959 "2008-10-11T21:31:41Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![yellow1912](https://avatars.discourse-cdn.com/v4/letter/y/ce7236/32.png) [@yellow1912](https://forums.percona.com/u/yellow1912)\
**Post date:** [October 11, 2008, 9:31pm UTC](https://forums.percona.com/t/please-suggest-me-how-to-improve-this-search-query/959/1 "2008-10-11T21:31:41Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![malonso](https://avatars.discourse-cdn.com/v4/letter/m/eb8c5e/32.png) [@malonso](https://forums.percona.com/u/malonso)\
**Post date:** [October 13, 2008, 8:07pm UTC](https://forums.percona.com/t/please-suggest-me-how-to-improve-this-search-query/959/2 "2008-10-13T20:07:29Z")

</div>

Hello,

Ok I want to make sure I am understanding you correctly; my brain is a little fried from homework right now so bear with me. You want to retrieve all product\_id’s whose corresponding option\_id is either 2 or 4 correct? So given the example entries you provided the following rows would match:

1 - 2  
1 - 4  
2 - 2

So the resulting product\_id’s would be 1, 1, 2 right? Couldn’t you just do:

SELECT product\_id  
FROM `TESTTABLE`  
WHERE option\_id = 2 or option\_id=4

If you want you could change the where clause to “WHERE option\_id IN (2,4)”. Or am I missing something?

---

<div class="post-metadata">

**Author:** ![yellow1912](https://avatars.discourse-cdn.com/v4/letter/y/ce7236/32.png) [@yellow1912](https://forums.percona.com/u/yellow1912)\
**Post date:** [October 13, 2008, 10:36pm UTC](https://forums.percona.com/t/please-suggest-me-how-to-improve-this-search-query/959/3 "2008-10-13T22:36:33Z")

</div>

Hi malonso,

I wanted AND, not OR. So I want products that have both options (remember this table is a many to many relationship)

Anyhow, I solved it by using mysql along with php (I query all the products that have either options, then I put them in array, and then I use array\_intersect to get the ones that have both options)

Regards

---

<div class="post-metadata">

**Author:** ![AlexN](https://avatars.discourse-cdn.com/v4/letter/a/b9bd4f/32.png) [@AlexN](https://forums.percona.com/u/AlexN)\
**Post date:** [October 14, 2008, 2:17am UTC](https://forums.percona.com/t/please-suggest-me-how-to-improve-this-search-query/959/4 "2008-10-14T02:17:29Z")

</div>

Just a little bit longer.

SELECT products\_id  
FROM `testtable`  
WHERE options\_id in B[/B]  
group by products\_id  
having count(\*) = 2

In WHERE clause you list all values you need  
In having clause you put the number of values in the list.

That’s all.

Ah, yes, BOTH columns should be in primary key.