Geek Answers Handbook

MySQL Slow Big Request

I have this big query that I want to optimize, I already optimized it, but it is still slow (> 1s):

select count(DISTINCT if(ps15.specification in ('All Season'),p.products_id,NULL)) as count1 ,count(DISTINCT if(ps15.specification in ('Winter'),p.products_id,NULL)) as count2 ,count(DISTINCT if(ps15.specification in ('Zomer'),p.products_id,NULL)) as count3 ,count(DISTINCT if(ps15.specification in ('Winter 2012'),p.products_id,NULL)) as count4 ,count(DISTINCT if(ps15.specification in ('Zomer 2013'),p.products_id,NULL)) as count5 ,count(DISTINCT if(ps15.specification in ('Winter 2013'),p.products_id,NULL)) as count6 ,count(DISTINCT if(ps15.specification in ('Zomer 2014'),p.products_id,NULL)) as count7 from (products p) inner join (products_to_categories p2c) on (p.products_id = p2c.products_id) inner join (products_attributes pa) on (p.products_id = pa.products_id) inner join (products_options_values pv) on (pa.options_values_id = pv.products_options_values_id) inner join (products_stock ps) on (p.products_id=ps.products_id and pv.products_options_values_id = ps.products_options_values_id2 and ps.products_stock_quantity>0) INNER JOIN products_specifications ps15 ON p.products_id = ps15.products_id AND ps15.specifications_id = '15' AND ps15.language_id = '1' INNER JOIN products_specifications ps10 ON p.products_id = ps10.products_id AND ps10.specifications_id = '10' AND ps10.language_id = '1' where p.products_status = '1' and p2c.categories_id in (72,1,23,100,74,24,33,34,35,77,110,25,45,44,40,41,42,85,76,78,83,102,107,111,119,50,52,81,105,108,112,86,88,87,98,89,90,91,96,79,2,54,60,82,109,115,118,53,58,104,55,101,75,56,64,66,67,68,69,70,71,84,103,114,120,80,92,99,93,94,95,97,106,121) AND ps10.specification in ('Meisje') and products_options_values_name in ( 62,"3M/60cm","56-62","0-4 mnd","3m","0-3m","3-6m","3M","62/68","0-6m","50-62" , 68,"6M/67cm","9M/70cm","4-8 mnd","6m","3-6m","6M","62/68","0-6m" , 74,"4-8 mnd","8-12 mnd","6m","9m","6-9m","6M","9M","74/80","6-12m" );

output:

 +--------+--------+--------+--------+--------+--------+--------+ | count1 | count2 | count3 | count4 | count5 | count6 | count7 | +--------+--------+--------+--------+--------+--------+--------+ | 1 | 289 | 193 | 49 | 192 | 240 | 0 | +--------+--------+--------+--------+--------+--------+--------+

explain mysql outputs:

 +----+-------------+-------+-------+-------------------------------------------+-------------------------------------+---------+---------------------------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+-------------------------------------------+-------------------------------------+---------+---------------------------------------+------+--------------------------+ | 1 | SIMPLE | p | index | PRIMARY,products_id | products_id | 5 | NULL | 4539 | Using where; Using index | | 1 | SIMPLE | p2c | ref | PRIMARY | PRIMARY | 4 | kikleding.p.products_id | 1 | Using where; Using index | | 1 | SIMPLE | ps15 | ref | products_id | products_id | 12 | kikleding.p2c.products_id,const,const | 1 | Using where | | 1 | SIMPLE | ps10 | ref | products_id | products_id | 12 | kikleding.p.products_id,const,const | 1 | Using where | | 1 | SIMPLE | pa | ref | idx_products_attributes_products_id | idx_products_attributes_products_id | 4 | kikleding.p.products_id | 6 | | | 1 | SIMPLE | pv | ref | PRIMARY | PRIMARY | 4 | kikleding.pa.options_values_id | 2 | Using where | | 1 | SIMPLE | ps | ref | idx_products_stock_attributes,products_id | idx_products_stock_attributes | 4 | kikleding.ps15.products_id | 6 | Using where | +----+-------------+-------+-------+-------------------------------------------+-------------------------------------+---------+---------------------------------------+------+--------------------------+

I tried to index most tables, ref still gives NULL in the first line of explanation.

Now it outputs 7 columns, sometimes I need to output 50 columns.

Any tips?

+7
performance mysql
source share
1 answer

ref = NULL means that the rows from table p (i.e. product ) are not connected to other rows. These rows are the first to be selected in your query, and rows from other tables are joined to them again. I always expected the first line of EXPLAIN show ref = NULL .

Basically, your execution plan says:

  • Extract rows from products matching the WHERE
  • Then extract the lines products_to_categories corresponding lines from (1) in the field products.products_id
  • And so on for all tables

Suggested additional indexes:

  • tables (columns)
  • products (product_id, products_status)
  • products_specifications (products_id, specification, language_id, specification_id)
  • products_to_categories (products_id, categories_id)

The first should help reasonably; I would not expect too much from the other two.

I think the problem is that you have a lot of COUNT(IF()) . This is hacking, and the engine is not optimized for this kind of request. Instead, you should strive to return a result set as follows:

 + --------------- + ------- +
 |  specification |  count |
 + --------------- + ------- +
 |  All Season |  1 |
 + --------------- + ------- +
 |  Winter |  289 |
 + --------------- + ------- +
 |  ... |  ... |
 + --------------- + ------- +

Your request will look like this:

 SELECT specification, COUNT(*) FROM products JOIN ... -- your current JOIN list GROUP BY specification -- this is the important bit

... and should be almost instantaneous, even without additional indexes (or, possibly, on products_specifications(products_id, specification) )

+3
source share

More articles:


All Articles