MySQL Forums
Forum List  »  Optimizer & Parser

Optimizing query with having and count
Posted by: bw h ontwerpers
Date: January 13, 2011 05:58AM

Can anybody help me with optimizing this Query?
I want to get all the item_id's with a variable nr of linked options.

The main table is items, Then there is a second table with filteroptions. The filteroptions are linked to items in a many to many relation by the table filteroptionslinks.

Table structure:

----items----
item_id
....

--filteroptionslinks--
option_id
item_id

--filteroptions--
option_id
...

SELECT fol.item_id
FROM filteroptionslinks fol
INNER JOIN items ON fol.item_id = items.item_id
WHERE items.publish = 'Y'
AND fol.option_id = 112
AND fol.option_id = 115
GROUP BY fol.ITEM_ID
HAVING count(fol.item_id) = 2


Then i need an addition to count the nr of results of this query. Currently i can't do this by saying SELECT COUNT(fol.item_id) because this will just return multiple rows with the value 1.

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimizing query with having and count
2399
January 13, 2011 05:58AM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

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.