SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    Calgary
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Dillemma! Storing Lists in a Database

    I have many pages of content (several hundred) that will have lists of items on each page.

    I want to be able to compare items on lists from different pages and show matching items or similar items.

    I am concerned about MySQL performance as more and more information is added to the database.

    Would it be best to enter each list item individually in a MySQL table as its own row (many rows per page), or should I enter entire lists as a serialized array (1 row per page)? What is the best way to store these lists?

    Thanks in advance for any help!!

  2. #2
    SitePoint Addict
    Join Date
    Sep 2007
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you need to access individual list items for comparison, then storing each list item individually in a table row and using mysql to process them will be more efficient compared to using php to do the same after unserializing the lists.

  3. #3
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by nighthawk99 View Post
    I am concerned about MySQL performance as more and more information is added to the database.
    Working with tables containing 3bill records, so if you have your indexes done right, selecting won't be a problem.

    Inserting gets slower, but still not bad, and forget about deleting records.

  4. #4
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    Calgary
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Vali View Post
    Working with tables containing 3bill records, so if you have your indexes done right, selecting won't be a problem.

    Inserting gets slower, but still not bad, and forget about deleting records.
    I will need to both update and delete some of the records from time to time. What do I do? Am I kind of screwed or is there a real scalable way to do this properly?

  5. #5
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Update you need, but chances are you don't need to update the table keys.
    Delete, usually you don't need, since you want to keep the data for reports and so on (so usually you just change a "deleted" flag)

    For "real scalable way", there is always a way, but chances are your system will not reach over 10k records, so you should be fine.


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
  •