MySQL-Tunning (mysql tune speed)

Method increase mysql speed

1 key cache : load index into cache
e.g. set global city_cahc_key_buffer_size = 4194304
cache index world.cityin city_cache
load index into cache world_city.

2. avoid re-open table set table_cache. Refer :MySQL config.

3. query cache for repeat and some result.

4. for order by / group by set sort_buffer_size. Refer : MySQL config

5. join buffer for complex joins.

6. Record Buffer, cache per connection. for reducint the number of reader from disk.

7. Explain. e.g. explain Select * from country where name="Frame"
find out the key. Describe, show Index.

Tune innodb(mysql web site) :
1. use auto_increment column as primary key
2. use varchar instad of char
3. set innodb_flush_log_at_trx_comment = 0
4. innodb_buffer_pool_sizeUse DROP TABLE and CREATE TABLE to empty a table, notDELETE FROM tbl_name.
5.  setting the innodb_flush_method parameter to O_DSYNC.