how to improve query performance for a 40gb mysql isam table?

We have a big table about 40gb table (MYD + MYI file total), with all the indexable column indexed, including composite indexes of commonly joined columns, but the query performance is still excruciatingly slow. What else can I do to improve the query performance? Our server is hyperthreaded quad core with 8gb ram running linux. Thanks a lot in advance.

here is开发者_StackOverflow社区 a simple query using the table:

select sq.sq_search_id, count(ssh.sq_search_hit_id)
from sq_search sq
inner join sq_search_hit ssh 
use index(by_sq_search_id) on sq.sq_search_id = ssh.sq_search_id
where sq.search_ts >= '2011-09-07'group by sq.sq_search_id

here is the explain result:

1. 1, 'SIMPLE', 'ssh', 'ALL', 'by_sq_search_id', '', '', '', 149683523, 'Using temporary; Using filesort'
2. 1, 'SIMPLE', 'sq', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'lims.ssh.sq_search_id', 1, 'Using where'

224 rows found. Duration for 1 query: 359.754 sec

David

With that much information we cannot really give you advices, just very basic ones:

• More RAM
• Use SSD
• Shard the database if possible
• If the DB is read heavy use Master-Slave configuration.

Update

1, 'SIMPLE', 'ssh', 'ALL', 'by_sq_search_id'

That's a full table scan right there, I hope I don't have to explain how bad that is.

Is it faster if you remove the index hint? Is that the right index? Have you tried to qualify the table name for it?

As said here, you should expand the size of key_buffer_size. Here you have more info:

Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.

Try to make all your indexes (key_buffer_size) fit in memory.

Also, is there an index on sq.search_ts and ssh.sq_search_id?