Thanks Sasha. It helped trim down my table a bit.... Eric
"Sasha Pachev" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Eric B. wrote: > > Sure! Here it is: > > > > CREATE TABLE `index2` ( > > `id` varchar(50) NOT NULL default '', > > `recordid` varchar(20) NOT NULL default '', > > `transid` varchar(20) NOT NULL default '', > > `formid` varchar(20) NOT NULL default '', > > `groupid` varchar(20) NOT NULL default '', > > `clientid` varchar(20) NOT NULL default '', > > `userid` varchar(20) NOT NULL default '', > > `keyid` varchar(50) NOT NULL default '', > > `active_recordid` varchar(20) default NULL, > > `replacing` varchar(20) default NULL, > > `created` datetime default NULL, > > `lastmodified` datetime default NULL, > > `issaved` decimal(18,0) NOT NULL default '0', > > `isclosed` decimal(18,0) NOT NULL default '0', > > `isdeleted` decimal(18,0) NOT NULL default '0', > > `indexfield` varchar(50) default NULL, > > `indexvalue` varchar(200) default NULL, > > PRIMARY KEY (`id`), > > > > KEY `recordid_idxfield_idxvalue` (`recordid`,`indexfield`,`indexvalue`), > > KEY `indexfield_idxvalue` (`indexfield`,`indexvalue`), > > KEY `Fixed_fields_and_generic_fields` > > (`recordid`,`transid`,`formid`,`clientid`,`active_recordid`,`issaved`,`isclo > > sed`,`isdeleted`,`indexfield`,`indexvalue`), > > KEY `recordid_idxfield_formid_activeRid_isvars_idxval` > > (`recordid`,`indexfield`,`formid`,`active_recordid`,`issaved`,`isclosed`,`is > > deleted`,`indexvalue`), > > KEY `formid_idxfield_idxvalue` (`formid`,`indexfield`,`indexvalue`) > > > > ) TYPE=MyISAM > > Drop Fixed_fields_and_generic_fields and > recordid_idxfield_formid_activeRid_isvars_idxval > > Most queries would probably not benefit from those in the presence of other keys. > > Depending on the kind of queries you are running, you may want to reorganize > some of your keys. Good rule of thumb is if you have a key on (A,B,C) you > usually do not need a key on (A,B), and if for each unique value of (A,B) there > are no more than 10 matches, you do not usually need a key on (A,B,C). > > Disk trashing can happen on a keyed query if the records in the data file are > not in the same order as the ones in the key file. A certain order in the data > file can be forced with periodic runs of ALTER TABLE ORDER BY. > > -- > Sasha Pachev > Create online surveys at http://www.surveyz.com/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
