SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast wulf2001's Avatar
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    database capacity and search times - help

    Over my years on the web I've learned to look before you leap. After venturing into more than enough mid to large scale project, I've finally learned that if you don't get an understanding of your capacity you may end up having wasted a large deal of time.

    I'm currently constructing a new website which will be holding an immense database (at least by my standards) and I was wondering if it would be wise to store all my info in one database... Basically what I will be doing is having 1 database in which every member of my site hopefully upwards of 100,000 will each have their own table if not several tables and within those tables dozens of different columbs of information and thousands of rows of storage.

    I was wondering what type of search times I would be looking at or if it would be a wiser choice to use text files to store my information as opposed to a mysql database. Basically I'm willing to accept no further than a 2 second search time considering the fact that I will be over using this database having a single person search, extract and input information on a per second basis many of the times.

    Any input would be greatly appreciated.

  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)
    100,000 different user tables? whoa

    can you explain why you need separate tables for them?

    most database designers would use just one table, with an appropriate user_id
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast wulf2001's Avatar
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well its going to be storing stuff such as each time the user logged on what ip he was using at that time, chat details such as what room they entered when how long they were there etc...

    As you can see it cant be done under one table each user would need their own table and like I said I'm shooting for at least 100,000 members.

    Do you think queries would be short or is this going beyond the limit?

    My other options are attempting to use text files to store much of my data but I've realized that with text files it may take up more space.

  4. #4
    Strokin' Morango dele454's Avatar
    Join Date
    Oct 2005
    Location
    Cape town, South Africa
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wulf2001 View Post
    well its going to be storing stuff such as each time the user logged on what ip he was using at that time, chat details such as what room they entered when how long they were there etc...

    As you can see it cant be done under one table each user would need their own table and like I said I'm shooting for at least 100,000 members.

    Do you think queries would be short or is this going beyond the limit?

    My other options are attempting to use text files to store much of my data but I've realized that with text files it may take up more space.
    That is just too much tables. I agree with r937. I dont think you need that much table. In fact i once wanted to go this route ( But not with 100,000 tables i remember r937 advising me it is a clear picture of a 'bad database design'.- The moment you are thinking of having such ridiculous amounts of tables.

    I dont see any reason for instance, why you cant have one 'log' table to house all your member's activities per context. You dont need separate tables for that. You can have series of log tables to captures activities across the site with a userid that uniquely identifies each user.

    It is definitely going down the wrong road! It is also going to be so much pain maintaining those tables in the long run.

  5. #5
    SitePoint Enthusiast wulf2001's Avatar
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what about lets say 20-50 tables with half a million rows of data in each table? Do you think that would be a fairly quick search? or would it still exceed over 2 seconds per search.

  6. #6
    secure webapps for all Aleksejs's Avatar
    Join Date
    Apr 2008
    Location
    Riga, Latvia
    Posts
    755
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It really depends on how well your query is written and how well table structure and indexes are built.

  7. #7
    SitePoint Enthusiast wulf2001's Avatar
    Join Date
    Feb 2008
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmmmm......
    Have any advice on where to begin seeking for research resources on maximizing my query efficiency?

  8. #8
    secure webapps for all Aleksejs's Avatar
    Join Date
    Apr 2008
    Location
    Riga, Latvia
    Posts
    755
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  9. #9
    Strokin' Morango dele454's Avatar
    Join Date
    Oct 2005
    Location
    Cape town, South Africa
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Most importantly at this stage is to try and get your db design sorted. Organize all the intended information into associated tables, create your indexes on columns you will be searching data with, create sample data - could be 100,000 or more like you mentioned, run queries to fetch data as required from your application. - then you can see what queries are slow and what you can optimize. I will suggest that as a practical step towards realising your goals. Also need to decide if you need RI for your tables, if any and which you dont etc.

    Like for your log tables i will suggest MYISAM.

  10. #10
    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)
    Quote Originally Posted by dele454 View Post
    Also need to decide if you need RI for your tables...
    i can answer that one already -- yes, you do

    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
  •