Is it possible to optimize this query?

vigour

Dear list!

I'm building a picture database. Every picture have 7 keywords. I can search for the pictures by selecting 1 to 7 keywords from a list.

This query takes too long time, up to 10-15 seconds. Is it possible to optimize it so I can search for the pictures faster?

SELECT bild, product_id, COUNT(product_id) AS COUNT FROM `products` LEFT JOIN kw_t_p ON product_id = p_nr LEFT JOIN keyword ON kw_id = kw_nr WHERE (kw_label = 'Barn') OR (kw_label = 'Barnvagn') OR (kw_label = 'Bord') OR (kw_label = 'Båt') OR (kw_label = 'Bänk') OR (kw_label = 'Byggnad') OR (kw_label = 'Bilderna') GROUP BY product_id HAVING COUNT >= 7 ORDER BY bild ASC LIMIT 0, 16

Or maybe there are better ways to search?

Piranha

Do you have indexed the search columns? If not you should do that.

What version of MySQL do you use, it will make a pretty big difference.

If I understand the query you want to get the pictures where all the keywords match, is that right?

Do you use the count or is it just used to select where all the keywords match? I guess that it is the later.

What type of data is bild? If it is an actual picture I don't think it is a good idea to order by it, you probably are better off to order by product_id.

Something like this might help, but I'm not sure.

SELECT bild, product_id // don't return count
FROM `products` 
LEFT JOIN kw_t_p ON product_id = p_nr 
LEFT JOIN keyword ON kw_id = kw_nr 
WHERE (kw_label = 'Barn') OR (kw_label = 'Barnvagn') OR (kw_label = 'Bord') OR (kw_label = 'Båt') OR (kw_label = 'Bänk') OR (kw_label = 'Byggnad') OR (kw_label = 'Bilderna') 
GROUP BY product_id 
HAVING COUNT(p_nr) >= 7  // changed what to count, hopefully will make some difference
ORDER BY product_id ASC  // order by another column
LIMIT 0, 16

vigour

Piranha,

MySQL version is 4.1.11

I will make some tests today with your altered query and see where it takes me.

'Bild' is just a path to a picture.

Every field I search on is indexed. I have 350000 rows in the kw_t_p table right now.

Yes, I want to get the pictures where all the keywords match. If one of the 7 keywords does not match, it should not return that row. The user will use everything from 1 to 7 keywords when seaching so my app builds the query string dynamically. That already works.