[Solved] MySQL Join Query is very Slow

EverSQL Database Performance Knowledge Base

MySQL Join Query is very Slow

Database type:

I have a MySQL join query that I am executing and it never finishes: 

SELECT t1.`id` FROM `person` as t1 
      JOIN `temp_table` as t2 
      on t1.`date` = t2.`date` 
      and t1.`name` = t2.`name` 
      and t1.`country_id`= t2.`country_id`

The person table and temp_table have the exact same columns.

When I run the query with explain I see the following results: 

1   SIMPLE  t1  index   test    test    777 NULL    99560   Using where; Using index
1   SIMPLE  t2  ref test    test    777 development.t1.date,development.t1.name,development.t1.country_id   1   Using index

I created indexes for both tables with the following statement: 

ALTER TABLE `person` ADD INDEX `test` (`date`,`name`,`country_id`)
ALTER TABLE `temp_table` ADD INDEX `test` (`date`,`name`,`country_id`)

Each table has the same 100,000 rows or so in them, and thus the join should return 100,000 rows. I am assuming this query is so slow because of the number of rows being scanned on the t1 table. I'm not sure why that is the case though if I have applied indexes. Any help would be appreciated.

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 `temp_table` ADD INDEX `temp_table_idx_date_name_country_id` (`date`,`name`,`country_id`);
The optimized query:
SELECT
        t1.`id` 
    FROM
        `person` AS t1 
    JOIN
        `temp_table` AS t2 
            ON t1.`date` = t2.`date` 
            AND t1.`name` = t2.`name` 
            AND t1.`country_id` = t2.`country_id`

Related Articles



* original question posted on StackOverflow here.