June 17th, 2011, 10:49 AM
Website Statistics Query
What is the best method for saving large amounts of data such as site stats without bogging down the system by joining such a massive table?
I figured I should avoid doing UPDATE queries to avoid table locking for speed purposes but I have created the other problem for the report side of things. When I join the table of millions of rows it tends to timeout. I do have indexes and such but the query is so complex.
I was thinking about continuing mass INSERT SELECTS to save data, but instead of joining the table when fetching stats I will have another table that holds counts. I do this with a view but that as you know is just a query. If I switch it to another table that holds the counts and populate this table with a cron job would this solve my problem? is there a better solution? Also would it be better to write to the server instead of a table?
June 17th, 2011, 03:11 PM
you've asked a bunch of questions but you haven't told us what your tables look like, or where the updates are coming from
in fact, we don't have enough information even to determine whether you're doing updates or inserts