How to query rows between specific date range in MySQL efficiently?

Question

I have a table with a certain column say created_time with datatype as datetime. It is also one of the primary key on that table.

Now when I try to query a date range created_time BETWEEN :startTime AND :endTime , it ends up looking through the entire table as I can see from the execution plan.

Now, there is another primary key column sensor_time with data type as INT which stores the time in unix format. When I use the range in this, it only looks through the database for a small subsection.

Please someone explain me why the range does not work efficiently with the datetime type even though it is a primary key.

date_time is not a data type

Strawberry
– Strawberry

2020-06-10 12:06:28 +00:00
Commented Jun 10, 2020 at 12:06 — Strawberry
– Strawberry, Commented Jun 10, 2020 at 12:06
Sorry, I meant datetime. I have corrected it.

Vikrant Pradhan
– Vikrant Pradhan

2020-06-10 14:05:43 +00:00
Commented Jun 10, 2020 at 14:05 — Vikrant Pradhan
– Vikrant Pradhan, Commented Jun 10, 2020 at 14:05

Gordon Linoff · Accepted Answer · 2020-06-10 11:31:19Z

1

It is also one of the primary key on that table.

Uh. This is not possible. A primary key is a set of columns with the following characteristics:

The values are never NULL.
The combination of the column(s) in a given row are always unique.
There is only one primary key.

You probably have a composite primary key, composed of multiple columns. In that case, datetime filters would only be used for a query with this restriction:

where created_time between ? and ?

when created_time is the FIRST column in the primary key. Otherwise, the preceding columns need equality comparisons.

It sounds like you want a second column on datetime with that as the first column in the index (if there are multiple columns).

answered Jun 10, 2020 at 11:31

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vikrant Pradhan Over a year ago

My primary goal is to query between a specified range of dates without having to go for a full table lookup. Is it achievable in my case ?? Sorry, I didn't get the last paragraph.

Gordon Linoff Over a year ago

@VikrantPradhan . . . Create a secondary index on created_time.

Collectives™ on Stack Overflow

How to query rows between specific date range in MySQL efficiently?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related