can this query be optimized

i am busy with a pricewatch script/page and i am trying to optimize my qeurys. For some I have changed the db setup and that works fine. Only query for now is the following one. The query should get the latest price of the combination webshop, product and size. For now this is the query that takes longest, and I would like to optimize it. This is an example of 1 product and size.

select `pw_id`, `price`, `prices`.`webshop_id`, `last_update` from prices, (select webshop_id,max(tupdated_at) as last_update from prices where product_id=3 and size=50 GROUP BY webshop_id) last_updates where `prices`.`product_id` = 3 and `prices`.`size` = 50 and `prices`.`webshop_id` = `last_updates`.`webshop_id` and `prices`.`tupdated_at` = `last_updates`.`last_update` order by `price` asc

Table setup

  `pw_id` bigint unsigned NOT NULL,
  `price` float DEFAULT NULL,
  `size` float DEFAULT NULL,
  `product_id` bigint unsigned NOT NULL,
  `tupdated_at` bigint unsigned DEFAULT NULL,
  `webshop_id` bigint unsigned NOT NULL,

Is there maybe a better query to get a list of latest prices for a product/size?

Tray2

3 years ago

Level 73

Yes, first off, never ever store monetary values as float, a float is not exact. You should use either an int and multiply the price so you don't get any decimal points (3.60 * 100 = 360) or store it as decimal which is an exact value.

What the hell is tupdated_at ?

Now to business.

Write the word EXPLAIN in front of the select.

It will tell you what takes time and, most likely that you should add indexes to product_id, size and price.

You should probably do the joins before any filters

select
  `pw_id`,
  `price`,
  `prices`.`webshop_id`,
  `last_update`
from
  prices,
  (
    select
      webshop_id,
      max(tupdated_at) as last_update
    from
      prices
    where
      product_id = 3
      and size = 50
    GROUP BY
      webshop_id
  ) last_updates
where
  `prices`.`webshop_id` = `last_updates`.`webshop_id`
  and `prices`.`tupdated_at` = `last_updates`.`last_update`
  and `prices`.`product_id` = 3
  and `prices`.`size` = 50
 
order by
  `price` asc

Please or to participate in this conversation.