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