Indexes with < or > comparisons

Hi,
I'm trying to figure out why MySQL won't use my index when I do a <
comparison, but will use it when I do an = comparison.
I thought a BTREE index should work for < comparisons?

mysql> create index start_date_index using BTREE on
case_sessions(start_date);
Query OK, 423608 rows affected (12.99 sec)
Records: 423608 Duplicates: 0 Warnings: 0

mysql> explain SELECT * FROM case_sessions where start_date <
'2007-06-02 08:00';
+----+-------------+---------------+------+------------------+------
+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+---------------+------+------------------+------
+---------+------+--------+-------------+
| 1 | SIMPLE | case_sessions | ALL | start_date_index | NULL |
NULL | NULL | 423608 | Using where |
+----+-------------+---------------+------+------------------+------
+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM case_sessions where start_date =
'2007-06-02 08:00';
+----+-------------+---------------+------+------------------
+------------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+------------------
+------------------+---------+-------+------+-------+
| 1 | SIMPLE | case_sessions | ref | start_date_index |
start_date_index | 8 | const | 1 | |
+----+-------------+---------------+------+------------------
+------------------+---------+-------+------+-------+
1 row in set (0.00 sec)

Can anyone give me a clue?
Thanks,
Rob


CREATE TABLE `case_sessions` (
`case_id` bigint(20) NOT NULL default '0',
`start_date` datetime NOT NULL default '0000-00-00 00:00:00',
`end_date` datetime default NULL,
`beginning_status_code` tinytext NOT NULL,
`working_agent_login_id` tinytext,
`updating_system` tinytext,
`updating_agent_login_id` tinytext,
`updating_external_party_id` tinytext,
`creation_date` datetime NOT NULL default '0000-00-00 00:00:00',
`created_by` varchar(8) NOT NULL default '',
`last_updated_date` datetime NOT NULL default '0000-00-00 00:00:00',
`last_updated_by` varchar(8) NOT NULL default '',
`source` tinytext NOT NULL,
PRIMARY KEY
(`case_id`,`start_date`,`beginning_status_code`(2) ,`source`(2)),
KEY `start_date_index` USING BTREE (`start_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Hopefully this is slightly easier to read:

mysql> explain SELECT * FROM case_sessions where start_date <
'2007-06-02 08:00'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: case_sessions
type: ALL
possible_keys: start_date_index
key: NULL
key_len: NULL
ref: NULL
rows: 412863
Extra: Using where

mysql> explain SELECT * FROM case_sessions where start_date =
'2007-06-02 08:00'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: case_sessions
type: ref
possible_keys: start_date_index
key: start_date_index
key_len: 8
ref: const
rows: 1
Extra:

<spamtheaussie@gmail.com> schreef in bericht
news:1192818974.440076.195540@i13g2000prf.googlegr oups.com...
> Hopefully this is slightly easier to read:
>
> mysql> explain SELECT * FROM case_sessions where start_date <
> '2007-06-02 08:00'\G;
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: case_sessions
> type: ALL
> possible_keys: start_date_index
> key: NULL
> key_len: NULL
> ref: NULL
> rows: 412863
> Extra: Using where
>
> mysql> explain SELECT * FROM case_sessions where start_date =
> '2007-06-02 08:00'\G;
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: case_sessions
> type: ref
> possible_keys: start_date_index
> key: start_date_index
> key_len: 8
> ref: const
> rows: 1
> Extra:
>
>

using an index, or not, has somethings to do with optimizations...

i have a table wit a datetime field in it. the table contains data for every
day since july 2007;

when i do "select * from table where dateTime < '2007-10-19';" the index
with this field is used.

and when i do "select * from table where dateTime >'2007-10-19'; the index
is not used.

but, last but not least, there's a chapter on this subject in the manual
(http://dev.mysql.com/doc/refman/5.0/...imization.html) which i have not
read......

On Oct 19, 2:36 pm, spamtheaus...@gmail.com wrote:
> Hopefully this is slightly easier to read:
>
> mysql> explain SELECT * FROM case_sessions where start_date <
> '2007-06-02 08:00'\G;
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: case_sessions
> type: ALL
> possible_keys: start_date_index
> key: NULL
> key_len: NULL
> ref: NULL
> rows: 412863
> Extra: Using where
>
> mysql> explain SELECT * FROM case_sessions where start_date =
> '2007-06-02 08:00'\G;
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: case_sessions
> type: ref
> possible_keys: start_date_index
> key: start_date_index
> key_len: 8
> ref: const
> rows: 1
> Extra:

What does your data look like? If most of the rows are less than
2007-06-02 08:00 (which appears to be the case) it's probably faster
to scan the table than use an index (fewer disk seeks). The optimizer
is smarter than you think...

"Luuk" <luuk@invalid.lan> schreef in bericht
news:4719080c$0$234$e4fe514c@news.xs4all.nl...
>
> <spamtheaussie@gmail.com> schreef in bericht
> news:1192818974.440076.195540@i13g2000prf.googlegr oups.com...
>> Hopefully this is slightly easier to read:
>>
>> mysql> explain SELECT * FROM case_sessions where start_date <
>> '2007-06-02 08:00'\G;
>> *************************** 1. row ***************************
>> id: 1
>> select_type: SIMPLE
>> table: case_sessions
>> type: ALL
>> possible_keys: start_date_index
>> key: NULL
>> key_len: NULL
>> ref: NULL
>> rows: 412863
>> Extra: Using where
>>
>> mysql> explain SELECT * FROM case_sessions where start_date =
>> '2007-06-02 08:00'\G;
>> *************************** 1. row ***************************
>> id: 1
>> select_type: SIMPLE
>> table: case_sessions
>> type: ref
>> possible_keys: start_date_index
>> key: start_date_index
>> key_len: 8
>> ref: const
>> rows: 1
>> Extra:
>>
>>
>
> using an index, or not, has somethings to do with optimizations...
>
> i have a table wit a datetime field in it. the table contains data for
> every day since july 2007;
>
> when i do "select * from table where dateTime < '2007-10-19';" the index
> with this field is used.
>
> and when i do "select * from table where dateTime >'2007-10-19'; the index
> is not used.
>
> but, last but not least, there's a chapter on this subject in the manual
> (http://dev.mysql.com/doc/refman/5.0/...imization.html) which i have
> not read......
>
>
>

hmmmm, is was missing something, so the above is NOT true,
damn, i should have read the pages the link refers too...

the ebove should be:
- when i do "select * from table where dateTime < '2007-10-19';" the index
with this field is NOT used. (but the PRIMARY key is used)
- and when i do "select * from table where dateTime >'2007-10-19'; the index
IS used.

On Oct 19, 12:46 pm, ZeldorBlat <zeldorb...@gmail.com> wrote:
> On Oct 19, 2:36 pm, spamtheaus...@gmail.com wrote:
>
>
>
> > Hopefully this is slightly easier to read:
>
> > mysql> explain SELECT * FROM case_sessions where start_date <
> > '2007-06-02 08:00'\G;
> > *************************** 1. row ***************************
> > id: 1
> > select_type: SIMPLE
> > table: case_sessions
> > type: ALL
> > possible_keys: start_date_index
> > key: NULL
> > key_len: NULL
> > ref: NULL
> > rows: 412863
> > Extra: Using where
>
> > mysql> explain SELECT * FROM case_sessions where start_date =
> > '2007-06-02 08:00'\G;
> > *************************** 1. row ***************************
> > id: 1
> > select_type: SIMPLE
> > table: case_sessions
> > type: ref
> > possible_keys: start_date_index
> > key: start_date_index
> > key_len: 8
> > ref: const
> > rows: 1
> > Extra:
>
> What does your data look like? If most of the rows are less than
> 2007-06-02 08:00 (which appears to be the case) it's probably faster
> to scan the table than use an index (fewer disk seeks). The optimizer
> is smarter than you think...

You're right it depends on the date. Interesting.
The range of start_date is from 2002-07-02 to the present, with
gradually increasing density from then until the present.
It stops using the index when the date is greater than 2006-04-01.
So it makes sense that if it's retrieving all the row data anyway it
may as well just scan the table.

Unfortunately my actual use case is this:
SELECT * FROM case_sessions where start_date < '2006-04-01' and
end_date > '2006-04-01'
I created an end_date_index and a combined index on start_date and
end_date called date_index, but in this case I guess it doesn't
realise that there are only going to be a handful of rows that will
match the WHERE criteria, so it still tries to
a) use the start_date index instead of using the composite date_index
b) ignore all indexes after '2006-04-01'

I guess I'll just force it to use my composite index.

Thanks a lot for your help!
Rob

On Oct 19, 12:46 pm, ZeldorBlat <zeldorb...@gmail.com> wrote:
> On Oct 19, 2:36 pm, spamtheaus...@gmail.com wrote:
>
>
>
> > Hopefully this is slightly easier to read:
>
> > mysql> explain SELECT * FROM case_sessions where start_date <
> > '2007-06-02 08:00'\G;
> > *************************** 1. row ***************************
> > id: 1
> > select_type: SIMPLE
> > table: case_sessions
> > type: ALL
> > possible_keys: start_date_index
> > key: NULL
> > key_len: NULL
> > ref: NULL
> > rows: 412863
> > Extra: Using where
>
> > mysql> explain SELECT * FROM case_sessions where start_date =
> > '2007-06-02 08:00'\G;
> > *************************** 1. row ***************************
> > id: 1
> > select_type: SIMPLE
> > table: case_sessions
> > type: ref
> > possible_keys: start_date_index
> > key: start_date_index
> > key_len: 8
> > ref: const
> > rows: 1
> > Extra:
>
> What does your data look like? If most of the rows are less than
> 2007-06-02 08:00 (which appears to be the case) it's probably faster
> to scan the table than use an index (fewer disk seeks). The optimizer
> is smarter than you think...

You're right it depends on the date. Interesting.
The range of start_date is from 2002-07-02 to the present, with
gradually increasing density from then until the present.
It stops using the index when the date is greater than 2006-04-01.
So it makes sense that if it's retrieving all the row data anyway it
may as well just scan the table.

Unfortunately my actual use case is this:
SELECT * FROM case_sessions where start_date < '2006-04-01' and
end_date > '2006-04-01'
I created an end_date_index and a combined index on start_date and
end_date called date_index, but in this case I guess it doesn't
realise that there are only going to be a handful of rows that will
match the WHERE criteria, so it still tries to
a) use the start_date index instead of using the composite date_index
b) ignore all indexes after '2006-04-01'

I guess I'll just force it to use my composite index.

Thanks a lot for your help!
Rob

On Oct 19, 12:46 pm, ZeldorBlat <zeldorb...@gmail.com> wrote:
> On Oct 19, 2:36 pm, spamtheaus...@gmail.com wrote:
>
>
>
> > Hopefully this is slightly easier to read:
>
> > mysql> explain SELECT * FROM case_sessions where start_date <
> > '2007-06-02 08:00'\G;
> > *************************** 1. row ***************************
> > id: 1
> > select_type: SIMPLE
> > table: case_sessions
> > type: ALL
> > possible_keys: start_date_index
> > key: NULL
> > key_len: NULL
> > ref: NULL
> > rows: 412863
> > Extra: Using where
>
> > mysql> explain SELECT * FROM case_sessions where start_date =
> > '2007-06-02 08:00'\G;
> > *************************** 1. row ***************************
> > id: 1
> > select_type: SIMPLE
> > table: case_sessions
> > type: ref
> > possible_keys: start_date_index
> > key: start_date_index
> > key_len: 8
> > ref: const
> > rows: 1
> > Extra:
>
> What does your data look like? If most of the rows are less than
> 2007-06-02 08:00 (which appears to be the case) it's probably faster
> to scan the table than use an index (fewer disk seeks). The optimizer
> is smarter than you think...

You're right it depends on the date. Interesting.
The range of start_date is from 2002-07-02 to the present, with
gradually increasing density from then until the present.
It stops using the index when the date is greater than 2006-04-01.
So it makes sense that if it's retrieving all the row data anyway it
may as well just scan the table.

Unfortunately my actual use case is this:
SELECT * FROM case_sessions where start_date < '2006-04-01' and
end_date > '2006-04-01'
I created an end_date_index and a combined index on start_date and
end_date called date_index, but in this case I guess it doesn't
realise that there are only going to be a handful of rows that will
match the WHERE criteria, so it still tries to
a) use the start_date index instead of using the composite date_index
b) ignore all indexes after '2006-04-01'

I guess I'll just force it to use my composite index.

Thanks a lot for your help!
Rob