MySQL select with multiple many to many joins causing very slow query

Question

Using the following table structure:

Items (~20,000 records)

• item_id

Properties (~30 records)

• property_id

Item_properties (~40,000 records)

• id
• property_id
• item_id

A user can choose to filter items by a number of fields in the items table itself, and can also choose any number of properties that the item must have. The search needs to choose an item with all properties, not just one of them. I'm currently using the format

SELECT item.field...
FROM items
INNER JOIN item_properties AS ip1 ON ip1.item_id=item.item_id and ip1.property_id=3
INNER JOIN item_properties AS ip2 ON ip2.item_id=item.item_id and ip2.property_id=4
INNER JOIN item_properties AS ip3 ON ip3.item_id=item.item_id and ip3.property_id=5
INNER JOIN item_properties AS ip4 ON ip4.item_id=item.item_id and ip4.property_id=6
etc...
WHERE item.something_else='words'
GROUP BY item_id

I have also tried, as a way of specifying search purely by WHERE rather than by JOIN

SELECT item.field...
FROM items
WHERE item.something_else='words'
and item_id IN (select item_id from item_properties where property_id=3)
and item_id IN (select item_id from item_properties where property_id=4)
and item_id IN (select item_id from item_properties where property_id=5)
and item_id IN (select item_id from item_properties where property_id=6)
etc...

However this approach seemed, if anything, to take even longer to query the set. With about 4 properties chosen query time is about 4-5s, much more and the queries tend to get killed or bring the MySQL server down altogether.

As far as I am aware all the _id fields are indexed on each table, being the primary keys of their respective tables too.

Are there ways to improve the query or might I need to limit the number of options that can be queried?

Answer 1

Use post aggregation filtering if you want all property_id

SELECT item.field
FROM items
INNER JOIN item_properties AS ip1 ON ip1.item_id=item.item_id and  
and ip1.property_id IN(3,4,5,6)
WHERE item.something_else='words'
GROUP BY item.field
HAVING COUNT(DISTINCT property_id )=4

4 is the number of property_id IN(3,4,5,6)

Answer 2

I think you just need an index on item_properties:

create index idx_item_properties_2 on item_properties(item_id, property_id)