Carefully choose attribute types and lengths. Where possible, use small variants such as SMALLINT or MEDIUMINT rather than the regular choice INT. When using fixed-length attributes, such as CHAR, specify a length that is as short as practical. 

Use fixed-length attributes; that is, try to avoid types such as VARCHAR or BLOB. While fixed-length text attributes may waste space, scanning fixed-length rows in a query is much faster than scanning variable-length rows. Use trim when selecting. Only do this when you will actually have such fields in where clauses. If not, use your VARCHARS

Design indexes with care. Keep the primary key index as small as possible, create only indexes that are needed if you can see where's and joins on queries, and use prefixes of attributes where possible. Ensure that the leftmost attribute in the index is the most frequently used in queries and, if all attributes are used, make sure the leftmost attribute is the one with the highest number of duplicate entries. eg. SELECT * FROM A WHERE B=3 AND C=5 have an index with B and C, so SELECT .... where B=3 will use the same index. Don't use indexes in all rows (have seen many coders do this) - depends on how many where and join hits it gets, and size of the table. Some tables (with fewer rows) are actually slower with indexing.

Create a statistics table if aggregate functions such as COUNT( ) or SUM( ) are frequently used in queries on large tables. A statistics table stores only one row that is manually updated with the aggregate values of another table. For example, if the statistics table maintains the count of rows in a large customer table, each time a row is inserted or deleted in the customer table, the count is updated in the statistics table. For large tables, this is often faster than calculating aggregate functions with the slow built-in functions that require complete processing of all rows.

If large numbers of rows are deleted from a table, or a table containing variable-length attributes is frequently modified, disk space may be wasted. MySQL doesn't usually remove deleted or modified data; it only marks the location as being no longer in use. Wasted space can affect access speed.

To reorganize a table.by copying data to a temporary location and back again. MySQL provides the OPTIMIZE TABLE command, which should be used periodically. For example:

OPTIMIZE TABLE customer;

The OPTIMIZE command should be run when the DBMS is offline for scheduled maintenance. The command is nonstandard SQL.

It is possible to create different table types for specific tasks. The default in MySQL is the MyISAM type. For small, temporary, frequently used lookup tables, a different type, the heap table type, can be used. There are other types like InnoDB, BDB, etc