Re: More Columns VS More Tables - Which is better?

[Allen Arakaki]

Ideally, you don't want any columns "in common." Otherwise there's potential for data inconsistencies. Without knowing your Db schema it's difficult to answer your question.

When designing a Db schema, your first concern should be to "normalize" your Db schema. Follow the "normalization rules" to insure Db integrity ... meaning, Db operations (select, insert, etc.) should leave the DB in a consistent state. Normalization is usually a process of breaking up large tables into smaller tables with clear relationships. Denormalization is usually the process for "making redundant data" to improve system performance.

There are "steps" to take an ERD (entity relationship diagram) and create the tables/columns/properties so that they obey the normalization rules. I believe it involves linear algebra (i.e. transitive properties) to take ERD entities and relationships and create the proper tables/columns/properties for 1 to 1, 1 to many and many to many relationships.

I foget how - it's been a long time since that CS DB class. I (and probably most people) just wing it, when they create their DB schema and follow a limited set of normalization rules.

I've found the following to be necessary and sufficient when developing a DB app:
1) Design the DB schema properly - via ERD and transforming the ERD into a DB schema (table, column, properties).
2) Insure your queries are cacheable - see the suggestions for the query cache
3) Use connection pooling (if necessary) to reduce the connection time
4) Get beefier machine

Although there may be situations when denormalizing may be correct, I don't see why you would focus on that when there are so many other ways to improve the performance of the DB.

Sure the following all have performance costs:
Number of Open Tables
Size of Tables
Number and Type of Columns
Joins
etc.

But that shouldn't be your concern. That's the concern of the mysql developers (to create a fast engine that can handle small/med/large tables, etc.) Your concern should be to design your db schema properly. And then tune the mysql DB engine accordingly till you get the performance you desire.

------------- I know I didn't answer your question, but here's how you can find the answer yourself ...

As a general rule for fast performance:
Use indexes (ideally primary keys) and limit indexes to single columns
You can then test the speed of the JOIN ... prefer EQUI JOINs
For example
SELECT t1.col, t2.col FROM table1 AS t1, table2 AS t2 WHERE t1.key=t2.key
is very fast

There will be a cost to the JOIN (as well as the number of OPEN TABLES) - but there's also a cost to WIDE TABLES (i.e. lots of columns) ...

Anyway, it should be easy enough to test whether it is better (in your case) to have a WIDE TABLE or NARROWER tables and using JOINS.

Be sure to turn off query caching - otherwise you won't get proper benchmarks.

IMHO you should design the schema properly, then tune the DB for your schema.

my 2 cents ...