Page 1 of 1

MySQL Performance!

Posted: Mon Dec 04, 2006 12:55 pm
by William
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

Posted: Mon Dec 04, 2006 1:08 pm
by feyd
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.

Posted: Mon Dec 04, 2006 1:11 pm
by William
So what would YOU do in this situation? Also what about the SQL JOIN function, is it slower then just running multiple query's?

Thanks,
William

Posted: Mon Dec 04, 2006 1:15 pm
by feyd
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.

Posted: Mon Dec 04, 2006 1:18 pm
by William
Thank you very much Feyd, you helped me a lot!