mySQL query optimizer not using indexes 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&lt;&gt;'ad69469a- 
d73f-4b33-a1c5-bde03c765d89' and last_modified&gt;'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: 


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?
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.
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 &lt;&gt; 'ad69469a-d73f-4b33-a1c5-bde03c765d89'
AND EXISTS(SELECT esub FROM Event esub WHERE esub = e AND esub.lastModified &gt; '2018-08-20 20:14:51.394')
ORDER BY e.lastModified