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
Possibly because datestamp and 20041105 have different datatypes. There's an implicit data conversion required for the comparison, which in some cases prevents use of an index. Try writing '20041105' rather than 20041105 and see if that makes a difference.
+------------+------+---------------+------+---------+------+--------+------------+
| 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)
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
