Document MySQL optimisation

En français, des notes pour optimiser une base de données MySQL
(type de données, dates, astuces, serveur)
http://spreadsheets.google.com/pub?key=toSt1VdSpf8rdn9YTCTe9Jw&output=html

MySQL Optimisation

Here is some advise to speed up a database or to manage a large amount of data.

- Denormalized table (a CRON can create them every night)
- Use Memory/Heap table
- MyISAM better than InnoDB (just for the speed)
- Right choice of the indexes (be carefull when using memory table,
you need to change them and use Btree if you have to sort or group
your data)
Sometimes you need two indexes : col1, col2 and another one col2, col1
- There is tricky things about DATE, I think in a WHERE, Date =
Datetime wont use index, you need to use Date = Date(Datetime), use
EXPLAIN to find out
- Decompose a year on quarters, so you have a structure like : Date
(Datetime), Year(Smallint unsigned), Quarter(tinyint unsigned) and
indexed on (Date) and (Year, Quarter),
> this is a kind of "partitioning"
- Use the right Datatype to minimize the size of the tables (dont use
Datetime if Date is sufficient, Tinyint and not Int, and Unsigned (one
bit but important on billion of line!) like for NULL(1 extra bit) and
NOT NULL !)
- Best Replace query :
with InnoDB : INSERT INTO … ON DUPLICATE KEY UPDATE
with MyISAM: REPLACE
- No sub-query most of the time, prefer create a temporary table and
join on it. (JOIN ON will be your WHERE when using a subquery)
- Datawarehouse, store the old data somewhere else, and notice that it
gonna be slow to access the archived.
- Then a huge part for the server configuration ! see my-huge.cnf, and
keybuffersize and table_cache
When I did this, MySQL could not use multi-processors, and it was
better to have a huge memory RAM and fast hard drive.
- MySQL Table partition was useless in our case, maybe not in yours ..

Time to start a blog, snippet .. anything