Re^5: DBI performance problem with MySQL

There's using an index and then there's utilizing an index. Your query is going to use the composite PRIMARY key because the first column in that key is "logic_id". However, if you comment out the PK and leave only the IoV index, I think you'll find that it's not using the index.

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

Comment on Re^5: DBI performance problem with MySQL

Replies are listed 'Best First'.
Re^6: DBI performance problem with MySQL by e2ka (Acolyte) on Mar 09, 2005 at 19:20 UTC
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	[reply]

Replies are listed 'Best First'.

Re^6: DBI performance problem with MySQL
by e2ka (Acolyte) on Mar 09, 2005 at 19:20 UTC

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

[reply]