Slow MySQL query and order by

[sharp parcel]

Hello, we are running MySQL database and php application on 2 different servers. (from digitalocean).

What we tried?

• we added index on stock_status which can be only 3 values (out_of_stock, available_in_stock, available_to_order). It improved the response time.

• We also deployed this fix to live server and found out, that the result is not the same as in testing. (On local with exported DB average response time is 600ms, on live server is around 2s)

We dug little bit further and found, that if we order by 2 columns, then the response time increases. Is there any solution how to increase speed? We know that there is somethinf like multicolumn index, but we do not know, if this would solve our problem - we do not have this much expertise in MySQL.

Im sending to you the query which takes the most time.

Note: catalog column is a json column.

Thank you for any help!

select
  `products`.`id`,
  `products`.`uuid`,
  `products`.`name`,
  `products`.`perex`,
  `products`.`slug`,
  `products`.`group_code`,
  `products`.`custom_meta`,
  `products`.`has_variants`,
  `products`.`is_available_for_order`,
  `products`.`order_availability`,
  `products`.`stock_status`,
  `products`.`actual_stock_count`,
  `products`.`retail_price`,
  `products`.`retail_price_eur`,
  `products`.`retail_price_discounted`,
  `products`.`retail_price_discounted_eur`,
  `products`.`discount_percentage_eur`,
  `products`.`product_category_id`,
  `products`.`product_brand_id`,
  `products`.`stock_item_id`,
  `products`.`product_badge_id`
from
  `products`
where
  `catalog` like '%catalog%'
  and `stock_status` != 'out_of_stock'
  and (
    exists (
      select
        *
      from
        `product_categories`
        inner join `product_product_categories` on `product_categories`.`id` = `product_product_categories`.`product_category_id`
      where
        `products`.`id` = `product_product_categories`.`product_id`
        and `product_categories`.`id` = 720
        and `product_categories`.`deleted_at` is null
    )
    or `product_category_id` = 720
  )
  and `is_concept` = 0
  and `show_in_listing` = 1
  and `retail_price_discounted_eur` <= 7.6861433640456E + 18
  and `products`.`deleted_at` is null
order by
  `stock_status` asc,
  `created_at` desc
limit
  24 offset 0

[tough phoenix]

We can't really guess why such a query would be slow. You could chain a ->explain() and dump that, then you could see what might cause the issue

[willow frost]

Yeah it's hard to help since we don't how many rows the tables have, and which columns are indexed. But I would try adding an index for created_at.

[sharp parcel]

Indexed columns:

In products table we have 44k rows

[willow frost]

Again, i'd try with an index on created_at

[tough phoenix]

Still, indexes wouldn't tell much either. We know nothing of the cardinality, we don't know if indexes are used etc. Like I said in my initial reply; chain ->explain(), dump that and read through that, it'll explain the query, it will show which indexes are used. Could even just dump it here https://explainmysql.com/ and see if you get any valuable information from that

Too many or bad indexes could have an adverse effect, just randomly throwing more indexes at it might not fix the issue. Might make it worse. Especially if you don't know what is causing it to be slow in the first place. That's just throwing shit at the wall and seeing what sticks

[sharp parcel]

Thank you @tough phoenix i will look at it.

@tough phoenix you probably speak about indexes on screen. Almost all of indexes starting with idx_ are virtual columns from json columns

[willow frost]

But my gut feeling is that and (exists(...) or ...) is the culprit. I'm no expert, but I've had some cases where adding an or ... to a clause impacted performance significantly because it meant MySQL couldn't use the index I expected it to use.

And sometimes splitting a big complicated query into a few simpler queries can improve performance a lot.

[sharp parcel]

Thank you guys, we will look at your suggestions.