Mysql query optimization with left joins

how to optimize sql query with multiple left joins
mysql left join
mysql join optimization
left join taking long time in sql server
left join alternative
mysql explain
mysql query optimizer
mysql join performance

I have the following query and this query takes 1.141 sec to execute. This query has bunch of joins. Is there a way to optimize the query ? Any kind of help is appreciated.

SELECT `cou`.`id` AS `country_id`,
                `a`.`id` AS `area_id`,
                `y`.`id` AS `year_id`,
                `su`.`id` AS `subject_id`,
                `co1`.`name` AS `course_name`,
                `ca1`.`id` AS `root_category_id`,
                `ca1`.`name` AS `root_category_name`,
                `ca4`.`id` AS `chapter_id`,
                `ca4`.`name` AS `chapter_name`,
                `ca4`.`no_of_assets` AS `no_of_assets`,
                `ca4`.`active_status` AS `status`,
                0 AS `READ_IT`,
                0 AS `WATCH_IT`,
                0 AS `PLAY_IT`,
                0 AS `PROVE_IT`,
                count(DISTINCT `pa`.`id`) AS `APROVE_IT`,
                if((count(`pa`.`id`) > 0),'True', 'False') AS `sections_with_content`,
                count(`pa`.`id`) AS `content_count`,
                `pa`.`status` AS `content_flag`
         FROM (((((((((((((((((`edu_db`.`category_relation_xref` `crx1`
                               JOIN `edu_db`.`category` `ca1` on((`crx1`.`parent_id` = `ca1`.`id`)))
                              LEFT JOIN `edu_db`.`course` `co1` on((`ca1`.`course_id` = `co1`.`id`)))
                             JOIN `edu_db`.`category_relation_xref` `crx2` on((`crx1`.`child_id` = `crx2`.`parent_id`)))
                            JOIN `edu_db`.`category` `ca2` on((`crx2`.`parent_id` = `ca2`.`id`)))
                           JOIN `edu_db`.`category` `ca3` on((`crx2`.`child_id` = `ca3`.`id`)))
                          JOIN `edu_db`.`category_relation_xref` `crx3` on((`crx2`.`child_id` = `crx3`.`parent_id`)))
                         JOIN `edu_db`.`category` `ca4` on((`crx3`.`child_id` = `ca4`.`id`)))
                        LEFT JOIN `edu_db`.`category_relation_xref` `crx4` on((`crx3`.`child_id` = `crx4`.`parent_id`)))
                       LEFT JOIN `edu_db`.`category` `ca5` on((`crx4`.`child_id` = `ca5`.`id`)))
                      JOIN `edu_db`.`course` `co2` on((`ca4`.`course_id` = `co2`.`id`)))
                     JOIN `edu_db`.`curriculum` `cu` on((`co2`.`curriculum_id` = `cu`.`id`)))
                    JOIN `edu_db`.`year` `y` on((`cu`.`year_id` = `y`.`id`)))
                   JOIN `edu_db`.`subject` `su` on((`su`.`id` = `cu`.`subject_id`)))
                  JOIN `edu_db`.`area` `a` on((`y`.`area_id` = `a`.`id`)))
                 JOIN `edu_db`.`country` `cou` on((`a`.`country_id` = `cou`.`id`)))
                LEFT JOIN `edu_db`.`qbnk_category_published_assessment_xref` `qcpa` on((`ca4`.`id` = `qcpa`.`category_id`)))
               LEFT JOIN `edu_db`.`qbnk_published_assessment` `pa` on((`qcpa`.`published_assessment_id` = `pa`.`id`)))
         WHERE ((`pa`.`status` <> 'non_active')
                AND (`qcpa`.`status` <> 'deleted'))
         GROUP BY `ca4`.`id`

This is the output of the explain command. In here there is a select type that using filesort which means that query does not use an index. Is there a way to optimize this query with indexes?

 +------+---------------+---------+--------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------+--------------------------------+--------+------------+-------------------------------------------------------------+
| "id" | "select_type" | "table" | "partitions" |  "type"  |                                                                                      "possible_keys"                                                                                      |              "key"               | "key_len" |             "ref"              | "rows" | "filtered" |                           "Extra"                           |
+------+---------------+---------+--------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------+--------------------------------+--------+------------+-------------------------------------------------------------+
| "1"  | "SIMPLE"      | "pa"    | \N           | "index"  | "PRIMARY,status"                                                                                                                                                                          | "status"                         | "2"       | \N                             | "7714" | "50.00"    | "Using where; Using index; Using temporary; Using filesort" |
| "1"  | "SIMPLE"      | "qcpa"  | \N           | "ref"    | "PRIMARY,FK_qbnk_cat_id_pub_ass_tbl_to_category_tbl_id,cat_pub_status"                                                                                                                    | "PRIMARY"                        | "4"       | "edu_db.pa.id"             | "6"    | "50.00"    | "Using where"                                               |
| "1"  | "SIMPLE"      | "ca4"   | \N           | "eq_ref" | "PRIMARY,FK_curriculum_item_id_category_tbl_to_id_curriculum_item_tbl,FK_curriculum_id_category_tbl_to_id_curriculum_tbl,name,Fk_category_tbl_course_id_to_course_tbl_id,Index 6,Index 7" | "PRIMARY"                        | "4"       | "edu_db.qcpa.category_id"  | "1"    | "100.00"   | "Using where"                                               |
| "1"  | "SIMPLE"      | "co2"   | \N           | "eq_ref" | "PRIMARY,FK_curriculum_id_to_id_curriculum_tbl"                                                                                                                                           | "PRIMARY"                        | "4"       | "edu_db.ca4.course_id"     | "1"    | "100.00"   | "Using where"                                               |
| "1"  | "SIMPLE"      | "cu"    | \N           | "eq_ref" | "PRIMARY,FK_subject_id_to_id_subject_tbl,FK_year_id_to_id_year_tble"                                                                                                                      | "PRIMARY"                        | "4"       | "edu_db.co2.curriculum_id" | "1"    | "100.00"   | "Using where"                                               |
| "1"  | "SIMPLE"      | "su"    | \N           | "eq_ref" | "PRIMARY"                                                                                                                                                                                 | "PRIMARY"                        | "4"       | "edu_db.cu.subject_id"     | "1"    | "100.00"   | "Using index"                                               |
| "1"  | "SIMPLE"      | "y"     | \N           | "eq_ref" | "PRIMARY,FK_year_tbl_area_id_to_id_area_tbl"                                                                                                                                              | "PRIMARY"                        | "4"       | "edu_db.cu.year_id"        | "1"    | "100.00"   | "Using where"                                               |
| "1"  | "SIMPLE"      | "a"     | \N           | "eq_ref" | "PRIMARY,FK_country_id_to_country_tbl"                                                                                                                                                    | "PRIMARY"                        | "4"       | "edu_db.y.area_id"         | "1"    | "100.00"   | \N                                                          |
| "1"  | "SIMPLE"      | "cou"   | \N           | "eq_ref" | "PRIMARY"                                                                                                                                                                                 | "PRIMARY"                        | "4"       | "edu_db.a.country_id"      | "1"    | "100.00"   | "Using index"                                               |
| "1"  | "SIMPLE"      | "crx3"  | \N           | "ref"    | "PRIMARY,FK_child_id_to_id_category_tbl"                                                                                                                                                  | "FK_child_id_to_id_category_tbl" | "4"       | "edu_db.qcpa.category_id"  | "1"    | "100.00"   | "Using index"                                               |
| "1"  | "SIMPLE"      | "ca3"   | \N           | "eq_ref" | "PRIMARY,Index 6,Index 7"                                                                                                                                                                 | "PRIMARY"                        | "4"       | "edu_db.crx3.parent_id"    | "1"    | "100.00"   | "Using index"                                               |
| "1"  | "SIMPLE"      | "crx2"  | \N           | "ref"    | "PRIMARY,FK_child_id_to_id_category_tbl"                                                                                                                                                  | "FK_child_id_to_id_category_tbl" | "4"       | "edu_db.crx3.parent_id"    | "1"    | "100.00"   | "Using index"                                               |
| "1"  | "SIMPLE"      | "ca2"   | \N           | "eq_ref" | "PRIMARY,Index 6,Index 7"                                                                                                                                                                 | "PRIMARY"                        | "4"       | "edu_db.crx2.parent_id"    | "1"    | "100.00"   | "Using index"                                               |
| "1"  | "SIMPLE"      | "crx1"  | \N           | "ref"    | "PRIMARY,FK_child_id_to_id_category_tbl"                                                                                                                                                  | "FK_child_id_to_id_category_tbl" | "4"       | "edu_db.crx2.parent_id"    | "1"    | "100.00"   | "Using index"                                               |
| "1"  | "SIMPLE"      | "ca1"   | \N           | "eq_ref" | "PRIMARY,Index 6,Index 7"                                                                                                                                                                 | "PRIMARY"                        | "4"       | "edu_db.crx1.parent_id"    | "1"    | "100.00"   | \N                                                          |
| "1"  | "SIMPLE"      | "co1"   | \N           | "eq_ref" | "PRIMARY"                                                                                                                                                                                 | "PRIMARY"                        | "4"       | "edu_db.ca1.course_id"     | "1"    | "100.00"   | \N                                                          |
| "1"  | "SIMPLE"      | "crx4"  | \N           | "ref"    | "PRIMARY"                                                                                                                                                                                 | "PRIMARY"                        | "4"       | "edu_db.qcpa.category_id"  | "4"    | "100.00"   | "Using index"                                               |
| "1"  | "SIMPLE"      | "ca5"   | \N           | "eq_ref" | "PRIMARY,Index 6,Index 7"                                                                                                                                                                 | "PRIMARY"                        | "4"       | "edu_db.crx4.child_id"     | "1"    | "100.00"   | "Using index"                                               |
+------+---------------+---------+--------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------+--------------------------------+--------+------------+-------------------------------------------------------------+

Following are the create code for category_relation_xref and qbnk_category_published_assessment_xref tables

 CREATE TABLE `category_relation_xref` (
    `parent_id` INT(11) NOT NULL,
    `child_id` INT(11) NOT NULL,
    `template_id` INT(11) NOT NULL DEFAULT '1',
    `possition_id` INT(11) NOT NULL DEFAULT '1',
    `comment` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf16_unicode_ci',
    `display_order` INT(11) NOT NULL DEFAULT '0',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `created_by` INT(11) NULL DEFAULT NULL,
    `updated_by` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`parent_id`, `child_id`),
    INDEX `FK_child_id_to_id_category_tbl` (`child_id`),
    INDEX `FK_cat_rel_xref_tbl_template_id_to_content_template_tbl` (`template_id`),
    INDEX `FK_cat_rel_xref_tbl_possition_id_to_content_template_tbl_id` (`possition_id`),
    CONSTRAINT `FK_cat_rel_xref_tbl_possition_id_to_content_template_tbl_id` FOREIGN KEY (`possition_id`) REFERENCES `content_possition` (`id`),
    CONSTRAINT `FK_cat_rel_xref_tbl_template_id_to_content_template_tbl` FOREIGN KEY (`template_id`) REFERENCES `content_template` (`id`),
    CONSTRAINT `category_relation_xref_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `category` (`id`),
    CONSTRAINT `category_relation_xref_ibfk_2` FOREIGN KEY (`child_id`) REFERENCES `category` (`id`)
)
COMMENT='store parent child relations'
COLLATE='utf16_unicode_ci'
ENGINE=InnoDB
;

CREATE TABLE `qbnk_category_published_assessment_xref` (
    `published_assessment_id` INT(11) NOT NULL,
    `category_id` INT(11) NOT NULL,
    `status` ENUM('active','deleted') NOT NULL DEFAULT 'active' COLLATE 'utf16_unicode_ci',
    `comment` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf16_unicode_ci',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `created_by` INT(11) NULL DEFAULT '0',
    `updated_by` INT(11) NULL DEFAULT '0',
    PRIMARY KEY (`published_assessment_id`, `category_id`),
    INDEX `FK_qbnk_cat_id_pub_ass_tbl_to_category_tbl_id` (`category_id`),
    INDEX `cat_pub_status` (`status`),
    CONSTRAINT `FK_qbnk_cat_id_pub_ass_tbl_to_category_tbl_id` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE,
    CONSTRAINT `FK_qbnk_cat_pub_ass_id_to_pub_ass_tbl_id` FOREIGN KEY (`published_assessment_id`) REFERENCES `qbnk_published_assessment` (`id`) ON DELETE CASCADE
)
COMMENT='Store category published assessments mappings'
COLLATE='utf16_unicode_ci'
ENGINE=InnoDB
;

After cleaning up the query, stripping unnecessary tic marks and parenthesis, I believe the following is much easier to follow and directly see the relationships between the tables. You had it well done with the joins of leftAlias.leftColumn = rightAlias.rightColumn.

Now that I can see things better, lets consider your tables. They MOSTLY appear to be lookup tables where you have an ID and a descriptive column you are returning. I would CREATE covering indexes on these tables so the join resolutions can be handled directly from the indexes instead of going to the raw data pages.

One ADDITIONAL consideration on time is to add the MySQL keyword "STRAIGHT_JOIN" which tells the engine to query the tables in the order I have them listed. Dont think for me. Each of your tables starts at the topmost level and gets all the lookups down-stream. You can also compare the time by removing the STRAIGHT_JOIN clause too.. On a system I worked on YEARS ago, it took a primary table with about 15M records and 20+ lookup tables from being hung after 12+ hours processing and reduced to a completed query running in just over an hour.

Original query cleaned up

SELECT STRAIGHT_JOIN
      cou.id AS country_id,
      a.id AS area_id,
      y.id AS year_id,
      su.id AS subject_id,
      co1.`name` AS course_name,
      ca1.id AS root_category_id,
      ca1.`name` AS root_category_name,
      ca4.id AS chapter_id,
      ca4.`name` AS chapter_name,
      ca4.no_of_assets AS no_of_assets,
      ca4.active_status AS `status`,
      0 AS READ_IT,
      0 AS WATCH_IT,
      0 AS PLAY_IT,
      0 AS PROVE_IT,
      count(DISTINCT pa.id) AS APROVE_IT,
      if((count(pa.id) > 0),'True', 'False') AS sections_with_content,
      count(pa.id) AS content_count,
      pa.`status` AS content_flag
   FROM 
      edu_db.category_relation_xref crx1
         JOIN edu_db.category ca1
            ON crx1.parent_id = ca1.id
            LEFT JOIN edu_db.course co1
               ON ca1.course_id = co1.id

         JOIN edu_db.category_relation_xref crx2 
            ON crx1.child_id = crx2.parent_id

            JOIN edu_db.category ca2
               ON crx2.parent_id = ca2.id

            JOIN edu_db.category ca3
               ON crx2.child_id = ca3.id

            JOIN edu_db.category_relation_xref crx3 
               ON crx2.child_id = crx3.parent_id
               JOIN edu_db.category ca4 
                  ON crx3.child_id = ca4.id

                  JOIN edu_db.course co2
                     ON ca4.course_id = co2.id
                     JOIN edu_db.curriculum cu
                        ON co2.curriculum_id = cu.id
                        JOIN edu_db.`year` y 
                           ON cu.year_id = y.id
                           JOIN edu_db.area a
                              ON y.area_id = a.id
                              JOIN edu_db.country cou 
                                 ON a.country_id = cou.id
                        JOIN edu_db.subject su
                           ON cu.subject_id = su.id

                  LEFT JOIN edu_db.qbnk_category_published_assessment_xref qcpa
                     ON ca4.id = qcpa.category_id
                     LEFT JOIN edu_db.qbnk_published_assessment pa
                        ON qcpa.published_assessment_id = pa.id

               LEFT JOIN edu_db.category_relation_xref crx4
                  ON crx3.child_id = crx4.parent_id
                  LEFT JOIN edu_db.category ca5 
                     ON crx4.child_id = ca5.id
   WHERE 
          pa.`status` <> 'non_active'
      AND qcpa.`status` <> 'deleted'
   GROUP BY 
      ca4.id

Another item... You have some tables that are LEFT joined and not even being used in the query and can be completely removed. Explicitly the "LEFT JOIN edu_db.category ca5". You are not pulling any values from the CA5 alias, and a left-join means you didn't care about it anyhow. Similarly for the "LEFT JOIN edu_db.category_relation_xref crx4"

SELECT STRAIGHT_JOIN
      a.country_id,
      a.id AS area_id,
      y.id AS year_id,
      cu.subject_id,
      co1.`name` AS course_name,
      ca1.id AS root_category_id,
      ca1.`name` AS root_category_name,
      ca4.id AS chapter_id,
      ca4.`name` AS chapter_name,
      ca4.no_of_assets AS no_of_assets,
      ca4.active_status AS `status`,
      0 AS READ_IT,
      0 AS WATCH_IT,
      0 AS PLAY_IT,
      0 AS PROVE_IT,
      count(DISTINCT pa.id) AS APROVE_IT,
      if((count(pa.id) > 0),'True', 'False') AS sections_with_content,
      count(pa.id) AS content_count,
      pa.`status` AS content_flag
   FROM 
      edu_db.category_relation_xref crx1
         JOIN edu_db.category ca1
            ON crx1.parent_id = ca1.id
            LEFT JOIN edu_db.course co1
               ON ca1.course_id = co1.id

         JOIN edu_db.category_relation_xref crx2 
            ON crx1.child_id = crx2.parent_id
            JOIN edu_db.category_relation_xref crx3 
               ON crx2.child_id = crx3.parent_id
               JOIN edu_db.category ca4 
                  ON crx3.child_id = ca4.id
                  JOIN edu_db.course co2
                     ON ca4.course_id = co2.id
                     JOIN edu_db.curriculum cu
                        ON co2.curriculum_id = cu.id
                        JOIN edu_db.`year` y 
                           ON cu.year_id = y.id
                           JOIN edu_db.area a
                              ON y.area_id = a.id

                  JOIN edu_db.qbnk_category_published_assessment_xref qcpa
                     ON ca4.id = qcpa.category_id
                     AND qcpa.`status` <> 'deleted'

                     JOIN edu_db.qbnk_published_assessment pa
                        ON qcpa.published_assessment_id = pa.id
                        AND pa.`status` <> 'non_active'
   GROUP BY 
      ca4.id

Your WHERE clauses associated with the "pa" and "qcpa" cancel-out the LEFT JOIN portion as the where turns it into a WHERE clause. So I removed the "LEFT" component AND moved the where clause portion directly to that join component.

You are pulling the "subject" table (alias su), but only grabbing the su.id. Since you have the subject ID from the "cu" alias, you can just use the "cu.subject_id" instead and remove yet another table from the query -- UNLESS you plan on getting other descriptions from the subject table. This is probably the same for your country, area, year joins as well. If you already have an ID from a prior table, use that and drop what is not needed...

Not using the "ca2" or "ca3" aliases for any underlying extra details, descriptions, get rid of it.

So my index suggestions for each table would include the following. These will be more COVERING indexes. These should not be individual indexes on the same table, such as Tbl1 index on ID, Tbl1 index on Description, but Tbl1 index ON (id, description) as a single index.

table                                   index
qbnk_published_assessment               ( id, `status` ) 
qbnk_category_published_assessment_xref ( category_id, `status`, published_assessment_id )
area                                    ( id )
`year`                                  ( id, area_id ) 
curriculum                              ( id, year_id )
course                                  ( id, curriculum_id )
category                                ( id, course_id )
category_relation_xref                  ( parent_id, child_id )

8.2.1.9 Outer Join Optimization, MySQL implements an A LEFT JOIN B join_specification as follows: Table B is set Now the optimizer can rewrite the query as an inner join, like this: SELECT​  In the first query, the nested join is formed with a left join operation. In the second query, it is formed with an inner join operation. In the first query, the parentheses can be omitted: The grammatical structure of the join expression will dictate the same order of execution for join operations.


Since the EXPLAIN starts with the pa and ocpa tables, we can see that the optimizer is trying to filter the results first, before doing anything else. That is smart, but another smart(er?) route would to be to perform the GROUP BY first, and then filter the results.

New Index

With that in mind, let's try to help the optimizer do that to see if we get any improvement:

ALTER TABLE qbnk_category_published_assessment_xref
ADD INDEX so52589130_qcpa (`status`,`category_id`,`published_assessment_id`)
Explanation

The above index allows starting at the category table for the GROUP BY, then use the WHERE clause on the status column with the category_id to match the two leftmost columns of that index. That gives immediate access to the published_assessment_id column without having to make a secondary lookup.

The reason it isn't trying to do this in the first place is that the indexes you have on the qbnk_category_published_assessment_xref don't allow that without a secondary lookup.

cat_pub_status allows using the status, but then it gets the published_assessment_id before the category_id, which doesn't allow it to use the category_id to quickly find just the record it wants.

FK_qbnk_cat_id_pub_ass_tbl_to_category_tbl_id allows using the category_id first, but then still has to go back to the clustered index to get the status.

Our new index allows just one trip to this table, which should help save time.

Possible additional savings

The fact that the two WHERE clauses are <> may reduce the performance a little, in comparison to if they were = values. If there are only two status values for each status column, I would swap those to use = instead. That is not overly important though, if there's more than just 2 values.

Instructions

So, give the above index a try. Run it twice, discard the first timing result as the buffer cache will be loading on that call. Reply with the second time, AND whether or not the EXPLAIN plan changed. If the EXPLAIN plan changed, and the timing is still not fast enough, add the new EXPLAIN plan to your question and I'll take another look. (I may need more table definitions at that time too, but we'll wait and see how this works out first.)

8.2.1.7 LEFT JOIN and RIGHT JOIN Optimization, 1.7 LEFT JOIN and RIGHT JOIN Optimization. MySQL implements an A LEFT JOIN B join_condition as follows: Table B is set to depend on table A and all tables on which A depends. Table A is set to depend on all tables (except B ) that are used in the LEFT JOIN condition. Query Optimization (WHERE, GROUP BY, LEFT JOINs) Ask Question MySQL query optimization JOIN. 1220. Select first row in each GROUP BY group? 1018. Group by in LINQ. 1.


I call this "over normalization".

There is no need for an extra table for country -- there are perfectly good CHAR(2) CHARACTER SET ascii values for all the countries. Note that INT is 4 bytes, so this change saves space!

That is a datatype YEAR; use it. If you have a full date or datetime, then use DATE or DATETIME and pick the YEAR out of it in the SELECT. YEAR is smaller than INT.

Read about ENUM as a possible datatype for columns that have a small number of possible values.

etc.

Understanding MySQL LEFT JOIN Clause By Examples, This query took under 1 sec. Detailed answer: Step 1. Creating table with fulltext indexes. InnoDb supports fulltext indexes from MySQL 5.7 so if you use 5.5 or 5.6​  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: mysql> SELECT * FROM tag-> JOIN tag_post ON tag_post.tag_id=tag.id-> JOIN post ON tag_post.post_id=post.id-> WHERE tag.tag='mysql';


How to optimize very slow SELECT with LEFT JOINs over big tables , This query optimization approach works well when one of the two joined tables has a relatively small number of rows. Here's the new query: In our extended syntax, the parentheses in (t2, t3) of the second query are required, although theoretically the query could be parsed without them: We still would have unambiguous syntactical structure for the query because LEFT JOIN and ON play the role of the left and right delimiters for the expression (t2,t3).


SQL subquery performance was 260x faster than a left join, A JOIN in MySQL is a tool that allows developers to combine one or more tables in a query. There are a few flavors of JOIN, but we'll be focusing on LEFT JOIN  Outer joins include LEFT JOIN and RIGHT JOIN. MySQL implements an A LEFT JOIN B join_specification as follows: Table B is set to depend on table A and all tables on which A depends. Table A is set to depend on all tables (except B) that are used in the LEFT JOIN condition.


Optimizing MySQL Queries with JOINs, In some cases it is not possible to use an index to optimize a JOIN. As a result these kind of queries can be very slow; however in MySQL 5.6 and is the LEFT JOIN on performance_schema.file_summary_by_instance. select * from t1 left join t2 on t2.a=t1.a inner join t3 on t3.b=t1.b where t3.c > 0 For the original query, the optimizer evaluates only plans compatible with the single table-access order T1,T2,T3 .