mySql planning

[ggalan]

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

[djr33]

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.

[ggalan]

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

[djr33]

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.

[traq]

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
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.

[ggalan]

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?

[djr33]

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.

[ggalan]

couldnt you search for a table just like a row?


SELECT * FROM DBNAME.tableName;

[traq]

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:
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.

[djr33]

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.

[ggalan]

i see
im just worried that if i keep everything in a few tables then 1 table has a chance of ballooning to over 100K rows
btw, to clear up any confusion i am not trying to connect to different databases but rather thinking of dynamically creating new tables to keep the number of rows in a particular table under control, but djr33 suggested not to do this

[djr33]

It's better for organization to have more rows in a single table and fewer tables. Research "large databases" if you want. But you don't need to worry. I'm sure there are databases out there with millions of rows.

On the other hand, your server may not be powerful enough. But if the server is good enough then it should work. If you run into storage space issues or slow processing then it's time to move to a new server.


You can do whatever you'd like, but having lots of rows is a main design feature in databases.

[ggalan]

great! thanks for all your input

[traq]

the default max table size (http://dev.mysql.com/doc/refman/5.0/en/full-table.html) using the MyISAM engine in mysql is 4GB. Properly configured, you can increase that to 65,536 TB (yes, terabytes). it's more of an operating system (or plain old disk size) issue than anything you might need to do differently with your data structure.