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]
