How can I optimize this SQL query that searches for zero start or end dates?

Question

Question . Given the range of zero start and end dates, what would be the best way to optimize the next query (a diagram with a sample below):

-- 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);

Business conditions :

• Is name.start> = startSearch AND <= endSearch?
• Is name.start> = startSearch AND name.end null?
• Is name.end <= endSearch AND name.start null?

---

The following shows how EXPLAIN searches only for the beginning and end:

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

Below is an EXPLAIN with added null search:

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

---

MySQL schema example :

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);

Answer 1

, . start end:

create index idx_dateranges_start_end on dateranges(start, end)

, union all:

SELECT * FROM dateranges WHERE ('2014-11-10 05:59:59' > `start` AND
                                '2014-11-03 06:00:00' <= `end` AND
                                '2014-11-03 06:00:00' < `start`
                               )
UNION ALL
SELECT * FROM dateranges WHERE ('2014-11-03 06:00:00' >= `start` AND `end` is null)
UNION ALL
SELECT * FROM dateranges WHERE ('2014-11-10 05:59:59' <= `end` AND `start` is null);

MySQL where. , union all . MySQL ( SQL) or.

EDIT:

, NULL, - "2000-01-01" "2100-12-31" . :

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

Answer 2

, , [2014-11-03 06:00:00, 2014-11-10 06:00:00), . , :

SELECT * FROM dateranges
WHERE '2014-11-10 06:00:00' > `start`
AND `end` > '2014-11-03 06:00:00'

NULL, NOT NULL (, 1000-01-01 9999-12-31 ) NULL.

---

, NULL OR - , :

SELECT a.*
FROM dateranges AS a
INNER JOIN dateranges AS b ON a.id = b.id
WHERE ('2014-11-10 06:00:00' > a.`start` OR a.`start` IS NULL)
AND (b.`end` > '2014-11-03 06:00:00' OR b.`end` IS NULL)

, AND-OR . HOWEVER, MySQL - (, - ), , .

---

, . (, ), (, ). MySQL "" .