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 Thanks, Eric "Chris Nolan" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Can you send us the CREATE TABLE statement for this troublesome table? > > Regards, > > Chris > > Eric B. wrote: > > >Sorry - forgot to mention it. I've already tried both an OPTIMIZE TABLE and > >ANALYZE TABLE to try to improve performance, but with no result. > > > >Any other ideas? > > > >Thanks, > > > >Eric > > > > > >"Chris Nolan" <[EMAIL PROTECTED]> wrote in message > >news:[EMAIL PROTECTED] > > > > > >>Hmm...if there's lots of thrashing, it might be to do with > >>fragmentation. Have you tried running OPTIMIZE TABLE on the table in > >>question? > >> > >>Does anyone on the list have anything to say about putting the MYD and > >>MYI files on seperate disks or using RAID MyISAM tables?? > >> > >>Regards, > >> > >>Chris > >> > >>Eric B. wrote: > >> > >> > >> > >>>Help! > >>> > >>>Okay - so I've been working around on my indexing of a table. My table > >>> > >>> > >is > > > > > >>>currently over 5M rows (close to 500Mb) and growing at a quick rate. In > >>>order to handle different types of queries, I am forced to create > >>> > >>> > >multiple > > > > > >>>indexes for the table. But by doing so, I end up with an MYI index file > >>> > >>> > >of > > > > > >>>over 2Gig!! > >>> > >>>Now the problem is that my query is still taking way to long to execute > >>> > >>> > >(ie: > > > > > >>>30 secs). If I try an "Explain" on the query, it tells me that it only > >>>needs to examine 30 000 rows (which is not bad considering there are over > >>> > >>> > >5 > > > > > >>>million in the table), however, when I actually execute it, I can see the > >>>disk thrashing an enormous amount. Is there any way to know if the > >>> > >>> > >thrasing > > > > > >>>because it is actually reading through the DB and retrieving the rows or > >>>trying to read through this gigantic index file? > >>> > >>>Is there anything I can do to help optimize this? Loading a 2G index > >>> > >>> > >file > > > > > >>>into RAM doesn't seem realistic since this index file will grow with > >>> > >>> > >time, > > > > > >>>and I can't imagine needing to constantly add more RAM to handle a bigger > >>>and bigger index file. > >>> > >>>Is there any way to determine what the I/O is due to? Is the thrashing > >>>MySQL reading the DB or reading the index file? Or is there anything > >>> > >>> > >else I > > > > > >>>can do to help optimize my queries further? I'm using MyISAM tables, if > >>> > >>> > >it > > > > > >>>makes any difference.... > >>> > >>>Thanks for any insight! > >>> > >>>Eric > >>> > >>> > >>> > >>> > >>> > >>> > >>> > >>> > >>> > >>> > >> > >>-- > >>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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
