MySQL Optimization

Optimization is a complex task because ultimately it requires understanding of the entire system to be optimized. Although it may be possible to perform some local optimizations with little knowledge of your system or application, the more optimal you want your system to become, the more you will have to know about it.

The most common system bottlenecks are:

*Disk seeks. It takes time for the disk to find a piece of data. The way to optimize seek time is to distribute the data onto more than one disk.
*Disk reading and writing. When the disk is at the correct position, we need to read the data.This is easier to optimize than seeks because you can read in parallel from multiple disks.
*CPU cycles. Having small tables compared to the amount of memory is the most common limiting factor. But with small tables, speed is usually not the problem.
*Memory bandwidth. When the CPU needs more data than can fit in the CPU cache, main memory bandwidth becomes a bottleneck.

Tips for optimization:
--------------------------------------------------------------------------
- The EXPLAIN statement can be used either as a synonym for DESCRIBE or as a way to obtain information about how MySQL will execute a SELECT statement

- Try to avoid complex SELECT queries on MyISAM tables that are updated frequently, to avoid problems with table locking that occur due to contention between readers and writers.

-With MyISAM tables that have no deleted rows, you can insert rows at the end at the same time that another query is reading from the table. If this is important for you, you should consider using the table in ways that avoid deleting rows. Another possibility is to run OPTIMIZE TABLE after you have deleted a lot of rows.

- If you very often need to calculate results such as counts based on information from a lot of rows, it's probably much better to introduce a new table and update the counter in real time. An update of the following form is very fast:

UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;

- If you need to collect statistics from large log tables, use summary tables instead of scanning the entire log table. Maintaining the summaries should be much faster than trying to calculate statistics "live." It's much faster to regenerate new summary tables from the logs when things change (depending on business decisions) than to have to change the running application!

- Take advantage of the fact that columns have default values. Insert values explicitly only when the value to be inserted differs from the default. This reduces the parsing that MySQL needs to do and improves the insert speed.

- Normally, you should try to keep all data non-redundant (what is called "third normal form" in database theory). However, do not be afraid to duplicate information or create summary tables if necessary to gain more speed.

- You can always gain something by caching queries or answers in your application and then performing many inserts or updates together. If your database supports table locks (like MySQL and Oracle), this should help to ensure that the index cache is only flushed once after all updates.

- Use INSERT DELAYED when you do not need to know when your data is written. This speeds things up because many records can be written with a single disk write.

- Use INSERT LOW_PRIORITY when you want to give SELECT statements higher priority than your inserts.

- Use SELECT HIGH_PRIORITY to get retrievals that jump the queue. That is, the SELECT is done even if there is another client waiting to do a write.

- Use multiple-row INSERT statements to store many rows with one SQL statement (many SQL servers support this).

- Use LOAD DATA INFILE to load large amounts of data. This is faster than using INSERT statements.

- Use AUTO_INCREMENT columns to generate unique values.

- Use OPTIMIZE TABLE once in a while to avoid fragmentation with MyISAM tables when using a dynamic table format.

- Use HEAP tables when possible to get more speed.

- When using a normal Web server setup, images should be stored as files. That is, store only a file reference in the database. The main reason for this is that a normal Web server is much better at caching files than database contents, so it's much easier to get a fast system if you are using files.

- Use in-memory tables for non-critical data that is accessed often, such as information about the last displayed banner for users who don't have cookies enabled in their Web browser.

- If you are using numerical data, it's faster in many cases to access information from a database (using a live connection) than to access a text file. Information in the database is likely to be stored in a more compact format than in the text file, so accessing it will involve fewer disk accesses. You will also save code in your application because you don't have to parse your text files to find line and column boundaries.

- Declaring a MyISAM table with the DELAY_KEY_WRITE=1 table option makes index updates faster because they are not flushed to disk until the table is closed.

To read more click here