CODEWITHSUNDEEP
mysqlsqlquery-optimization
Bin Chen
Bin Chen

Reputation: 63299

How to accelerate time ranged query?

I have a MySQL data table with around 30 million records, one of the fields is create_time, which is a timestamp.

I want to get the count of records in a specified time range, and use unique keywords to get the unique user count.

The SQL statement is:

select count(distinct owner_id) from code_orange_checkpointrecord 
where create_time between '2023-01-01 00:00:00' and '2023-01-30 23:59:59';

But the query is very slow. I tried to create an index on create_time, but the result is a lot slower.

I think I am doing something wrong?

Upvotes: 1

Views: 41

Answers (1)

O. Jones
O. Jones

Reputation: 108651

The precise query you showed us will be accelerated by this multi-column index.

CREATE INDEX create_owner  
    ON code_orange_checkpointrecord (create_time, owner_id);

And, by the way, it's best to avoid BETWEEN for time ranges. Use this instead to get all the records for January. 23:59:59 might not be the precise last timestamp in the month, so it's better to use < and the first timestamp you don't want.

where create_time >= '2023-01-01 00:00:00' 
  and create_time <  '2023-02-01 00:00:00';

Upvotes: 2

Related Questions