dates and index cardinality

[rloef@interfold.com]

I have a 5.0.15 database with a table with a few million rows and 408
different dates in a date field. If I do 'SHOW INDEXES IN TABLENAME'
the output says the cardinality of the DATE index is 568289. I take it
this is right, but other indexes in the table have cardinalities that
sort of look more in line with what I expect; DATE just appears to be
wrong. Is it? I'm trying to re-write some queries to narrow searches
quicker by using 'smaller' indexes first.


thanks,

r

[Markus Popp]

Re: dates and index cardinality

Is this a MyISAM table? Then do a

ANALYZE TABLE [table name]

.... this will update the index statistics (which could possibly be out of
date).

Markus

[rloef@interfold.com]

Re: dates and index cardinality

ANALYZE came out clean, as did OPTIMIZE. I even dumped the table to a
text file and poured it back into an empty form. Also dropped the index
and re-created it on the new table. Nothing changed. So I'm re-ordering
the queries anyway and chalking it up to some new internal thing in the
5.n releases (like my earlier post about mysqlcc.) Time and times will
tell.

Thanks for the reply.

r