Queries - When is More More, and When is Less More?

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
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Queries - When is More More, and When is Less More?

Post by Jonah Bron »

Hello, world!

It this thread:
viewtopic.php?f=1&t=121692

Josh, McInfo, and DigitalMind stated that breaking joins into multiple queries was better, after I asserted the opposite. Their explanation satisfied me. But, I have encountered several places in POEAA (Fowler) that appear to contradict this. Examples are on 266 (paragraph 1), 256 (paragraphs 2 & 3), and 243 (paragraph 3). There are probably other examples that I've forgotten about, and more that I haven't read yet :roll:

I'm sure that both parties know what they are talking about, and there's some subtle difference/performance improvement I haven't caught. Perhaps someone could clarify for me?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Queries - When is More More, and When is Less More?

Post by Eran »

There's no truism regarding queries - each have to be looked at in context. As long as performance is good, it's preferable to fetch all relevant information in one query, as concurrency is always an issue and it usually simplifies application logic. Sometimes, one of the JOINs in a multiple table join really brings down query performance OR makes some aggregate information impossible to calculate (such as SUM(), AVG() etc). In that case it's better to split that troublesome join out, even if it increases application logic complexity somewhat.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Queries - When is More More, and When is Less More?

Post by Jonah Bron »

I see, so basically it varies from case to case.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Queries - When is More More, and When is Less More?

Post by califdon »

Disclaimer: many others here are far better qualified than I am to make judgments on database engine internals and optimization strategies -- but that never prevented me from speaking out before, so I'll add my 2 cents worth (and that may be over pricing it, considering inflation).

I agree with Eran, based mostly on what I've heard others say whom I have reason to believe truly do understand this complex subject, such as an acquaintance who spent 5 years writing the internal code for MySQL at MySQL AB.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Queries - When is More More, and When is Less More?

Post by Weirdan »

Basically there's only one true method for performance optimization:

Code: Select all

do {
  profile();
  makeYourChanges();
  $results = profile();
} while (!$results->satisfiable());
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Queries - When is More More, and When is Less More?

Post by Jonah Bron »

Heh, that's a pretty good representation. Thanks for the help.
Post Reply