How to optimize MySQL query ‘SELECT * from table WHERE Date=CURDATE() and ID=1;’

I have the following data in a MySQL t开发者_运维百科able table:

ID: int(11) [this is the primary key]

Date: date

and I run the MySQL query:

SELECT * from table WHERE Date=CURDATE() and ID=1;

This takes between 0.6 and 1.2 seconds.

Is there any way to optimize this query to get results quicker?

My objective is to find out if I already have a record for today for this ID.

---

Add indexes on ID and Date.

See CREATE INDEX manual.

---

You could add a limit 1 at the end, since you are searching for a primary key the max results is 1.

And if you only want to know wether it exists or not you could replace * with ID to select only the ID.

Furthermore, if you haven't already, you really need to add indexes.

---

SET @cur_date = CURDATE()

...WHERE Date = @cur_date ...

and then create an index of Date, ID (order is important, it should match the order you query on).

In general, calling functions before you do the query and storing them to variables lets SQL treat them like numbers instead of functions, which tends to allow it to use a faster query algorithm.