Index usage on Large tables

Hi all. I am doing some testing to see if MySQL behaves good in certain
situations. I have earlier used MyISAM for some tables we have had
problems with, since MyISAM doesn't support transactions. These tables
are now converted to InnoDB and our earlier problems has gone away, but
during testing something weird happened, or maybe its not weird...I'm
hoping that someone here can explain this.

2 tables, they look exactly the same.

CREATE TABLE `testtbl1_1` (
`m_id` int(10) unsigned NOT NULL auto_increment,
`s` varchar(16) NOT NULL default '',
`id` int(10) unsigned NOT NULL default '0',
`anotherid` int(10) unsigned NOT NULL default '0',
`status` int(10) unsigned NOT NULL default '0',
`h` varchar(16) NOT NULL default '',
`date` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`d1` float(5,4) unsigned NOT NULL default '0.0000',
`d2` float(5,4) unsigned NOT NULL default '0.0000',
`d3` float(5,4) unsigned NOT NULL default '0.0000',
`s` int(10) unsigned NOT NULL default '0',
`bla` varchar(8) NOT NULL default '',
`tjoho` varchar(255) NOT NULL default '',
`number` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`m_id`),
KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=4000000000 AVG_ROW_LENGTH=64

testtbl_2 has the exact same definition.

testtbl_1 contains around 9 million rows and testtbl_2 around 450
million rows.
The select looks like this :
select id,tjoho,number from testtbl_1 where (date >= '2006-03-02
23:00:00') AND (date <= '2006-03-01 23:59:59')

This will get me 1 hour of entries, and it will use the index on date,
doing an EXPLAIN on this query. However, if I get myself 2 hours
instead, date >= '2006-03-02 22:00:00', EXPLAIN tells me that a full
table scan will be made. Why ?
If I use force index(date) in the query, it will use the index.

If I do this queries on the large table, it will stop using the index
when selecting around 30 hours of data.

Does anyone has any idea why MySQL behaves like this ?

Thanx

/zw

ZoombyWoof wrote:
> Hi all. I am doing some testing to see if MySQL behaves good in certain
> situations. I have earlier used MyISAM for some tables we have had
> problems with, since MyISAM doesn't support transactions. These tables
> are now converted to InnoDB and our earlier problems has gone away, but
> during testing something weird happened, or maybe its not weird...I'm
> hoping that someone here can explain this.
>
> 2 tables, they look exactly the same.
>
> CREATE TABLE `testtbl1_1` (
> `m_id` int(10) unsigned NOT NULL auto_increment,
> `s` varchar(16) NOT NULL default '',
> `id` int(10) unsigned NOT NULL default '0',
> `anotherid` int(10) unsigned NOT NULL default '0',
> `status` int(10) unsigned NOT NULL default '0',
> `h` varchar(16) NOT NULL default '',
> `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP,
> `d1` float(5,4) unsigned NOT NULL default '0.0000',
> `d2` float(5,4) unsigned NOT NULL default '0.0000',
> `d3` float(5,4) unsigned NOT NULL default '0.0000',
> `s` int(10) unsigned NOT NULL default '0',
> `bla` varchar(8) NOT NULL default '',
> `tjoho` varchar(255) NOT NULL default '',
> `number` int(10) unsigned NOT NULL default '0',
> PRIMARY KEY (`m_id`),
> KEY `date` (`date`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=4000000000
> AVG_ROW_LENGTH=64
>
> testtbl_2 has the exact same definition.
>
> testtbl_1 contains around 9 million rows and testtbl_2 around 450
> million rows.
> The select looks like this :
> select id,tjoho,number from testtbl_1 where (date >= '2006-03-02
> 23:00:00') AND (date <= '2006-03-01 23:59:59')
>
> This will get me 1 hour of entries, and it will use the index on date,
> doing an EXPLAIN on this query. However, if I get myself 2 hours
> instead, date >= '2006-03-02 22:00:00', EXPLAIN tells me that a full
> table scan will be made. Why ?
> If I use force index(date) in the query, it will use the index.
>
> If I do this queries on the large table, it will stop using the index
> when selecting around 30 hours of data.
>
> Does anyone has any idea why MySQL behaves like this ?
>
> Thanx
>
> /zw
A small correction, the select statement shall of course be :
select id,tjoho,number from testtbl_1 where (date >= '2006-03-01
> 23:00:00') AND (date <= '2006-03-01 23:59:59')

:-)