MySQL – Indexing for Date Part of Field

indexMySQLmysql-5.6

I have a datetime field, but I often query over the date part, like this

select * from table where date(datetime_field)=curdate()

This query can't use index on datetime_field!
What would be the best solution with the minimum amount of changes in code or db?

PS. upgrading mysql could be an option if there is a solution I'm not aware of yet, but a more short term solution for 5.6 would be appreciated

Best Answer

The problem isn't the index. The problem is that your query as written does not allow an index to be used.

Modify your query so that the date range is resolved to a pair of constants and the problem is solved:

WHERE datetime_field >= CURDATE()
  AND datetime_field < DATE_ADD(CURDATE(), INTERVAL 1 DAY)

This will use the index on the column.

As a rule, never use a column as an argument to a function in WHERE because this forces a table scan.

Related Solutions

Thesql trigger to insert/update date column from datetime column

It's unclear why you need this but assuming that the table looks something like

CREATE TABLE Table1
    (`id` int not null auto_increment primary key, 
     `dt1` datetime, 
     `dt2` datetime, 
     `d1`  date, 
     `d2`  date);

your triggers might look the following way

CREATE TRIGGER tg_bi_table1
BEFORE INSERT ON table1
FOR EACH ROW
  SET NEW.d1 = DATE(NEW.dt1), 
      NEW.d2 = DATE(NEW.dt2);

CREATE TRIGGER tg_bu_table1
BEFORE UPDATE ON table1
FOR EACH ROW
  SET NEW.d1 = DATE(NEW.dt1), 
      NEW.d2 = DATE(NEW.dt2);

Here is SQLFiddle demo

Mysql – theSQL query with field replacemnt

select network_segment_ID, display_name, starting_ip, ending_ip, bld.description, dept.description
from network_segments
join buildings bld on (building = building_ID)
join departments dept on (department = department_ID);

Best Answer

Related Solutions

Thesql trigger to insert/update date column from datetime column

Mysql – theSQL query with field replacemnt

Related Question