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] -~----------~----~----~----~------~----~------~--~---
