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?
"Chris Nolan" <chris@stripped> wrote in message
> Hmm...if there's lots of thrashing, it might be to do with
> fragmentation. Have you tried running OPTIMIZE TABLE on the table in
> Does anyone on the list have anything to say about putting the MYD and
> MYI files on seperate disks or using RAID MyISAM tables??
> Eric B. wrote:
> >Okay - so I've been working around on my indexing of a table. My table
> >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
> >indexes for the table. But by doing so, I end up with an MYI index file
> >over 2Gig!!
> >Now the problem is that my query is still taking way to long to execute
> >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
> >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
> >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
> >into RAM doesn't seem realistic since this index file will grow with
> >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
> >can do to help optimize my queries further? I'm using MyISAM tables, if
> >makes any difference....
> >Thanks for any insight!
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: