Re: Index with two date fields using comparison operators

[Thomas Wiedmann]

my second test work with same result...

But what about this...is this faster? Can anybody check this sample?

CREATE TABLE subreg (
 id INT NOT NULL,
 valid_from DATE NOT NULL,
 valid_till DATE NOT NULL,
 doc VARCHAR(100) NOT NULL,
 PRIMARY KEY(id),
 KEY idx_from_till_id (valid_from, valid_till, id),
 KEY idx_till_from_id (valid_till, valid_from, id)
);

INSERT INTO subreg VALUES
( 1, '2010-01-01', '2010-01-02', 'one'),
( 2, '2010-01-02', '2010-01-03', 'two'),
( 3, '2010-01-03', '2010-01-04', 'three'),
( 4, '2010-01-04', '2010-01-05', 'four'),
( 5, '2010-01-05', '2010-01-06', 'five');

ANALYZE TABLE subreg;

EXPLAIN
SELECT * FROM ( SELECT id, valid_from FROM subreg
                 WHERE valid_from > '2010-01-03'
               ) m_from 
         JOIN ( SELECT id, valid_till FROM subreg
                 WHERE valid_till < '2010-01-03'
              ) m_till
           ON m_from.id = m_till.id;
+----+-------------+------------+--------+------------------+------------------+---------+------+------+--------------------------+
| id | select_type | table      | type   | possible_keys    | key              | key_len | ref  | rows | Extra                    |
+----+-------------+------------+--------+------------------+------------------+---------+------+------+--------------------------+
|  1 | PRIMARY     | <derived3> | system | NULL             | NULL             | NULL    | NULL |    1 |                          |
|  1 | PRIMARY     | <derived2> | ALL    | NULL             | NULL             | NULL    | NULL |    2 | Using where              |
|  3 | DERIVED     | subreg     | index  | idx_till_from_id | idx_from_till_id | 10      | NULL |    5 | Using where; Using index |
|  2 | DERIVED     | subreg     | range  | idx_from_till_id | idx_from_till_id | 3       | NULL |    3 | Using where; Using index |
+----+-------------+------------+--------+------------------+------------------+---------+------+------+--------------------------+
4 rows in set (0.00 sec)

mysql>

with kind regards,
Thomas