0

In our production environment (mySQL 5.6.40 on Amazon RDS), for certain queries the query optimizer ignores table indexes and takes several minutes to complete, whereas in our non-RDS test environment (mySQL 5.7), the same queries take a fraction of a second - due to using the indexes.

Rather than immediately upgrading from mySQL 5.6.40 to 5.7 on RDS (assuming the optimizer simply works better!), I wondered if there was something we're missing as the queries appear straight forward... I'm a bit surprised the optimizer doesn't pick the indexes.

Example query:

select id, username, agent, app_instance_id, end_time, event_time, 
last_modified 
from event where username='joebloggs' and app_instance_id<>'ad69469a- 
d73f-4b33-a1c5-bde03c765d89' and last_modified>'2018-08-20 20:14:51.394' 
order by last_modified asc limit 20

Execution time:

  • mySQL 5.6.40: 6m+
  • mySQL 5.7: 0.02s

EXPLAIN:

On mySQL 5.6.40 EXPLAIN shows that the optimizer doesn't use any of the indexes (5th column 'possible keys', 9th column 'extra')

'1','SIMPLE','event','range','last_modified,username,appinstanceid,user_app_last','last_modified','6',NULL,'1128956','Using where'

Whereas on mySQL 5.7 EXPLAIN shows the optimizer picks an index.

'1','SIMPLE','event',NULL,'range','username,last_modified,appinstanceid,user_app_last','last_modified','6',NULL,'1030540','0.54','Using index condition; Using where'

Long story short, our RDS instance used to run mySQL 5.5 and all worked fine... but with a forced upgrade to 5.6.40 due to end of life on 5.5 this problem has bubbled to the surface.

I've also run ANALYZE TABLE to ensure stats etc updated for optimizer. Any insight would be greatly appreciated. Thanks!

UPDATE:

  1. Our client app is using JPA, which can be configured to use index hints. Is it simply that the query optimizer is better on 5.7 and that we should upgrade, or should we be providing hints after all.. and not relying on the query optimizer?

  2. After running further tests on a new TEST instance (mySQL 5.7.23 upgraded from 5.6.40) on RDS it appears to be hit and miss on the same query as to whether the optimizer chooses to use indexes.

  • Could you please add the name of the index that is used by 5.7 so I can add it to my answer? – ltlBeBoy Oct 27 '18 at 8:12
  • From my basic understanding, from the explain it appears to be 'last_modified' which is surprising again as there's also a composite (username, appinstanceid, last_modified) available which would fit the bill as far as I can see. – darkmatter Oct 27 '18 at 8:18
  • Another solution: You said, ther is a composite key (username, appinstanceid, last_modified). Does ist work, if you create a separate index for each of these columns? – ltlBeBoy Oct 27 '18 at 9:38
  • @ltlBeBoy The table already has each separate index. I added the composite later, after noticing a related performance issue with a query using the same columns (select max(last_modified) from event where username='joebloggs' and app_instance_id<>'ad69469a-d73f-4b33-a1c5-bde03c765d89'). The composite worked for this query, but not the one above in the description. – darkmatter Oct 27 '18 at 10:31
  • One last idea: Have you tried (just for testing) to remove the composite key and using the separate indexes only for this query? Perhaps the optimizer sometimes chooses the composite key for this query which does not perform well... – ltlBeBoy Oct 27 '18 at 17:56
1

In any system, this will probably be beneficial, probably optimal:

INDEX(username, last_modified) -- in this order

Please provide SHOW CREATE TABLE so we can see what indexes, datatypes, engine, etc, you have.

The index I recommend

  • Is a 'composite' index.
  • Is not the same as two single-column indexes.
  • Deliberately starts with the column tested via =.
  • Ignores the <> as not being useful.
  • Includes one 'range' (on last_modified).
  • It should, but may not consume the ORDER BY, thereby allowing it to efficiently consume the LIMIT. Without reaching all the way to the LIMIT, it is destined to scan the entire table, thereby taking a 'long' time.

A side note: UUIDs (such as what app_instance_id seems to be) are notoriously inefficient when indexed in large tables. For this query, I am hoping that very few of the rows have that particular value.

To further investigate the "why", answer these questions:

  • What percentage of the table contains username='joebloggs'. and
  • What percentage of the table contains last_modified>'2018-08-20 20:14:51.394'

If either of these is more than say, 20%, then the Optimizer wisely chose to ignore the corresponding index. It could be that the statistics on one was better than on the other. Let's see SHOW INDEXES FROM event.

Cookbook for devising good indexes.

Try to avoid index hints -- they may help today, but hurt tomorrow (with different constants).

  • Thanks - this works brilliantly and both your explanation and cookbook are clear and precise. :) The existing composite that includes (app_instance_id) is still required for the max request... so, I replaced both composites with a single composite in the correct order (username, last_modified, app_instance_id) which appears to be extremely quick for both queries. More than happy not to use index hints! – darkmatter Oct 29 '18 at 12:08
1

Edit:

As commented by user @Rick James, it is always better to try to improve the indexes first before using MySQL Index Hints. Further description for finding the matching INDEX can be found in his excellent cookbook for building the best INDEX for a given SELECT in MySQL. Also please do not add INDEX(a) if you already have INDEX(a,b).

See his answer for a better solution.


Original answer:

Native SQL queries

You can use the MySQL Index Hints to show the optimizer how to choose the indexes.

Queries created by JPA

You can use subqueries to use the preferred index first and to reduce the number of lines for the main query.

Example:

SELECT e.id, e.username, e.agent, e.appInstanceId, e.endTime, e.eventTime, e.lastModified 
FROM Event e
WHERE e.username = 'joebloggs'
AND e.appInstanceId <> 'ad69469a-d73f-4b33-a1c5-bde03c765d89'
AND EXISTS(SELECT esub FROM Event esub WHERE esub = e AND esub.lastModified > '2018-08-20 20:14:51.394')
ORDER BY e.lastModified
  • Thanks - certainly one option... although I was trying to keep clear of query hints as they require changes to our jpa/hibernate app server. – darkmatter Oct 27 '18 at 8:12
  • OK, then could you please add a hint to the question that you are using JPA? – ltlBeBoy Oct 27 '18 at 8:14
  • @darkmatter I added a solution for JPA. Please let me know if it is an option for you. – ltlBeBoy Oct 27 '18 at 9:22
  • @ItlBeBoy Thanks - it's certainly an option... although currently the SQL is generated by JPA/Hibernate and I'm reluctant to override it. – darkmatter Oct 27 '18 at 9:27
  • After running further tests (see update in question), it's looking like index hints in JPA may be the way to go. – darkmatter Oct 27 '18 at 15:51

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.