You need to run [analyze table] to create the index stats...do this reasonable often (once a day for a well-used table).
Running [optimize table] on the table recreates index stats AND reorganizes/defrags/coalesces data pages...do this once a week or so. Look up the mysqlcheck utility--it lets you do this from scrips pretty easy. Peter <^_^> > -----Original Message----- > From: John Hinton [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, January 29, 2003 7:36 AM > To: [EMAIL PROTECTED] > Subject: Cardinality doesn't auto start > > > mysql Ver 11.18 Distrib 3.23.54, for pc-linux (i686) > > ----INDEXING PROBLEM---- > > I'm building an application which will have a self install script, > intended for use by many people. One particular area of the program > requires the need for an index. If I create this index when I > build the > table or apparently even after the table is built but before > any data is > written to the table, cardinality is shown as 'None' (using > phpMyAdmin) > and futhermore it does not start counting until after data is entered > and the index is recreated. > > Seems like when the index is created, if no data is in the table, it > should be given a default cardinality of 0 and the count continue as > data is entered. > > This need to create/recreate the index after data is written, is tough > to deal with as a programmer having to ask the end user to create this > index after data is gathered. > > Any ideas besides creating an index creater script for the user to run > later? > > Would this be considered a bug? > > ---- SQL to create table with index ---- > > CREATE TABLE somename ( > ndx int(10) NOT NULL auto_increment, > d_now int(7) default NULL, > adate timestamp(14) NOT NULL, > PRIMARY KEY (ndx), > UNIQUE KEY id (ndx), > KEY d_now (d_now) > ) TYPE=MyISAM; > > ---- end SQL ------- > > I have also tried it with d_now set to NOT NULL with the same results. > The index just won't start counting with no data entered. > > -- > John Hinton - Goshen, VA. > http://www.ew3d.com > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
