I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then in the application sometimes, we only need to use only 1 column.

So would it be faster to just make simple selects, and then "join" them in the system code?

The system could be in php, java, asp, or any language that connect to the database.

So the question is, what is faster going from a server side (php, java, asp, ruby, python...) to the database and running one query that gets everything we need or going from the server side to the database and running a query that only gets the columns from one table at a time?

  • 2
    Which implementation of 'SQL' are you using? MySQL, Microsoft SQL Server, Oracle, Postgresql, etc? Please update your tag.
    – RLF
    Commented Sep 18, 2014 at 12:30
  • 1
    Mysql and Postgresql
    – sudo.ie
    Commented Sep 18, 2014 at 12:45
  • 7
    My experience is that MySQL doesn't like complicated queries and is usually faster with very simply queries (but more). Postgres' query optimizer is much better and there it is usually more efficient to run a single large query.
    – user1822
    Commented Sep 18, 2014 at 12:51
  • 5
    @a_horse_with_no_name That is very broad generalization, specially in the context of this question. MySQL optimizer is indeed very simple by design, and can cause problems with joins and sub-queries -specially on older versions of MySQL- that have otherwise produce faster plans in PostgreSQL, while MySQL can be very fast for pure OLTP loads. However, in the context of the question, a single large query will be faster that, let's say -in the worse possible scenario- a SELECT inside a programming loop (no matter the RDBMS used).
    – jynus
    Commented Sep 18, 2014 at 13:54
  • 3
    @jynus: well, the question is very broad (plus: I said "in my experience" - other people might have different experiences). A query inside a LOOP is never a good idea and almost always the result of poor design or lack of understanding how to work with a relational database.
    – user1822
    Commented Sep 18, 2014 at 15:11

3 Answers 3


What would address your question is the subject JOIN DECOMPOSITION.

According to Page 209 of the Book

High Performance MySQL

You can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application. For example, instead of this single query:

JOIN tag_post ON tag_post.tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = 'mysql';

You might run these queries:

SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

Why on earth would you do this ? It looks wasteful at first glance, because you've increased the number of queries without getting anything in return. However, such restructuring can actually give significant performance advantages:

  • Caching can be more efficient. Many applications cache "objects" that map directly to tables. In this example, if the object with the tag mysql is already cached, the application will skip the first query. If you find posts with an ID of 123, 567, or 908 in the cache, you can remove them from the IN() list. The query cache might also benefit from this strategy. If only one of the tables changes frequently, decomposing a join can reduce the number of cache invalidations.
  • Executing the queries individually can sometimes reduce lock contention
  • Doing joins in the application makes it easier to scale the database by placing tables on different servers.
  • The queries themselves can be more efficient. In this example, using an IN() list instead of a join lets MySQL sort row IDs and retrieve rows more optimally than might be possible with a join.
  • You can reduce redundant row accesses. Doing a join in the application means retrieving each row only once., whereas a join in the query is essentially a denormalization that might repeatedly access the same data. For the same reason, such restructuring might also reduce the total network traffic and memory usage.
  • To some extent, you can view this technique as manually implementing a hash join instead of the nested loops algorithm MySQL uses to execute a join. A hash join might be more efficient.

As a result, doings joins in the application can be more efficient when you cache and reuse a lot of data from earlier queries, you distribute data across multiple servers, you replace joins with IN() lists, or a join refers to the same table multiple times.


I like the first bulletpoint because InnoDB is a little heavy-handed when it crosschecks the query cache.

As for the last bulletpoint, I wrote a post back on Mar 11, 2013 (Is there an execution difference between a JOIN condition and a WHERE condition?) that describes the nested loop algorithm. After reading it, you will see how good join decomposition may be.

As for all other points from the book, the developers really look for performance as the bottom line. Some rely on external means (outside of the application) for performance enhancements such as using a fast disk, get more CPUs/Cores, tuning the storage engine, and tuning the configuration file. Others will buckle down and write better code. Some may resort to coding all the business intelligence in Stored Procedures but still not apply join decomposition (See What are the arguments against or for putting application logic in the database layer? along with the other posts). It's all up to the culture and tolerance of each developer shop.

Some may be satisfied with performance and not touch the code anymore. Other simply don't realize there are great benefits one can reap if they try join composition.

For those developers that are willing ...


  • 8
    As for that link about changing to 3 queries... I know and respect Baron, Vadim, and Peter, but I disagree with this misleading suggestion. Most of the arguments in favor of the split up are so rare as to be not worth mentioning. Stick with a single query with JOINs, then let's work on improving it.
    – Rick James
    Commented Oct 29, 2016 at 19:31
  • 2
    @RickJames I agree with the spirit of your comment. Over the years, I have seen join decomposition work for some and fail for others. Even with the proper SQL skillset, it could work against you if the join decomposition isn't done right. At my current employer, many depts love scaling up and out, especially when legacy code is involved and deep pockets are available. With those who have caviar taste but egg salad budgets, join decomposition could be worth the risk but must be done right. Commented Oct 29, 2016 at 20:11
  • I'd love to see how this works in an Oracle environment if I had the rights and time. Commented Sep 7, 2017 at 14:34
  • One other way it can be faster is that if you're doing ordering, it will be less calculations overall to order smaller lists than to order one large list. Commented Oct 30, 2018 at 17:46
  • 1
    <3 As a relatively young architect, I had never really done the research but knew in my bones that this was the right way. I began to preach it to my devs. When I was challenged, we began to break out the scenarios. In every scenario (performance/caching, maintenance, scalability) I was able to debunk the fears. This post helped immensely in corroborating my arguments. Without this approach, dbs become swamped with context-specific objects and indexes, an overloaded cache server or consumers are bogged down by more data than is necessary. Thank you!
    – alan
    Commented Aug 7, 2021 at 1:55

In Postgres (and probably any RDBMS to a similar extent, MySQL to a lesser extent), fewer queries are almost always much faster.

The overhead of parsing and planning multiple queries is already more than any possible gain in most cases.

Not to speak of additional work to be done in the client, combining the results, which is typically much slower at that. An RDBMS specializes in that kind of task and operations are based on original data types. No casting to text and back for intermediate results or transforming to native types of the client, which may even lead to less correct (or incorrect!) results. Think of floating point numbers ...

You also transfer more data between DB server and client. This may be negligible for a hand full of values, or make a huge difference.

If multiple queries mean multiple round trips to the database server, you also collect multiple times the network latency and transaction overhead, possibly even connection overhead. Big, big loss.

Depending on your setup, network latency alone may take longer than all the rest by orders of magnitude.

Related question on SO:

There may be a turning point for very big, long running queries because transactions collect locks on DB rows on the way. Very big queries may hold many locks for an extended period of time which may cause friction with concurrent queries.

  • 1
    Just out of curiosity, what do you consider very big?
    – Sablefoste
    Commented Jan 9, 2017 at 20:14
  • @Sablefoste: Very much depends on your access patterns. A critical point is where concurrent transactions start queuing up, waiting for locks to be released.Or if you accumulate enough locks to eat a substantial part of your resources. Or if your queries run long enough to interfere with autovacuum ... Commented Jan 10, 2017 at 4:26
  • 1
    But if we take a somewhat typical situation - a query that uses an outer join and returns lots of redundant data for "parent" table, which then has to be parsed and sorted out by the app (most probably, some ORM library) versus a small select that fetches all required IDs first and then another smaller select with IN() instead of outer join? Won't the second approach be more efficient (considering both DB and app consumed CPU and communications bandwidth)? Commented Sep 14, 2017 at 7:47
  • 1
    @JustAMartin: That sounds like the kind of query that's almost certainly faster when handled by the query planner of the RDBMS - assuming correct queries. Concerning returns lots of redundant data for "parent" table: Why would you return redundant data? Only return the data you need. Commented Jan 10, 2018 at 13:35
  • 2
    With outer join RDBMS returns data from the parent table duplicated for every joined child, which means some network & memory overhead, and then some additional parsing in ORM tool to throw the duplicate parent values away and keep only one parent with n children. So, with single query we save on efficient work of RDBMS query planner, less network (or local pipe) requests but lose on additional unneeded payload and shifting data around in ORM library. I guess, it's as always - measure before optimizing. Commented Jan 10, 2018 at 14:23

I don't know if this is possible in (most) sql versions I only really know transact SQL (Microsoft)

The performance loss is mostly given by first joining everything together than filtering in the end by a where clause.

How about integrating the where into the join ON statement and if you have multiple joins intelligently order them so the most filtering is done in the first join reducing the rows to be used in the subsequent joins.


JOIN tag_post ON tag.tag = 'mysql' AND tag_post.tag_id = tag.id 
JOIN post ON tag_post.post_id = post.id

In t-sql this can greatly enhance the speed of the query as most rows from tag_post are filtered out in the first join step (given you have many different tags).

Can anyone comment if this is possible in other sql dialects and if it is as performant as doing multiple queries or if multiple queries still hold a performance boost?

  • 2
    I very much doubt that this changes anything compared to putting the tag = 'mysql' condition into the WHERE clause. This will most certainly not enhance the speed "in T-SQL" (=SQL Server) or any other modern database with a decent optimizer. If this indeed runs faster in MySQL, then I would consider this as a major deficiency in their query optimizer.
    – user1822
    Commented Apr 29, 2020 at 12:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.