Hello, I'm currently working on a project with a few people and we're currently at a debate on which of the following database structures is faster. Now this is all about a user database, users have a profile, and other information stored in a database. We would like to know which one is faster.
1) Having multiple databases with user details split up, maybe having one database for login information, one for profile information .etc
2) Having multiple tables instead of databases.
3) Having it all in the same table.
Another question is the following also good for performance, having users stored like: users_a, users_b, where the letter is the first character of the users last name. If so, what would be faster, having each table have it's own database, or each table being in the same database?
And the last question! A buddy and me are on a debate about the SQL JOIN command, isn't it true that JOIN is slower then just doing multiple SQL query's?
Thanks,
William
MySQL Performance!
Moderator: General Moderators
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
3 is 100% out. 1 will require the database to perform more authentication checks so it will likely run a tiny bit slower than 2. The actual difference should be fairly minimal however.
It's rare that splitting up a table to spread records out more will help performance. It will be more of an administrative nightmare than a programming one, however the programming side will still be necessarily more complicated.
It's rare that splitting up a table to spread records out more will help performance. It will be more of an administrative nightmare than a programming one, however the programming side will still be necessarily more complicated.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
I have yet to find a reason for any project I've been on to justify separate databases. I always have multiple tables. I try to follow a standard set of normalization guidelines... which often falls in line with when to create a new class over using an existing one.
JOIN can be very fast, but you have to engineer the query correctly and use indexes.
JOIN can be very fast, but you have to engineer the query correctly and use indexes.