I need to speed up specific mysql query on large table

Question

Hi I know there is a lot of topics dedicated to query optimizing strategies, but this one is so specific I couldnt find the answer anywhere on the interenet.

I have large table of product in eshop (appx. 180k rows) and the table has 65 columns. Yeah yeah I know its quite a lot, but I store there information about books, dvds, bluerays and games.

Still I am not considering a lot of cols into query, but the select is still quite tricky. There are many conditions that need to be considered and compared. Query below

SELECT *
FROM products
WHERE production = 1 
AND publish_on < '2012-10-23 11:10:06' 
AND publish_off > '2012-10-23 11:10:06' 
AND price_vat > '0.5' 
AND ean <> ''
AND publisher LIKE '%Johnny Cash%'
ORDER BY bought DESC, datec DESC, quantity_storage1 DESC, quantity_storege2 DESC, quantity_storage3 DESC
LIMIT 0, 20

I have already tried to put there indexes one by one on cols in where clause and even in order by clause, then I tried to create compound index on (production, publish_on, publish_off, price_vat, ean).

Query is still slow (couple of seconds) and it need to be fast since its eshop solution and people are leaving as they are not getting their results fast. And I am still not counting the time I need to perform the search for all found rows so I can make paging.

I mean, the best way to make it quick is to simplify the query, but all the conditions and sorting is a must in this case.

Can anyone help with this kind of issue? Is it even possible to speed this kind of query up, or is there any other way how I can for example simplify the query and leave the rest on php engine to sort the results..

Oh, Iam really clueless in this.. Share your wisdom peple, please...

Many thanks in advance

Solution

First of all be sure what you want to select and erase the '*'

Select * from

with something more specific

Select id, name, ....

There is no Join or anything other in your table so the speed up options are quite small I think.

1. Check that your mysql Server can use enough memory. Have a look at this confis in your my.cnf

   • key_buffer_size = 384M;
   • myisam_sort_buffer_size = 64M;
   • thread_cache_size = 8;
   • query_cache_size = 64M

2. Have a look a max allowed concurrency. mysql recommends CPU's*2

   thread_concurrency = 4

3. You should really thinks about splitting the table depending on informations you use and on standard normalization. If possible.

4. If it's a productive system with no way to split the tables then think about a caching server. But this will only help if you have a lot of recurring querys that are the same.

This is what I would do when knowing nothing about the underlying implementation or the system at all.

Edit: Making as many columns indexable as you can won't necessarily speed up your system. The more indexes ≠ the more speed.