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]
