1

We have a query that we don't like, since it ends up doing a full table scan.

EXPLAIN SELECT
    count(*) AS c, location.name
FROM
    study
LEFT JOIN
    location ON study.location_id = location.id
WHERE
    study.status = 'unread' AND
    study.deleted = '' AND
    study.id IN (
        SELECT DISTINCT
            study_id
        FROM
            studyassign
        WHERE (
            object_type = 'Group' AND
            object_id IN (
                SELECT
                    grp_id
                FROM
                    grpuser
                WHERE
                    user_id = '12')
            ) OR ( 
            object_type = 'User' AND
            object_id = '12')
        )
GROUP BY
    location.id;

This gives

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: study
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2015879
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: location
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: echo.study.location_id
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: studyassign
         type: index_subquery
possible_keys: studyassign_study_id_object_type_object_id_idx
          key: studyassign_study_id_object_type_object_id_idx
      key_len: 4
          ref: func
         rows: 1
        Extra: Using index; Using where
*************************** 4. row ***************************
           id: 3
  select_type: DEPENDENT SUBQUERY
        table: grpuser
         type: unique_subquery
possible_keys: grpuser_grp_id_user_id_idx,user_id
          key: grpuser_grp_id_user_id_idx
      key_len: 8
          ref: func,const
         rows: 1
        Extra: Using index; Using where
4 rows in set (0.07 sec)

I went about making changes methodically, starting with the innermost sub queries. I was making progress.

But then I wanted to see what would happen if simply changed how the join was made.

EXPLAIN SELECT
    count(*) AS c, location.name
FROM
    study, location
WHERE
    study.location_id = location.id AND
    study.status = 'unread' AND
    study.deleted = '' AND
    study.id IN (
        SELECT DISTINCT
            study_id
        FROM
            studyassign
        WHERE (
            object_type = 'Group' AND
            object_id IN (
                SELECT
                    grp_id
                FROM
                    grpuser
                WHERE
                    user_id = '12')
            ) OR ( 
            object_type = 'User' AND
            object_id = '12')
        )
GROUP BY
    location.id;

The results from EXPLAIN are very different ...

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: location
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3667
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: study
         type: ref
possible_keys: location_id
          key: location_id
      key_len: 5
          ref: echo.location.id
         rows: 233
        Extra: Using where
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: studyassign
         type: index_subquery
possible_keys: studyassign_study_id_object_type_object_id_idx
          key: studyassign_study_id_object_type_object_id_idx
      key_len: 4
          ref: func
         rows: 1
        Extra: Using index; Using where
*************************** 4. row ***************************
           id: 3
  select_type: DEPENDENT SUBQUERY
        table: grpuser
         type: unique_subquery
possible_keys: grpuser_grp_id_user_id_idx,user_id
          key: grpuser_grp_id_user_id_idx
      key_len: 8
          ref: func,const
         rows: 1
        Extra: Using index; Using where
4 rows in set (0.00 sec)

What am I missing?

3
  • 1
    First query has LEFT JOIN, the second one - INNER JOIN. They are not the same... – a1ex07 Aug 19 '14 at 20:28
  • To enhance what @a1ex07 said. The two joins are equivalent only if there is a foreign key constarint between the 2 tables (study -> location). But even in that case, the optimizer is probably not that smart to know they are equivalent. – ypercubeᵀᴹ Aug 20 '14 at 6:17
  • @ypercube ... can you help me understand how that foreign key constraint (there is one) makes the queries identical? – Alan C. Aug 20 '14 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.