mysql 查询日期为空_mysql – 如何优化搜索可以为空的开始日期或结束日期的SQL查询？...

问题：给定一系列可以为空的开始日期和结束日期,优化以下查询的最佳方法是什么(底部的示例模式)：

-- Query I am trying to optimize

SELECT * FROM dateranges WHERE

('2014-11-10 05:59:59' > `start` AND '2014-11-03 06:00:00' <= `end`)

OR ('2014-11-03 06:00:00' >= `start` AND `end` is null)

OR ('2014-11-10 05:59:59' <= `end` AND `start` is null);

-- Same query but with placeholders for clarification

SELECT * FROM dateranges WHERE

('{endSearch}' > `start` AND '{startSearch}' <= `end`)

OR ('{startSearch}' >= `start` AND `end` is null)

OR ('{endSearch}' <= `end` AND `start` is null);

商业条件有效：

> name.start> = startSearch AND< = endSearch？

> name.start> = startSearch AND name.end是否为空？

> name.end< = endSearch AND name.start是否为空？

下面显示EXPLAIN只搜索开始和结束：

1, SIMPLE, s, range, date_start_idx,date_end_idx, date_end_idx, 6, , 251, Using index condition; Using where; Using temporary; Using filesort

下面显示了添加了空搜索的EXPLAIN：

1, SIMPLE, s, ALL, date_start_idx,date_end_idx, , , , 6340, Using where; Using temporary; Using filesort

示例MySQL架构：

CREATE TABLE `dateranges` (

`name` VARCHAR(45) NULL,

`start` DATETIME NULL,

`end` DATETIME NULL);

INSERT INTO `dateranges` (`name`,`start`,`end`) VALUES

('God',null,null),

('Dog',null,'2014-10-06'),

('Cat','2014-10-01',null),

('People','2014-10-02','2014-10-04');

ALTER TABLE `dateranges`

ADD INDEX `index1` (`start` ASC),

ADD INDEX `index2` (`end` ASC);