Hi.
Try using a single, simple query to stop placing unnecessary work
accessing slow disk-resident data.
Perform this:
using a script from the command line if you have such access,
via cron if cron jobs are not limited in CPU time (or
reasonable
memory use),
by backing up the table and restoring it to your own machine
and
doing it there, or
or possibly from a script running on your machine attaching to
the
database remotely.
Then:
Read each row of the table simply without a WHERE or ORDER BY
or
GROUP BY clause (SELECT * FROM impressions;),
summarise results in memory (e.g. arrays); and
when all rows have been read, calculate any necessary stuff
(like
averages, if you are doing that) as you write summaries to the new
summary table.
There is no faster way to read all of the data in a table, which is
certainly what is slowing you down now.
-Craig
On 12/05/2009, at 10:51 PM, Gregor Brabyn wrote:
> 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]
-~----------~----~----~----~------~----~------~--~---
