Speed-up of readonly MyISAM table

Question

We have a large MyISAM table that is used to archive old data. This archiving is performed every month, and except from these occasions data is never written to the table. Is there anyway to "tell" MySQL that this table is read-only, so that MySQL might optimize the performance of reads from this table? I've looked at the MEMORY storage engine, but the problem is that this table is so large that it would take a large portion of the servers memory, which I don't want.

Hope my question is clear enough, I'm a novice when it comes to db administration so any input or suggestions are welcome.

Answer 1

You could use myisampack to generate fast, compressed, read-only tables.

(Not really sure if that hurts performance if you have to return most of the rows; testing is advisable; there could be a trade-off between compression and disk reads).

I'd say: also certainly apply the usual:

1. Provide appropriate indexes (based on the most used queries)
2. Have a look at clustering the data (again if this is useful given the queries)

Answer 2

Yes, you can compress the myisam tables.

Here is the doc from 5.0 : http://dev.mysql.com/doc/refman/5.0/en/myisampack.html