Geek Answers Handbook

How to index two date columns for this type of query

I have a MySQL table like this:

CREATE TABLE `dates` (
`id`  int UNSIGNED NULL AUTO_INCREMENT ,
`object_id`  int UNSIGNED NOT NULL ,
`date_from`  date NOT NULL ,
`date_to`  date NULL ,
`time_from`  time NULL ,
`time_to`  time NULL ,
PRIMARY KEY (`id`)
);

which is requested mainly as follows:

SELECT object_id FROM `dates`
WHERE NOW() BETWEEN date_from AND date_to

What is the best way to index a table? Should I create two indexes: one for date_fromand one for date_toor better is a combined index for both columns?

+5
source share
4 answers

For request:

WHERE NOW() >= date_from 
  AND NOW() <= date_to

A component index is (date_from, date_to)useless.

: (date_from) (date_to), SQL , . . . , .

( , ).

. (date_from, date_to, object_id) . NOW() <= date_from , , . , , , .

( , ). :

WHERE CURRENT_DATE() >= date_from 
  AND ( CURRENT_DATE() + INTERVAL 1 DAY <= date_to
       OR  ( CURRENT_DATE() = NOW() 
         AND CURRENT_DATE() = date_to
           )
      )

, :

WHERE CURRENT_DATE() >= date_from 
  AND CURRENT_DATE() <= date_to

NOW() a DATETIME, CURRENT_DATE() DATE .

+4

, date_from, date_to object_id, ypercube. , . , date_to , .

CREATE INDEX ON (date_to, date_from, object_id);
+2

? 10 , , . 10 , (date_from, date_to, object_id), , havind, object_id.

. , .

+1

Create an index with (date_from, date_to) as this single index will be used for WHERE criteria

If you create separate indexes, MySQL will have to use one or the other instead of two

0
source

More articles:


All Articles