CODEWITHSUNDEEP
mysqlsql
Ido Barash
Ido Barash

Reputation: 5122

MySQL - is query with where by timestamp more efficient?

I have a table with primary key, indexed field and an unindexed timestamp field.

example:

p_key  |   project   | name  | timestamp
-----------------------------------------
1      |    1        |  a    |    18:00
2      |    1        |  b    |     19:00

I want to get record 1.

should I ask:

SELECT *
FROM tbl
WHERE p_key = 1 AND project = 1 AND timestamp BETWEEN 16:30 AND 18:30)

OR

SELECT *
FROM tbl
WHERE p_key = 1

Lets say that I have many records.

Upvotes: 0

Views: 382

Answers (3)

C.Champagne
C.Champagne

Reputation: 5489

The second option obviously!!!

SELECT * FROM tbl WHERE p_key = 1

Filtering by primary key is clearly more efficient than by any other field (in your example) since it is the only one to be indexed.

Furthermore the primary key is enough to get the record you expect. No need to add complexity, bug risk...and computing time (yes, the conditions in the where clause need to be processed. The more you add, the longer it can take)

Upvotes: 0

user7396598
user7396598

Reputation: 1289

In your example it doesn't matter which query is more efficient in terms of execution time. The important piece to note is that a primary key is unique.

Your query:

SELECT * FROM tbl WHERE p_key = 1

Will return the same row as your other query:

SELECT * FROM tbl WHERE p_key = 1 AND project = 1 AND timestamp BETWEEN 16:30 AND 18:30)

Because both filter on the p_key = 1. The worst case scenario here is that the entry does not actually fall within your time span in the second query and you get no results at all.

I am assuming you have an index on the primary key here. This means there is absolutely no need to run the second query vs the first query, unless it is possible that it does not fall within the timespan requested.

So your efficiency in your database will be in that you do not need to create and maintain a new index for the second query. If you have "many" rows as you stated, this efficiency can become quite important.

Upvotes: 2

James
James

Reputation: 3015

A filter by an integer indexed field will be the fastest way to get your data under normal circunstances. Understanding that your data looks like your example (I mean, the Timestamp is not significant in your query and filtering by the primary key you get a single record...) In addition, by default a primary key generates an index, so you don't need to create it by yourself on this field.

Upvotes: 0

Related Questions