Optimizing tables

• MySQL has a rich set of different types. You should try to use the most efficient type for each column.
• The ANALYSE procedure can help you find the optimal types for a table: SELECT * FROM table_name PROCEDURE ANALYSE()
• Use NOT NULL for columns which will not store null values. This is particularly important for columns which you index.
• Change your ISAM tables to MyISAM.
• If possible, create your tables with a fixed table format.
• Don't create indexes you are not going to use.
• Use the fact that MySQL can search on a prefix of an index; If you have and INDEX (a,b), you don't need an index on (a).
• Instead of creating an index on long CHAR/VARCHAR column, index just a prefix of the column to save space. CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10)))
• Use the most efficient table type for each table.
• Columns with identical information in different tables should be declared identically and have identical names.