Greg A more elegant solution is to drive the summary table from a trigger (does MySQL have triggers?) Your table is a transaction table, so you should migrate the date into the 'summary' table with a redundant 'count' column driven by the trigger
HTH Neven > > Clarifying my previous email. > > My plan is to put the total number of impressions “for the day” for > each listing into a separate summary table. > > Cheers > > Greg > > -----Original Message----- > *From:* [email protected] [mailto:[email protected]] *On > Behalf Of *Gregor Brabyn > *Sent:* Tuesday, 12 May 2009 10:51 p.m. > *To:* [email protected] > *Subject:* [phpug] [OT] Large Database Tables > > Anyone got any good advice on dealing with large database tables. > > I have a page in the client area of a website that normally takes over > 30 seconds to load almost purely due to a query on a large database > table. The table records impressions for listings, each row represents > one listing getting an impression. The table now has over 26,000,000 > rows accumulated over a period just exceeding 1000 days. > > I have a plan to do a bit of data warehousing so that the total number > of impressions for a listing are summarized into one row in a separate > table. Once it is set up a CRON will be able to run each night and > summarize the impressions into this new table. Queries should be able > to read data quickly off this table. > > The problem I am having is that I need to summarize all the past data > which spans over 1000 days and I am finding that the MySQL query to > collect one day’s worth of summaries can easily take 5 minutes. I am > thinking that if I run a CRON to summarize a day’s data into the > warehousing table every 15 minutes then it is going to take something > like 11 days. > > At present I am also unsure of the settings on the Apache server, I > don’t think I will be able to have scripts operating for more that 10 > minutes before Apache stops them and at busy times of day my script to > summarize a day’s data could easily end up exceeding this. > > Anyone got experience and good advice on dealing with these issues. > > Cheers > > Greg > > > > > > --~--~---------~--~----~------------~-------~--~----~ NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected] -~----------~----~----~----~------~----~------~--~---
