Thanks Antoine, You mention lots of things I will have to research.
I was unaware of the mysql feature 'INSERT INTO … ON DUPLICATE KEY UPDATE'. The website should have originally been built recording impressions with this. Datetime is not really needed so I am planning to convert it to just date. Cheers Greg --- On Fri, 18/9/09, Antoine S. <[email protected]> wrote: > From: Antoine S. <[email protected]> > Subject: [phpug] Re: Large Database Table Problems > To: "NZ PHP Users Group" <[email protected]> > Date: Friday, 18 September, 2009, 1:36 PM > > 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] -~----------~----~----~----~------~----~------~--~---
