Long row read verses multiple queries (mySQL)

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
kristen
Forum Newbie
Posts: 14
Joined: Tue Sep 07, 2010 5:51 pm

Long row read verses multiple queries (mySQL)

Post by kristen »

Which is the lesser of the evils?

- Add more col to a table so that the DB is only getting hit with one query
- Add a second table, hit the DB with 2 queries and leave the rest to code?

Scenario:

I have a table that is already 19 col- the table is a unique ID#, then a pair of values: current value and max potential value. So I chose not to split them into 2 tables because the 2 values must always travel as pairs (everything is in context of potential vs final)

Now I need to add a third set of data (bonuses) So it was would be potential + max + bonuses. This would add another 9 col. Or I could put it in its own table and code the rest.

No actual calculations are done by the database- just very simple selects and the code does all the math.

So. Less evil to add more col, or less evil to have 2 queries?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Long row read verses multiple queries (mySQL)

Post by califdon »

In relational databases, with perhaps an occasional rare exception, there is no such thing as "too many columns" and the answer to "what columns belong in this table" is the same as the answer to "how many tables are needed in this database":

A table represents an entity in your data model. Absolutely everything is determined by that and that alone. The first thing you must always do is clearly define your data model as relationships among entities. Once you have done that, you determine what attributes each entity has, and these are the columns needed in its table. You will then need to consider what Primary and Foreign keys are needed in each table to support the relationships. That's it. There is never (or practically never) a reason to ask yourself questions like "would it be better if I split this column out to a different table." Exceptions to these rules may be necessary in enormously large databases (terabyte files or a table with several hundred columns) for performance purposes or in distributed databases.
kristen
Forum Newbie
Posts: 14
Joined: Tue Sep 07, 2010 5:51 pm

Re: Long row read verses multiple queries (mySQL)

Post by kristen »

Thank you. Early on I got hit over the head with the idea that no well designed DB will ever have more then 10 cols per table, and it's always lurking in the back of my mind.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Long row read verses multiple queries (mySQL)

Post by califdon »

kristen wrote:Thank you. Early on I got hit over the head with the idea that no well designed DB will ever have more then 10 cols per table, and it's always lurking in the back of my mind.
Somebody gave you extremely bad advice, then. I can't think of a professional database I ever developed where tables were that small, except for lookup tables.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Long row read verses multiple queries (mySQL)

Post by Christopher »

Good advice califdon!. This is also a case where the issue of Premature Optimization comes into play. And that is probably where the 10 column rule came from. In general you should do what creates the best designed code -- until you have an actual performance problem. Then look into optimizations.

A question here may be: how much duplicated data is in that 19 column data table? And does that duplication matter? If there is a lot of duplication then moving the duplicated data into separate tables and JOINing might be better. Especially if that makes maintaining the data simpler.

But there is also, especially for the web, this newer idea of keeping data closer to the format is it used in, rather than normalizing for the sake of normalizing.
(#10850)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Long row read verses multiple queries (mySQL)

Post by califdon »

Thanks, Christopher. As you probably know, I'm an old-timer and kind of dogmatic when it comes to relational data normalization. I learned it from taking a seminar from Chris Date, who (as I'm sure you know) worked with Ted Codd, the inventor of the relational model and SQL, and who wrote the definitive book on relational databases that is still used in practically every university computer science course across the world. As an old-timer, I may be guilty of distrusting you young whippersnappers and your fancy new ideas, but as a friend of mine says, maybe I'll learn better when I'm younger. :wink:
Post Reply