Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: mySql planning

  1. #1
    Join Date
    Jan 2008
    Posts
    441
    Thanks
    67
    Thanked 4 Times in 4 Posts

    Default mySql planning

    i have a site with several thousand members. when each member registers i need to create 3 tables for this member. should i create a new database for each member or create 3tables using a unique prefix for each member under 1 large database?
    trying to plan the best way for backups and performance, thanks
    Last edited by ggalan; 06-30-2011 at 08:18 PM.

  2. #2
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    You should not be dynamically creating tables. Create rows instead. Create a table to hold all information (for example passwords), and link each row to a particular user with their ID.
    In terms of performance, that will be MUCH better.
    If you absolutely can't do that (and I recommend rewriting the entire site if you have to), then use tables, not databases. Prefixes should be fine, I guess.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  3. #3
    Join Date
    Jan 2008
    Posts
    441
    Thanks
    67
    Thanked 4 Times in 4 Posts

    Default

    so you feel that thousands of rows (in few tables) is better in performance & maintenance than thousands of tables containing small amounts of rows?

  4. #4
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    Short answer: yes.

    Longer answer: It's not that technically rows take less space, but it keeps your database better organized and will allow for searching and things like that. Technically, it would probably take exactly the same amount of space both ways, but there are a number of reasons not to dynamically generate tables. The main one is that it will become extremely difficult to keep track of, and another is that duplicate table names would be a major problem, but duplicate rows are allowed. More generally, that's just how databases work. Minimize duplication of things, so when you are repeatedly storing similar types of data, store them in the same table. Link between different entries by referring to the ID of another. Each row (in each table) gets an ID. That ID then can link any elements together-- a user and his/her password, for example.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  5. The Following User Says Thank You to djr33 For This Useful Post:

    ggalan (06-30-2011)

  6. #5
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    absolutely. for each database, you'd need a new connection (or at least, a separate connection, to ensure you don't end up querying the wrong database). It's much more efficient.

    It will keep your code better organized and more efficient, as well. Plus, you can use more dynamic queries: something like
    Code:
    SELECT tbl1.name, tbl2.favquote FROM tbl1 INNER JOIN tbl2 ON tbl1.userid = tbl2.userid
    is really quick, but it's impossible if the two tables are in different databases.

  7. #6
    Join Date
    Jan 2008
    Posts
    441
    Thanks
    67
    Thanked 4 Times in 4 Posts

    Default

    absolutely. for each database, you'd need a new connection (or at least, a separate connection, to ensure you don't end up querying the wrong database). It's much more efficient.
    so i will potentially have 4 tables consisting of thousands of rows in 1 database
    just out of curiosity, what do you think is the max number of rows? 10, 20 50 thousand?

  8. #7
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    I don't think there is a limit to the number of rows. However, there may be a limit on the space allowed in the database (or on your server). Look at how much space your current database takes and multiply that as needed. Then look at your limits.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  9. #8
    Join Date
    Jan 2008
    Posts
    441
    Thanks
    67
    Thanked 4 Times in 4 Posts

    Default

    couldnt you search for a table just like a row?
    Code:
    SELECT * FROM DBNAME.tableName;

  10. #9
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    I did some reading, and apparently, you can. in PHP, however, this can lead to problems because the last-used database "overwrites (for lack of a better word)" the selected database for that handle. This means that the next time you use the handle, you might end up with the wrong database. In addition, the databases must be on the same server (in actuality, or configured to be "virtually" on the same server).

    There is an apparent solution, which is to not use mysql_select_db(). This means that you have to specify the database in every query, however:
    Code:
    SELECT db.tbl.col FROM db.tbl WHERE db.tbl.col = 'condition'
    ...or similar... I haven't tested any of this. Honestly, it seems like more trouble then it's worth. and I don't know what this does to efficiency.

    In terms of data organization and code structure / efficiency / cleanliness, it makes more sense to do things the standard way (as djr described). All of the examples I've found that use this method are trying to coordinate between separate websites - not running functions on a single site.
    Last edited by traq; 07-01-2011 at 04:22 AM.

  11. #10
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    If it's one website and you control everything, use one database. If not, then you may have to find a way around it. But that should not be the default method. It's your choice of course but I don't think it's worth the extra work. And at best efficiency will be the same (worse?) using the more complicated method.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

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
  •