MySQL Performance!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

MySQL Performance!

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

Post by William »

Thank you very much Feyd, you helped me a lot!
Post Reply