On Thu, Nov 06, 2003 at 11:08:03AM -0800, Eric Anderson wrote: > > Given the following table: > > CREATE TABLE campaign_t ( > acct_id int(11) unsigned NOT NULL default '0', > site_id tinyint(3) unsigned NOT NULL default '0', > ref_id int(11) unsigned NOT NULL default '0', > datestamp char(10) NOT NULL default '', > raws int(11) unsigned NOT NULL default '0', > uniques int(11) unsigned NOT NULL default '0', > trial_signups int(11) NOT NULL default '0', > full_signups int(11) NOT NULL default '0', > annual_signups int(11) unsigned NOT NULL default '0', > PRIMARY KEY (acct_id,site_id,ref_id,datestamp), > KEY acct_id (acct_id), > KEY site_id (site_id), > KEY ref_id (ref_id), > KEY datestamp (datestamp) > ) TYPE=MyISAM; > > How come it doesn't use the 'datestamp' index on this query: > > mysql> explain SELECT * FROM campaign_t WHERE datestamp < 20041105\g > +------------+------+---------------+------+---------+------+--------+------------+ > | table | type | possible_keys | key | key_len | ref | rows | Extra | > +------------+------+---------------+------+---------+------+--------+------------+ > | campaign_t | ALL | datestamp | NULL | NULL | NULL | 438166 | where used | > +------------+------+---------------+------+---------+------+--------+------------+ > 1 row in set (0.00 sec)
If most of the rows match that WHERE clause, it'll just scan the table rather than using the index. It's faster to do so. This is documented in the manual. http://www.mysql.com/doc/en/How_to_avoid_table_scan.html Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 2,006,826,779 queries (431/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
