Read-Only Acceleration of MyISAM Table

Question

We have a large MyISAM table that is used to archive old data. This archiving is performed every month, and unless the data is never written to the table. Does MySQL need to be “informed” that this table is read-only so that MySQL can optimize the read performance of this table? I looked at the MEMORY storage engine, but the problem is that this table is so large that it will require most of the server memory, which I don't want.

Hope my question is clear enough, I'm new when it comes to administering db, so any suggestions or suggestions are welcome.

Answer 1

Yes, you can compress myisam tables.

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

Answer 2

Instead of un-and re-compressing history tables: If you want to access a single table for history, you can use a merge table to join read-only compressed history tables.

Thus, assuming you have an active table and compressed history tables with the same table structure, you can use the following scheme:

Tables:

compressed_month_1 compressed_month_2 active_month 

Create a merge table:

 create table history_merge like active_month; alter table history_merge ENGINE=MRG_MyISAM union (compressed_month_1,compressed_month_2); 

After a month, compress the table active_month and rename it to compressed_month_3 . Now the following tables:

 compressed_month_1 compressed_month_2 compressed_month_3 active_month 

and you can update the history table

 alter table history_merge union (compressed_month_1, compressed_month_2, compressed_month_3); 

Answer 3

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

(I’m not quite sure that this can hurt performance if you need to return most of the lines, preferably testing, there may be a trade-off between compression and reading of the disk).

I would say: also be sure to apply the usual one:

• Indicate the appropriate indexes (based on the most frequently used queries)
• Look at data clustering (again, if useful for queries)