1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Louisville, Kentucky U.S.
    Rep Power

    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?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Toronto Canada
    Rep Power
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo