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

Members

Help

Home

Home » Performance » MySQL » Please suggest me how to improve this search query

Show: Today's Messages :: Show Polls :: Message Navigator

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

Re: Please suggest me how to improve this search query [message #3625 is a reply to message #3623 ]

Mon, 13 October 2008 21:37

malonso
Messages: 3
Registered: July 2008

Junior Member

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?

Report message to a moderator

Re: Please suggest me how to improve this search query [message #3626 is a reply to message #3625 ]

Tue, 14 October 2008 00:06

yellow1912
Messages: 2
Registered: October 2008

Junior Member

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

Report message to a moderator

Re: Please suggest me how to improve this search query [message #3627 is a reply to message #3623 ]

Tue, 14 October 2008 03:47

AlexN
Messages: 20
Registered: October 2007
Location: Moscow,Russia

Junior Member

Just a little bit longer.

SELECT products_id
FROM `testtable`
WHERE options_id in (2,4)
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.

Report message to a moderator

Previous Topic:	mysql performance on IBM Power6 systems
Next Topic:	Table data covered entirely by indexes

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 7 20:06:08 EST 2009

Total time taken to generate the page: 0.00707 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.