Suppose that I have these tables,and example data (the real table I am working on has more columns)

table `users`

id name zip
1 John 201

table `region`

zip city state country
22 a b c

table `purchase`

id user_id price
2 3 2

Should I use subquery,cte or join when those tables have billions of records (so efficiency matters) and

  1. query which city,state,country does the user id=1 lives (Yield 1 row)
  2. query the name, city, price of a purchase order by price desc limit 100 (Get details on top 100 purchases).
  3. query the number of the purchases, sum of price of users whose name begins with "Dr"
  4. query the average spending(sum of price) of users in each city in country "c"
  • 3
    There is no general recommendations except "Investigate execution plan firstly".
    – Akina
    Jun 2 at 9:09
  • I am using mariadb, and thanks @Akina, that's what I needed, but can you tell me more about the specific question? I searched the web about execution plan and still don't know the difference.
    – Sam
    Jun 2 at 9:25
  • And I heard that mysql is notorious when it comes to optimization of complicated where clauses
    – Sam
    Jun 2 at 9:26
  • 1
    dev.mysql.com/doc/refman/8.0/en/optimization.html => Understanding the Query Execution Plan
    – Akina
    Jun 2 at 9:31
  • 1
    FYI, there's no correlation for number of rows vs which methodology to use: subquery, CTE, or JOIN. I can query trillions of rows in milliseconds, on modest hardware, using any of those methodologies. It just depends on the individual and specific query you're trying to optimize, which as Akina mentioned, should start by analyzing the execution plan (assuming the query is already not fast enough).
    – J.D.
    Jun 2 at 11:50

1 Answer 1


I would say always aim for join.

If this is not possible consider subquery or CTE.

Here is why

Query order processing is: FROM, WHERE, GROUP BY, SELECT... Sooner you narrow result is better since next processed clause will have less to do.

Using join like this: SELECT ... FROM users u JOIN purchase p ON u.id=p.id ... will narrow result in the first processed clause - FROM clause. It will return only records satisfying join condition. In this example only users with at least one purchase.

In WHERE clause you narrow further. E.g. WHERE users.name like 'Dr%' Or WHERE region.country='c'

Next there are GROUP BY, ORDER BY and FETCH. E.g. ... ORDER BY purchase.price FETCH NEXT 100 ROWS ONLY

Optimizer is sophisticated piece of software. Sometimes it can rewrite a query from CTE or subquery into join. I do not know if MySQL's is smart enough though.

  • 2
    The vast majority of RDBMSs out there it doesn't make any difference. A predicate can normally be pushed down in order to filter sooner. Jun 2 at 15:13
  • 2
    I'm addition to what Charlieface said, the ordering you mentioned is the logical ordering of query processing, but not necessarily the execution order.
    – J.D.
    Jun 3 at 1:22

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