Hi, I have worked in a bank and we had to solve such problem of amount of data and speed. For my data model and my queries, I have found few solutions. I had two types of access : live extranet website and calcul engine. For the website, we solve many problem by running queries during the night (or on demand) to create denormalized table and then replace the previous one. (clients tree, products tree, cache table..) For the calcul engine, we dump the needed table to another mysql server, and then use the following tips : - Denormalized table - 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 then 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 - We decomposed a year on quarters, so we had a structure like : Date (Datetime), Year(Smallint unsigned), Quarter(tinyint unsigned) and indexed on (Date) and (Year, Quarter), that was a kind of partition - 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 !) - Replace : 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 et 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 .. We just
That's all I remember for now.. Hope you will find a way ! Antoine www.doubleclique.com --~--~---------~--~----~------------~-------~--~----~ NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected] -~----------~----~----~----~------~----~------~--~---
