How to optimize mysql

MySQL is a database engine similar to postgresql, oracle, or Ms SQL server in that it allows you to create tables and store data.

For many dynamic web sites MySql is a requirement for operation - and is a huge bottleneck potential if the application and the server are not configured properly

Difficulty: Moderate

Instructions

1. Run optimize table against your tables
   This command defragments a table after you have deleted a lot of rows from it.

2. Use insert delayed when you do not need instant gratification - a response instantly - when your data is written. This reduces the overall insertion impact because many rows can be written with a single disk write.

3. use statement priorities
   
   * 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 executed even if there is another client waiting.

4. Use multiple-row INSERT statements to store many rows with one SQL statement.

5. Don't use complex permissions. The more complex your permissions setup, the more overhead you have. Try using simple GRANT statements to reduce the overhead looking up and applying permissions will cause.

6. When analyzing query performance, it's also useful to consider the EXPLAIN keyword. This keyword, when placed in front of a SELECT query, describes how MySQL intends to execute the query and the number of rows it will need to process to successfully deliver a result set.
   
   Example of usage: explain select * from table

7. Try optimizing your where clause
   - Remove unnecessary parentheses
   - when using "count (*)" on non myisam databases make sure you have indexes present
   - make your where clauses as specific as possible

8. Use Indexes
   MySQL allows you to index database tables, making it possible to quickly seek to records without performing a full table scan first and thus significantly speeding up query execution. You can have up to 16 indexes per table, and MySQL also supports multi-column indexes and full-text search indexes.
   
   Adding an index to a table is as simple as calling the CREATE INDEX command and specifying the field(s) to index.

9. Adjust internal server variables such as key_buffer, read_buffer_size, table_cache, query_cache_size, query_cache_type, query_cache_limit and long_query_time can go a long way. There is no "right answer" for those values, so you need to play around with them. they will be different on every server.

Tips & Warnings

• This variable controls the size of the buffer used when handling table indices (both read and write operations). The MySQL manual recommends that this variable be increased "to as much as you can afford" to ensure you get the best performance on indexed tables, and recommends a value equivalent to about 25 percent of the total system memory. This is one of the more important MySQL configuration variables and if you're interested in optimizing and improving performance
• When you increase max_connections make sure to also increase the maximum open tables, otherwise you may be allowing more people to connect and query then tables can allow for, thus slowing your response times.