SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    518
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Best table type for visit logging

    I'm setting up tables to store details of visitors. One table with details of the visitor session, and another containing the pages they viewed. We're looking at upwards of 4 million records for the visitors table and 20 million for the pages table.

    I'd probably be running occasional queries on the tables and obviously I wouldn't want that to slow down the user tracking. I could, if necessary, insert into a temporary table and copy across to the main table at non-peak times?

    The default table type is MyISAM. Is this the best table to use or would another be more suitable?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    myisam uses a table lock when doing inserts, deletes, or updates

    clearly for tables as large as you expect (and the assumed frequency of inserts), this could be a problem
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    518
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Might InnoDB be better then?
    Would it also be faster (albeit take up more disc space) if I convert all VARCHAR fields to CHAR?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes definitely, and yes but not measurably
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •