Page 1 of 1
Long querys vs many little queries
Posted: Tue Jul 25, 2006 5:23 am
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?
Re: Long querys vs many little queries
Posted: Tue Jul 25, 2006 5:27 am
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.
Posted: Tue Jul 25, 2006 5:31 am
by shiznatix
alright thanks
Posted: Tue Jul 25, 2006 5:32 am
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.
Posted: Tue Jul 25, 2006 5:33 am
by Benjamin
Have fun writing that query

Posted: Tue Jul 25, 2006 12:11 pm
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?
Posted: Tue Jul 25, 2006 1:05 pm
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.
Re: Long querys vs many little queries
Posted: Thu Jul 27, 2006 4:54 pm
by jmut
...maybe you should run EXPLAIN on your SELECT query with 5 LEFT JOINs and see what it says. might be helpful
Posted: Thu Jul 27, 2006 5:48 pm
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.