How I can optimize query with where index?

Question

I have query

select `price`, `asset_id` 
from `history_average_pairs` 
where `currency_id` = 1 
  and date(`created_at`) >= DATE_SUB(NOW(), INTERVAL 7 DAY) 
group by hour(created_at), date(created_at), asset_id 
order by `created_at` asc

And table

CREATE TABLE IF NOT EXISTS history_average_pairs (
  id bigint(20) unsigned NOT NULL,
  asset_id bigint(20) unsigned NOT NULL,
  currency_id bigint(20) unsigned NOT NULL,
  market_cap bigint(20) NOT NULL,
  price double(20,6) NOT NULL,
  volume bigint(20) NOT NULL,
  circulating bigint(20) NOT NULL,
  change_1h double(8,2) NOT NULL,
  change_24h double(8,2) NOT NULL,
  change_7d double(8,2) NOT NULL,
  created_at timestamp NOT NULL DEFAULT current_timestamp(),
  updated_at timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  total_supply bigint(20) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE history_average_pairs
  ADD PRIMARY KEY (id),
  ADD KEY history_average_pairs_currency_id_asset_id_foreign (currency_id,asset_id),
ALTER TABLE history_average_pairs
  MODIFY id bigint(20) unsigned NOT NULL AUTO_INCREMENT;

It contains more than 10 000 000 rows, and query takes

Showing rows 0 - 24 (32584 total, Query took 27.8344 seconds.)

But without currency_id = 1, it takes like 4 sec.

UPDATE 1

Okey, I updated key from currency_id, asset_id to currency_id, asset_id, created_at and it takes

Showing rows 0 - 24 (32784 total, Query took 6.4831 seconds.)

Its much faster, any proposal to do it more faster? GROUP BY here to take only first row for every hour. For example:

19:01:10 
19:02:14 
19:23:15

I need only 19:01:10

Answer 1

You can rephrase the filtering predicate to avoid using expressions on columns. For example:

select max(`price`) as max_price, `asset_id` 
from `history_average_pairs` 
where `currency_id` = 1 
  and created_at >= date_add(curdate(), interval - 7 day)
group by hour(created_at), date(created_at), asset_id 
order by `created_at` asc

Then, this query could be much faster if you added the index:

create index ix1 on `history_average_pairs` (`currency_id`, created_at);