CODEWITHSUNDEEP
mysqldatabasedatabase-optimization
Julian
Julian

Reputation: 25

How to optimize this query? Slow query

Hi I am Trying to Optimize this Query. If there are a lot of transactions in the timeframe it can take up to 10 Seconds to execute on my local environment. I tried to create a Index on the created_at column but it doesn't solve the issue if there are a lot of rows in the table (my table has only 4m rows). Can someone recommend some optimization tips?

select 
   count(*) as total,
   trader_id 
from 
  (select * 
   from `transactions`
   where `created_at` >= '2018-05-04 10:54:00'
   order by `id` desc)
  as `transactions`
 where
   `transactions`.`market_item_id` = 1
    and `transactions`.`market_item_id` is not null
    and `gift` = 0
 group by `trader_id`;

Edit:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra

1   SIMPLE  transactions    NULL    range   transactions_market_item_id_foreign,transactions_trader_id_foreign,transactions_created_at_index    transactions_created_at_index   5   NULL    107666  2.41    Using index condition; Using where; Using MRR; Using temporary; Using filesort

Upvotes: 0

Views: 219

Answers (2)

Bohemian
Bohemian

Reputation: 425198

Delete the (unnecessary) inner query:

select 
  count(*) as total,
  trader_id 
from transactions
where created_at >= '2018-05-04 10:54:00'
and market_item_id = 1
and gift = 0
group by trader_id

Notes:

  • removed inner query, which was unnecessary, added cost, massively increased temp storage requirements and usage and prevented other conditions from being used by any indexes
  • removed order by, which would have cost a lot but made zero difference to the result
  • removed market_item_id is not null condition, because market_item_id = 1 already asserts that
  • removed backticks, because I don’t like them

Upvotes: 2

Ankit Bajpai
Ankit Bajpai

Reputation: 13519

The better version of Bohemian query is here - 

SELECT count(*) as total
      ,trader_id
FROM `transactions`
WHERE `created_at` >= '2018-05-04 10:54:00'
AND `market_item_id` = 1
AND `gift` = 0
GROUP BY `trader_id`

Upvotes: -1

Related Questions