SELECT
products.*,
products_description.*
FROM
products
LEFT JOIN products_description ON products_description.products_id = products.products_id
LEFT JOIN products_rooms ON products_rooms.products_id = products.products_id
WHERE
products_status = 1
AND ( products_name LIKE '%kitchen%' OR products_rooms.room_id = 5 )
GROUP BY
products.products_id
In this query if I remove either the products_name LIKE '%kitchen%' or products_rooms.room_id = 5 the query is virtually instant. However if I combine them with the “OR” the query takes 8 seconds (reference table is about 7000 records).
Any idea what I’m doing wrong?
3
You have 3 tables, one of them has 7000, it’s unclear how many records are there in the other two and how many tuples there will be in the end.
Your claim is that the two criteria in ( products_name LIKE '%kitchen%' OR products_rooms.room_id = 5 ) are slow together, whereas they are instant otherwise.
This strongly suggests that you have some indexes that quicken the query if one of them is in use, but there are still many records and checking the second criteria will take too long.
So, you will need to consider the creation of indexes upon (product_name, room_id) or the other way around, if these two fields are in the same table. If not, then you will need to consider restructuring your tables in order to better cope with this kind of search.
2