[Solved] Syntax performance of INNER JOIN

EverSQL Database Performance Knowledge Base

Syntax performance of INNER JOIN

Is the performance of both these examples the same?

Example 1:

SELECT t1.wanted_1, t2.wanted_2 
FROM table1 t1 
INNER JOIN table2 t2 ON t1.common_col = t2.common_col

Example 2:

SELECT wanted_1, wanted_2 
FROM 
    (SELECT wanted_1, common_col FROM table1) 
INNER JOIN 
    (SELECT wanted_2, common_col FROM table_2) USING(common_col)

I am using example #2 at the moment since I am joining 15+ tables, each table with many unnecessary columns and many rows (1 million+)

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `table2` ADD INDEX `table2_idx_common_col` (`common_col`);
The optimized query:
SELECT
        t1.wanted_1,
        t2.wanted_2 
    FROM
        table1 t1 
    INNER JOIN
        table2 t2 
            ON t1.common_col = t2.common_col

Related Articles



* original question posted on StackOverflow here.