What would be the best indices for this kind of slow MySQL-Query? (InnoDB)

Question

this kind of MySQL-Query is very slow at the moment.

What would be the best indices for this to speed it up? (InnoDB)

SELECT item_id, 
       Group_concat(storage_nr SEPARATOR ',') AS storage_nr, 
       Group_concat(condition SEPARATOR ',') AS condition, 
       Group_concat(number SEPARATOR ',') AS number, 
       Group_concat(price SEPARATOR ',') AS price, 
       last_calc 
FROM   items
WHERE  number > 0 
       AND bottomlimit IS NOT NULL 
       AND condition IN (1, 2, 3) 
       AND ( price_date IS NULL 
              OR price_date < Date_sub(Now(), INTERVAL 1 hour) ) 
       AND ( NOT ( price = bottomlimit 
                   AND pricebefore = bottomlimit 
                   AND pricebefore2 = bottomlimit ) 
              OR price IS NULL 
              OR pricebefore IS NULL 
              OR pricebefore2 IS NULL 
              OR Date(price_date) <> Curdate() ) 
GROUP  BY item_id 
ORDER  BY last_calc 
LIMIT  20

Thanks a lot in advance!

Best regards!

My first inclination is hopeless. I think your query has to process a lot of data, and that is just necessary. — Gordon Linoff, Jul 27 '17 at 18:56
I already had single indices for number, item_id, last_calc, bottomlimit and condition. I'm not sure if it would be better to create a group indicie. — Webbox, Jul 27 '17 at 19:03
And which `last_calc` are you expecting to see in your results...because that (the values used for non-aggregated, non-grouped fields, in a grouping query) is not formally defined by MySQL. — Uueerdo, Jul 27 '17 at 19:05
I will be sure to get anytime the oldest processed entry of the table - last_calc would be set with the actual timestamp. — Webbox, Jul 27 '17 at 19:10
Is `item_id` the `PRIMARY KEY`? If so, get rid of the `GROUP BY`. Or is there more than one row per `item_id`? — Rick James, Jul 27 '17 at 23:44

score 0 · Answer 1 · answered Jul 27 '17 at 19:10

I'm inclined to agree with Gordon's comment for the most part, but one thing you could try is conditional aggregation. (This could be one of those unique scenarios where processing more of the data and discarding what you don't need is faster than filtering to what you do need, especially since it seems some OR conditions tend to wreck index use in MySQL).

Something similar to this might help.

SELECT item_id
       , Group_concat(
           IF(condition IN (1, 2, 3) 
               AND ( price_date IS NULL OR price_date < Date_sub(Now(), INTERVAL 1 hour) ) 
               AND ( NOT ( price = bottomlimit AND pricebefore = bottomlimit AND pricebefore2 = bottomlimit ) 
                     OR price IS NULL 
                     OR pricebefore IS NULL 
                     OR pricebefore2 IS NULL 
                     OR Date(price_date) <> Curdate() 
                   ) 
              , storage_nr, NULL)
           SEPARATOR ','
         ) AS storage_nr 
       , [etc...]
FROM   items
WHERE  number > 0 AND bottomlimit IS NOT NULL 
GROUP  BY item_id 
HAVING storage_nr IS NOT NULL
ORDER  BY last_calc 
LIMIT  20

Thanks, i try this, but the strange is - this table runs before with MyISAM much more faster than now with InnoDB - can this be normal? — Webbox, Jul 27 '17 at 19:27
Did you shrink `key_buffer_size` and increase `innodb_buffer_pool_size`? — Rick James, Jul 27 '17 at 23:46

score 0 · Answer 2 · answered Jul 27 '17 at 19:30

You only hope to use indexed is to break down your complex query in a UNION of more simple and indexed query. First of all, you can use the FROM [subquery] syntax

SELECT item_id, 
       Group_concat(storage_nr SEPARATOR ',') AS storage_nr, 
       Group_concat(condition SEPARATOR ',') AS condition, 
       Group_concat(number SEPARATOR ',') AS number, 
       Group_concat(price SEPARATOR ',') AS price, 
       last_calc 
FROM   

SUBQUERY

AS items

GROUP  BY item_id 
ORDER  BY last_calc 
LIMIT  20

This could be your subquery:

SELECT *
FROM items
WHERE number > 0 
AND bottomlimit IS NOT NULL 
AND condition IN (1, 2, 3) 
AND price_date IS NULL 
AND ( NOT ( price = bottomlimit 
                   AND pricebefore = bottomlimit 
                   AND pricebefore2 = bottomlimit ) 
              OR price IS NULL 
              OR pricebefore IS NULL 
              OR pricebefore2 IS NULL 
              OR Date(price_date) <> Curdate() ) 

UNION ALL

SELECT *
FROM items
WHERE number > 0 
AND bottomlimit IS NOT NULL 
AND condition IN (1, 2, 3) 
AND price_date < Date_sub(Now(), INTERVAL 1 hour)
AND ( NOT ( price = bottomlimit 
                   AND pricebefore = bottomlimit 
                   AND pricebefore2 = bottomlimit ) 
              OR price IS NULL 
              OR pricebefore IS NULL 
              OR pricebefore2 IS NULL 
              OR Date(price_date) <> Curdate() )

Define the following index

- condition
- price_date
- bottomLimit

Please give me a feedback on the results. Thanks.

score 0 · Answer 3 · answered Jul 28 '17 at 08:36

0

Thanks for helping me!

The best solution for me was after testing everything to convert the table to MyISAM.

With this change i can speed the query up 3 to 5 times faster - from round about 12 seconds to less then 3 seconds.

answered Jul 28 '17 at 08:36

Webbox

25
3

Had you increased `innodb_buffer_pool_size` then you wouldn't have to convert anything and would get better or same performance as with MyISAM. – Mjh Jul 28 '17 at 08:39

What would be the best indices for this kind of slow MySQL-Query? (InnoDB)

3 Answers3