MySQL - Optimize Query To Remove Using Temporary and Using filesort

1
votes

Query

SELECT SQL_NO_CACHE
 `Table1`.`recordID`
FROM
  `Table1`
  LEFT JOIN `Table3` ON `Table3`.`table1RecordID`=`Table1`.`recordID`
WHERE
  (`Table3`.`status` = '3' OR `Table3`.`status` = '4')  AND
  (`Table1`.`groupName` = 'Sample Name')
GROUP BY `Table3`.`recordID` ASC, `Table1`.`recordID` ASC;

Explain

+----+-------------+--------+--------+------------------------------+---------+---------+------------------------------+-------+----------------------------------------------+
| id | select_type | table  |  type  |        possible_keys         |   key   | key_len |             ref              | rows  |                    Extra                     |
+----+-------------+--------+--------+------------------------------+---------+---------+------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | Table3 | ALL    | fk_packageID,regStatus,pkgID | NULL    | NULL    | NULL                         | 11322 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | Table1 | eq_ref | PRIMARY,groupName            | PRIMARY | 4       | testDb.Table3.table1RecordID |     1 | Using where                                  |
+----+-------------+--------+--------+------------------------------+---------+---------+------------------------------+-------+----------------------------------------------+

If I remove the second part of the GROUP BY, "Table1.recordID ASC" but data isn't correct when I do that. Why is it doing this and how can I fix it and still group down by Table1 in addition to the Table3 first.

Thanks in advanced!

Update 1/24/14

I had time to take the full query and pull the tables to a generic form to post without client data. I was able to add schema to sqlfiddle but without the data I'm using results can be different and I was even unable to put 100 rows pre-table (7 total) into sqlfiddle due to limitations of characters. So instead I've done a dump of the tables and I'm sharing it over dropbox.

Dropbox

https://www.dropbox.com/s/9fgu626996utpar/stackoverflow-21291707_test_db_schema_and_data.sql

Query

SELECT
  `t1`.`name` AS `Object1.Name`,
  GROUP_CONCAT(DISTINCT
  IF(`t5`.`questionID`=68,
    IF(`t6`.`writeInRequired` = 1,
      CONCAT(
        `t6`.`value`,
        ':', `t5`.`writeInResponse`
      ),
      `t6`.`value`
    ),
    NULL
  ) SEPARATOR ', ') AS `Object3.Response_68`,
  GROUP_CONCAT(DISTINCT
  IF(`t5`.`questionID`=67,
    IF(`t6`.`writeInRequired` = 1,
      CONCAT(
        `t6`.`value`,
        ':', `t5`.`writeInResponse`
      ),
      `t6`.`value`
    ),
    NULL
  ) SEPARATOR ', ') AS `Object3.Response_67`,
  GROUP_CONCAT(DISTINCT
  IF(`t5`.`questionID`=66,
    IF(`t6`.`writeInRequired` = 1,
      CONCAT(
        `t6`.`value`,
        ':', `t5`.`writeInResponse`
      ),
      `t6`.`value`
    ),
    NULL
  ) SEPARATOR ', ') AS `Object3.Response_66`,
  `t7`.`firstName` AS `Object8.FirstName`,
  `t7`.`lastName` AS `Object8.LastName`,
  `t7`.`email` AS `Object8.Email`,
  `t1`.`recordID` AS `Object1.PackageID`,
  `t3`.`recordID` AS `Object5.RegistrationID`
FROM
  `Table1` t1
  LEFT JOIN `Table2` t2 ON `t1`.`recordID`=`t2`.`table1RecordID`
  LEFT JOIN `Table3` t3 ON `t3`.`table1RecordID`=`t1`.`recordID`
  LEFT JOIN `Table4` t4 ON `t4`.`table3RecordID`=`t3`.`recordID` AND `t4`.`type` = 1
  LEFT JOIN `Table5` t5 ON `t5`.`objectID`=`t3`.`recordID` AND `t5`.`objectType`='Type2'
  LEFT JOIN `Table6` t6 ON `t6`.`recordID`=`t5`.`table6RecordID`
  JOIN `Table7` t7 ON `t7`.`recordID`=`t4`.`table7RecordID`
WHERE
  `t3`.`status` IN ('3','4')
GROUP BY
  `Object5.RegistrationID` ASC,
  `Object1.PackageID` ASC

EXPLAIN EXTENDED (/G)

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t7
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11627
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t4
         type: ref
possible_keys: idx_table7RecordID,idx_table3RecordID
          key: idx_table7RecordID
      key_len: 5
          ref: testDb.t7.recordID
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
         type: eq_ref
possible_keys: PRIMARY,table1RecordID_status,idx_status,idx_table1RecordID
          key: PRIMARY
      key_len: 4
          ref: testDb.t4.table3RecordID
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: t5
         type: ref
possible_keys: compositeIDs
          key: compositeIDs
      key_len: 773
          ref: const,testDb.t3.recordID
         rows: 5
     filtered: 100.00
        Extra:
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: t6
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: testDb.t5.table6RecordID
         rows: 1
     filtered: 100.00
        Extra:
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: testDb.t3.table1RecordID
         rows: 1
     filtered: 100.00
        Extra:
*************************** 7. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ref
possible_keys: idx_table1RecordID
          key: idx_table1RecordID
      key_len: 5
          ref: testDb.t1.recordID
         rows: 85
     filtered: 100.00
        Extra: Using index
7 rows in set, 1 warning (0.13 sec)

EXPLAIN EXTENDED (ASCII TABLE)

+----+-------------+-------+--------+-------------------------------------------------------------+--------------------+---------+--------------------------+-------+----------+---------------------------------+
| id | select_type | table |  type  |                        possible_keys                        |        key         | key_len |           ref            | rows  | filtered |              Extra              |
+----+-------------+-------+--------+-------------------------------------------------------------+--------------------+---------+--------------------------+-------+----------+---------------------------------+
|  1 | SIMPLE      | t7    | ALL    | PRIMARY                                                     | NULL               | NULL    | NULL                     | 11627 | 100.00   | Using temporary; Using filesort |
|  1 | SIMPLE      | t4    | ref    | idx_table7RecordID,idx_table3RecordID                       | idx_table7RecordID | 5       | testDb.t7.recordID       |     1 | 100.00   | Using where                     |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY,table1RecordID_status,idx_status,idx_table1RecordID | PRIMARY            | 4       | testDb.t4.table3RecordID |     1 | 100.00   | Using where                     |
|  1 | SIMPLE      | t5    | ref    | compositeIDs                                                | compositeIDs       | 773     | const,testDb.t3.recordID |     5 | 100.00   |                                 |
|  1 | SIMPLE      | t6    | eq_ref | PRIMARY                                                     | PRIMARY            | 4       | testDb.t5.table6RecordID |     1 | 100.00   |                                 |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY                                                     | PRIMARY            | 4       | testDb.t3.table1RecordID |     1 | 100.00   |                                 |
|  1 | SIMPLE      | t2    | ref    | idx_table1RecordID                                          | idx_table1RecordID | 5       | testDb.t1.recordID       |    85 | 100.00   | Using index                     |
+----+-------------+-------+--------+-------------------------------------------------------------+--------------------+---------+--------------------------+-------+----------+---------------------------------+

Thanks again in advanced!

For @Strawberry

Removed select clauses and just select the recordID of the first table. This query still produces the same explain from above.

SELECT
  `t1`.`recordID`
FROM
  `Table1` t1
  LEFT JOIN `Table2` t2 ON `t1`.`recordID`=`t2`.`table1RecordID`
  LEFT JOIN `Table3` t3 ON `t3`.`table1RecordID`=`t1`.`recordID`
  LEFT JOIN `Table4` t4 ON `t4`.`table3RecordID`=`t3`.`recordID` AND `t4`.`type` = 1
  LEFT JOIN `Table5` t5 ON `t5`.`objectID`=`t3`.`recordID` AND `t5`.`objectType`='Type2'
  LEFT JOIN `Table6` t6 ON `t6`.`recordID`=`t5`.`table6RecordID`
  JOIN `Table7` t7 ON `t7`.`recordID`=`t4`.`table7RecordID`
WHERE
  `t3`.`status` IN ('3','4')
GROUP BY
  `t3`.`recordID` ASC,
  `t1`.`recordID` ASC;
1
mysqlperformanceoptimizationquery-optimizationfilesort
Why do you have a GROUP BY at all? Also, this is rendered as an INNER JOIN because of the WHERE condition on the joined table – Strawberry
I think you should start again, with proper DDLs (and/or an sqlfiddle) TOGETHER WITH THE DESIRED RESULT SET. – Strawberry
@Strawberry I'll see what I can do. The data behind it is too large for sqlfiddle sadly. – MasterEthan
Er, so provide less data!?! Also, fwiw, I like to do explains with \G rather than ; – Strawberry
@Strawberry I tried to reduce data but sqlfiddle with the number of tables wouldn't even accept 50 rows per-table. So instead I've updated the question with a full query and a SQL file on dropbox. – MasterEthan

1 Answers

0
votes

Some comments about your query:

  1. The Left outer join is unnecessary. You are undoing the outer join with the where clause.
  2. There is no need to group by both fields, Table1.recordID is sufficient.
  3. in is more readable than a bunch of comparisons with or.

Here is the resulting query:

SELECT SQL_NO_CACHE t1.`recordID`
FROM `Table1` t1 JOIN
     `Table3` t3
     ON t3.`table1RecordID` = t1.`recordID`
WHERE t3.`status` in ('3', '4')  AND
      t1.`groupName` = 'Sample Name'
GROUP BY t1.`recordID` ASC;

Does this perform better?

If not, let's think about what the query is doing. It is trying to filter out records from t1 based on things in t3. Assuming that table1.RecordId is unique, then this would work:

SELECT SQL_NO_CACHE t1.`recordID`
FROM `Table1` t1 
WHERE t1.`groupName` = 'Sample Name' and
      exists (select 1
              from Table3 t3
              where t3.`status` in ('3', '4') and
                    t1.RecordId = t3.RecordId
             );

For best performance, you should have an index on Table3(RecordId, status).