Multiple instances of an aurora mysql db. Only one of them does not use the possible index

I ran into a performance issue with a large query, and broke it down with explain. Dev and production are fine but the QA DB is having trouble.

Here is a sanitized explain

EXPLAIN SELECT COUNT(*)
FROM  table1 t1
LEFT OUTER JOIN table2 t2 ON t2.correlation_id = t1.correlation_id
WHERE t2.request_id = '<varchar(100) here>';

In one developer instance of the db I get

select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, extra
SIMPLE,t2,,ref,request_id,request_id,1022,const,3753,100,Using where
SIMPLE,t1,,eq_ref,"correlationIdUniqueConstraint,idx_correlation_id",correlationIdUniqueConstraint,402,dev_db.t2.correlation_id,1,100,Using index

Production is very similar / identical. So everything seems fine so far.

However in the QA db I'm getting

select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, extra
SIMPLE,t2,,ref,request_id,request_id,1022,const,1176,100, <null>
SIMPLE,t1,,ALL,"correlationIdUniqueConstraint,idx_correlation_id",,,,3271577,10,Range checked for each record (index map: 0x40000004)

Using the schema compare tool in datagrip I made the Developer & QA dbs match. The only difference would be the exact rows/counts. However in terms of rows Production > QA > Dev and yet Production & Dev are fine.

Schema info if relevant:

request_id has an index
correlation_id is varchar(100) in both t1 & t2.
The correlation_id unique constraint & index exist on t1

I confirmed with

SELECT CHAR_LENGTH(correlation_id)
FROM merchant_analysis
WHERE CHAR_LENGTH(correlation_id) > 90;

That no correlation_ids even exceed 90 chars much less 100.

Some DB info: 5.7.mysql_aurora.2.07.2 QA & Production Ram > Dev RAM

I'm hoping someone has seen a gotcha like this before, and I don't have to recreate the QA db.

from Recent Questions - Stack Overflow https://ift.tt/3GWz3Ga
https://ift.tt/eA8V8J