Re: Help! How to handle Massive index file???

[Chris Nolan]

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]