Why isn't MySQL using my indexes when running my query?

Question

I’m using MySQL 5.5.37. It is not an option to upgrade at this time. I have this table

CREATE TABLE `my_classroom` (
  `ID` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `CLASSROOM_NAME` varchar(100) COLLATE utf8_bin NOT NULL,
  `ACCESS_CODE_ID` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `TEACHER_ACCESS_CODE_ID` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UK_my_classroom` (`ACCESS_CODE_ID`),
  UNIQUE KEY `UK2_my_classroom` (`TEACHER_ACCESS_CODE_ID`),
  KEY `FK2_my_classroom` (`CLASSROOM_SCHEDULE_ID`),
  CONSTRAINT `FK3_my_classroom` FOREIGN KEY (`TEACHER_ACCESS_CODE_ID`) REFERENCES `my_reg_code` (`ID`) ON UPDATE NO ACTION,
  CONSTRAINT `FK_my_classroom` FOREIGN KEY (`ACCESS_CODE_ID`) REFERENCES `my_reg_code` (`ID`) ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Notice the indexes on the ACCESS_CODE_ID and TEACHER_ACCESS_CODE_ID columns. However, when this query is run (it is generated by Hibernate, which is why it looks a little funky), note the full table scan that is happening …

mysql> explain select davesclass0_.id as id1_7_, davesclass0_.ACCESS_CODE_ID as ACCESS_13_7_,
    davesclass0_.CLASSROOM_NAME as CLASSROO7_7_, davesclass0_.TEACHER_ACCESS_CODE_ID as TEACHER15_7_
from my_classroom davesclass0_
left outer join my_reg_code myregcode1_ on davesclass0_.ACCESS_CODE_ID=myregcode1_.ID
left outer join my_reg_code accesscode2_ on davesclass0_.TEACHER_ACCESS_CODE_ID=accesscode2_.ID
where myregcode1_.ACCESS_CODE='ABCDEF' or accesscode2_.ACCESS_CODE='ABCDEF';
+----+-------------+--------------+--------+---------------+---------+---------+-------------------------------------------+---------+-------------+
| id | select_type | table        | type   | possible_keys | key     | key_len | ref                                       | rows    | Extra       |
+----+-------------+--------------+--------+---------------+---------+---------+-------------------------------------------+---------+-------------+
|  1 | SIMPLE      | davesclass0_ | ALL    | NULL          | NULL    | NULL    | NULL                                      | 1914867 |             |
|  1 | SIMPLE      | myregcode1_  | eq_ref | PRIMARY       | PRIMARY | 98      | my_db.davesclass0_.ACCESS_CODE_ID         |       1 |             |
|  1 | SIMPLE      | accesscode2_ | eq_ref | PRIMARY       | PRIMARY | 98      | my_db.davesclass0_.TEACHER_ACCESS_CODE_ID |       1 | Using where |
+----+-------------+--------------+--------+---------------+---------+---------+-------------------------------------------+---------+-------------+

Is there any way this can be rewritten to return the same results but have MySQL understand to use the indexes on the my_classroom table?

Edit: In response to lsemi’s suggestion, MySql's explain plan ...

mysql> explain select davesclass0_.id as id1_7_, davesclass0_.ACCESS_CODE_ID as ACCESS_13_7_, davesclass0_.TEACHER_ACCESS_CODE_ID as TEACHER15_7_ from my_classroom davesclass0_ where davesclass0_.ACCESS_CODE_ID in (select myregcode1_.ID from my_reg_code myregcode1_ where myregcode1_.ACCESS_CODE='ABCDEF') or davesclass0_.TEACHER_ACCESS_CODE_ID in (select myregcode2_.ID from my_reg_code myregcode2_ where myregcode2_.ACCESS_CODE='ABCDEF');
+----+--------------------+--------------+------+---------------+------+---------+------+--------+-----------------------------------------------------+
| id | select_type        | table        | type | possible_keys | key  | key_len | ref  | rows   | Extra                                               |
+----+--------------------+--------------+------+---------------+------+---------+------+--------+-----------------------------------------------------+
|  1 | PRIMARY            | davesclass0_ | ALL  | NULL          | NULL | NULL    | NULL | 216280 | Using where                                         |
|  3 | DEPENDENT SUBQUERY | NULL         | NULL | NULL          | NULL | NULL    | NULL |   NULL | Impossible WHERE noticed after reading const tables |
|  2 | DEPENDENT SUBQUERY | NULL         | NULL | NULL          | NULL | NULL    | NULL |   NULL | Impossible WHERE noticed after reading const tables |
+----+--------------------+--------------+------+---------------+------+---------+------+--------+-----------------------------------------------------+

Edit 2: Explain plan for the

mysql> explain select davesclass0_.id as id1_7_, davesclass0_.ACCESS_CODE_ID as ACCESS_13_7_, davesclass0_.TEACHER_ACCESS_CODE_ID as TEACHER15_7_ from mY_classroom davesclass0_ where davesclass0_.ACCESS_CODE_ID in (select myregcode1_.ID from my_reg_code myregcode1_ where myregcode1_.ACCESS_CODE='0008F0'); 
+----+--------------------+--------------+-------+---------------------------+-------------------+---------+-------+--------+-------------+
| id | select_type        | table        | type  | possible_keys             | key               | key_len | ref   | rows   | Extra       |
+----+--------------------+--------------+-------+---------------------------+-------------------+---------+-------+--------+-------------+
|  1 | PRIMARY            | davesclass0_ | ALL   | NULL                      | NULL              | NULL    | NULL  | 216280 | Using where |
|  2 | DEPENDENT SUBQUERY | myregcode1_ | const | PRIMARY,UK_my_reg_code | UK_my_reg_code | 98      | const |      1 | Using index |
+----+--------------------+--------------+-------+---------------------------+-------------------+---------+-------+--------+-------------+
2 rows in set (0.00 sec)

mysql> explain select davesclass0_.id as id1_7_, davesclass0_.ACCESS_CODE_ID as ACCESS_13_7_, davesclass0_.TEACHER_ACCESS_CODE_ID as TEACHER15_7_ from mY_classroom davesclass0_ where davesclass0_.ACCESS_CODE_ID = 'ABCEF';
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

Do you alway want 100% of my_classroom records, even if neither of the two joins return any related records? I recommend adding to your question a sentence explaining what the result of the query is supposed to be (or that the current results are correct) and details so we can know if your really need a left join or not. — Ray, Oct 06 '16 at 19:46
If the codes don't match anything in the table, no rows will be returned. It will not return all rows in the table as you suggest above. Indeed, I don't want all rows returned. — Dave, Oct 06 '16 at 21:50

LSerni · Answer 1 · 2016-10-07T18:21:38.283

Note: I am assuming that you want only those records that match and that this number is small. Otherwise you're reading everything, and therefore indexes are useless. So, MySQL is correct in not using them.

Under this hypothesis, I'd start rewriting those JOINs as straight JOINs (not LEFT JOINs), and verify there is an index such as

CREATE INDEX my_reg_code_ndx ON my_reg_code(ACCESS_CODE);

Then you could perhaps use a single JOIN (results may be subtly different if a single record has both access and teacher access set to ABCDEF's ID):

JOIN my_reg_code AS mrc ON (mrc.ID = ACCESS_CODE_ID OR mrc.ID = TEACHER_ACCESS_CODE_ID)

Or you could also rewrite the WHERE as a different query altogether:

SELECT ... FROM my_classroom AS mc
WHERE mc.ACCESS_CODE_ID IN 
   (SELECT ID from my_reg_code WHERE ACCESS_CODE='ABCDEF')
OR mc.TEACHER_ACCESS_CODE_ID IN (...)

Normally MySQL runs a calculation before doing each query, and this calculation is based on the query structure and constraints. This means that some conditions might hold that MySQL is unable to calculate in the few milliseconds allotted to the initial query study.

One source of this information is the ANALYZE command, which updates index statistics.

But this might not be enough, because if you do a query like

SELECT * FROM table WHERE indexedfield = value

some estimate of the field cardinality can be given based on the value; if you do instead

SELECT * FROM table WHERE indexfield = FUNCTION(value)

or

SELECT * FROM table WHERE indexfield = SELECT(...)

then it's possible that MySQL won't run the function or query, and therefore not be able to actually do the analysis of the outer SELECT. When actually running the query, the function or SELECT will translate to a set of values, and then MySQL will run the outer query analysis and use an index, or not.

So a complex query (not JOIN based) might use the index you expect, one you don't expect, or none at all.

Enter the HINT

You can suggest to MySQL to use a specific index:

SELECT * FROM my_classroom AS mc  USE INDEX (indexToUse)
    WHERE mc.ACCESS_CODE_ID IN (...) ...

You can also use FORCE instead of USE.

Hi, I tried your suggestion (included MySQL explain plan in my queiton) but it is still telling me it is doing a full table scan, which I'm tryhing to avoid. — Dave, Oct 06 '16 at 20:47
Even when I use an access code taht exists in the table (returning one row from the query), your subquery optimization is still doing a full table scan according to MySQL's explain plan. Whether or not the code is in the table, it shoudln't affect the explain plan, should it? — Dave, Oct 06 '16 at 21:40
I'm not sure. But here I would expect the index to be used. Unless the expense to retrieve the other data was comparable or Mysql *thought* so. .. try analyzing the table, and ask explain for a select based on WHERE ACCESS_CODE=somecode without anything else. — LSerni, Oct 06 '16 at 22:27
I added more explain plans into my answer. Still does a full table scan with one subquery. With just checking the code's ID explain plan gives me some strange "Impossible WHERE noticed after reading const tables" message. — Dave, Oct 07 '16 at 13:26
This is *weird*. With an existing code ID, it says *impossible WHERE*? Either you have some really bad charset juju going on, and the condition you think you're setting isn't really the one MySQL is getting, or the index somehow got corrupted. If you run the query for real, does it return results? — LSerni, Oct 07 '16 at 14:30
Running the query with the "IMPOSIBLE" explain plan returns zero rows. When it returns something, explain plan says it used an index. But the question remains, why does doing a single subquery result in a full table scan? — Dave, Oct 07 '16 at 15:42

score 0 · Answer 2 · answered Oct 06 '16 at 19:33

Problem is in this condition:

myregcode1_.ACCESS_CODE='ABCDEF' or accesscode2_.ACCESS_CODE='ABCDEF';

Rewrite query using UNION:

select davesclass0_.id as id1_7_, davesclass0_.ACCESS_CODE_ID as ACCESS_13_7_,
    davesclass0_.CLASSROOM_NAME as CLASSROO7_7_, davesclass0_.TEACHER_ACCESS_CODE_ID as TEACHER15_7_
from my_classroom davesclass0_
left outer join my_reg_code myregcode1_ on davesclass0_.ACCESS_CODE_ID=myregcode1_.ID
left outer join my_reg_code accesscode2_ on davesclass0_.TEACHER_ACCESS_CODE_ID=accesscode2_.ID
where myregcode1_.ACCESS_CODE='ABCDEF';

UNION

select davesclass0_.id as id1_7_, davesclass0_.ACCESS_CODE_ID as ACCESS_13_7_,
    davesclass0_.CLASSROOM_NAME as CLASSROO7_7_, davesclass0_.TEACHER_ACCESS_CODE_ID as TEACHER15_7_
from my_classroom davesclass0_
left outer join my_reg_code myregcode1_ on davesclass0_.ACCESS_CODE_ID=myregcode1_.ID
left outer join my_reg_code accesscode2_ on davesclass0_.TEACHER_ACCESS_CODE_ID=accesscode2_.ID
where accesscode2_.ACCESS_CODE='ABCDEF';

The queries are being generated through Hibernate (JPA 2.1) and sadly, Hibernate does not support UNION, at least, not from waht I have read. — Dave, Oct 06 '16 at 20:48
@Dave, Maybe http://stackoverflow.com/a/18257931/1920758 or http://stackoverflow.com/a/27569757/1920758 will help — Nick, Oct 06 '16 at 21:27
I was hoping to knock this out with one efficient query instead of two but if the only way is to execute two different queries, so be it. I want to explore the one query path a little further though. — Dave, Oct 06 '16 at 21:48

Ray · Answer 3 · 2016-10-07T14:16:09.580

0

Assuming you really always want every record from my_classroom, even if it has no corresponding entries from one of the two left joins, your query will never use an index to limit the my_classroom aliased table davesclass0_

This is because:

you're using it as the left hand side of a left join
nothing in your WHERE clause uses the indexed column of my_classroom to limit the results from it (because of the or)

The simple solution is break it into 2 queries and use a UNION as mentioned in another post.

edited Oct 07 '16 at 14:16

answered Oct 06 '16 at 19:36

Ray

40,256
21
101
138

You have reframed the problem well. How do I make this query perform better (e.g. no full talbe scans) while returning the same data? – Dave Oct 06 '16 at 21:41

score 0 · Answer 4 · answered Oct 07 '16 at 01:21

OR -> UNION DISTINCT
Get rid of LEFT
Get rid of unnecessary JOINs
Shorten aliases (for less clutter)
Add some indexes

Something like:

select  c.id as id1_7_, c.ACCESS_CODE_ID as ACCESS_13_7_,
        c.CLASSROOM_NAME as CLASSROO7_7_, c.TEACHER_ACCESS_CODE_ID as TEACHER15_7_
    from  my_classroom AS c
    JOIN  my_reg_code AS r  ON c.ACCESS_CODE_ID = r.ID
    where  r.ACCESS_CODE='ABCDEF'
UNION  DISTINCT 
select  c.id as id1_7_, c.ACCESS_CODE_ID as ACCESS_13_7_,
        c.CLASSROOM_NAME as CLASSROO7_7_, c.TEACHER_ACCESS_CODE_ID as TEACHER15_7_
    from  my_classroom AS c
    JOIN  my_reg_code AS a  ON c.TEACHER_ACCESS_CODE_ID = a.ID
    where  a.ACCESS_CODE='ABCDEF';

Indexes:

my_reg_code:   INDEX(ACCESS_CODE, ID)  -- (composite)
my_classroom:  INDEX(ACCESS_CODE_ID), INDEX(TEACHER_ACCESS_CODE_ID)  -- (separate)

Similar to mnv's answer, my issue is taht Hibernate isn't capable of generating UNION queries (I'm using the JPA criteria builder). mnv posted a link to help break it apart into multiple smaller queries, so I might end up doing that, but I wanted to see if I could get this into one query first. — Dave, Oct 07 '16 at 15:40
I'm sorry that your 3rd party software is getting in the way. It should be helping, not hindering. — Rick James, Oct 07 '16 at 16:27

Why isn't MySQL using my indexes when running my query?

4 Answers4

Enter the HINT