Long querys vs many little queries

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
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Long querys vs many little queries

Post by shiznatix »

Ok I need to bring a whole bunch of tables together. Now I can either do a large query with like 5 LEFT JOINs or I can just pull a record out from the first table, then use that result to get info from the second and third and so on.

Which one would be faster? I would think the long query would be because you don't have to run the query then pull the returned rows out and run another query and whatnot but I don't know much about how fast LEFT JOINs are.

Do LEFT JOINs become slower if you use like 10 or more of them?

Also, does it make the query faster if I specify what I want returned like SELECT ...... instead of just using the * wildcard?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Long querys vs many little queries

Post by Benjamin »

shiznatix wrote:Also, does it make the query faster if I specify what I want returned like SELECT ...... instead of just using the * wildcard?
Yes, and more memory efficient as well. You want to use large joins. I had a page once that ended up with 80+ db queries because of all the sub queries it was performing. It was really slowing it down and it would have killed the MySQL server under a moderate load.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

alright thanks
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

5 left joins isn't that large... There are queries in the database I manage here at work that join 17 tables...

In terms of performance, it's difficult to say with certainty which would be quicker. My gut feeling says a joined query would be quicker than processing data returned from a query and feeding it into another query, but you'd need to benchmark it to be sure.

You should usually specify the fields you need, instead of using *. In queries with joins this is even more important, as a SELECT * on multiple tables will return all the fields from all the tables, and usually that is not what you need.

In general, the more fields that are returned by a query, the slower the query goes.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Have fun writing that query :wink:
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

It depends how many entries are expected to be in that table.

I once had a query that was joining a 7K row table with a 15K row table & the query was taking about 5 seconds. When I broke up that query & turned it into two queries without joins (but with a little PHP logic), I was able to get the query below 1 second. I've seen this behaviour mirrored elsewhere as well.

How big are the tables going to get?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

Well, it all depends upon how much memory the database server is going to use to execute the multi-joined query. If you join too many tables or have a number of LARGE tables, then the server will start using swap file space and that will slow the query process down considerably.

You really have to be careful about using joins. Sometimes it is faster to use multiple queries instead of one large joined query.
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Re: Long querys vs many little queries

Post by jmut »

...maybe you should run EXPLAIN on your SELECT query with 5 LEFT JOINs and see what it says. might be helpful
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

and make sure you know what storage engine your server is using (InnoDB, MyISAM, something else). Because of different locking methods they behave quite differently. For example with MyISAM tables with gaps running a massive query would prevent concurrent inserts to all the fragmented tables involved.
Post Reply