in reply to Re^5: DBI performance problem with MySQL
in thread DBI performance problem with MySQL

Like this?

mysql> EXPLAIN SELECT since, till FROM COND_dummy                                            
               WHERE ((since >= '2005-01-01 00:00:00'
                       AND since < '2005-01-01 00:00:01')                 
                   OR (till  >  '2005-01-01 00:00:00'
                       AND till  < '2005-01-01 00:00:01')               
                   OR ('2005-01-01 00:00:00' >= since
                       AND '2005-01-01 00:00:01' < till))                           
               LIMIT 1;
+----+-------------+------------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+------------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | COND_dummy | index | IoV           | IoV  |      16 | NULL | 9499 | Using where; Using index |
+----+-------------+------------+-------+---------------+------+---------+------+------+--------------------------+

Then it will use the IoV index. I created the two indexes because sometimes the table will be searched specifiying logic_id, sometimes not

  • Comment on Re^6: DBI performance problem with MySQL