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 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
>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!