Hi Eric, The Cardinality of the datestamp column is only 76. That means it thinks there's only 76 unique values in all of your rows. In other words, there's a good chance that your WHERE matches more than ~30% of the rows, in which case the index won't be used.
If you haven't run OPTIMIZE in years, it'd probably be a good idea to do that anyway... see if it changes anything. OPTIMIZE includes ANALYZE by the way. On a side note, you might as well remove the separate acct_id index since it's the first column of the PRIMARY KEY anyway where it can be used as if it was a separate index. The additional index is just wasting space. Drop this index before you run the OPTIMIZE. :-) ALTER TABLE campaign_t DROP INDEX acct_id; OPTIMIZE TABLE campaign_t; Matt ----- Original Message ----- From: "Eric Anderson" Cc: <[EMAIL PROTECTED]> Sent: Thursday, November 06, 2003 1:45 PM Subject: RE: This is confusing..? > > Can you send show keys from campaign_t. > > I bet you can solve your problem by running analyze table. > > mysql> show index from campaign_t\g > +------------+------------+-----------+--------------+-------------+---- -------+-------------+----------+--------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | > +------------+------------+-----------+--------------+-------------+---- -------+-------------+----------+--------+---------+ > | campaign_t | 0 | PRIMARY | 1 | acct_id | A | 1898 | NULL | NULL | | > | campaign_t | 0 | PRIMARY | 2 | site_id | A | 5621 | NULL | NULL | | > | campaign_t | 0 | PRIMARY | 3 | ref_id | A | 109627 | NULL | NULL | | > | campaign_t | 0 | PRIMARY | 4 | datestamp | A | 438511 | NULL | NULL | | > | campaign_t | 1 | acct_id | 1 | acct_id | A | 1898 | NULL | NULL | | > | campaign_t | 1 | site_id | 1 | site_id | A | 36 | NULL | NULL | | > | campaign_t | 1 | ref_id | 1 | ref_id | A | 54813 | NULL | NULL | | > | campaign_t | 1 | datestamp | 1 | datestamp | A | 76 | NULL | NULL | | > +------------+------------+-----------+--------------+-------------+---- -------+-------------+----------+--------+---------+ > > I have no idea how to interpret this.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
