Re: MySQL 5.5 Query Optimizer - Massive Regression (vs 5.0)
Hi Øystein,
Of course!
Here's an example query:
SELECT t.id, t.txn_hash, t.client_id, bank_merchant_id, store_id, refund_flag, tradingname, rtrim(ponum) as ponum, txn_type, txn_source, txn_channel, card_type, pan, card_number, expiry_date, cardholder_name, cardholder_email, amount, currency_code, surcharge_amount, surcharge_rate, surcharge_fee, trx_time, response_code, response_text, rtrim(bank_txn_id) as bank_txn_id, settlement, t.gateway_merchant_id, t.gateway_terminal_id, sli, eci, xid, ip_address, bank_auth_id, t.batch_id, b.code as batch_code, p.auth_code, p.completed, af_response_code, af_response_text, af_return_code, af_result1, af_result2, af_add_info1, af_add_info2, orig_txn_source, user_merchant_id, user_username FROM t_transactions t LEFT JOIN t_preauth_codes p ON t.id = p.transaction_id LEFT OUTER JOIN t_merchants m ON m.merchant_id = t.merchant_id LEFT OUTER JOIN t_batch b ON t.batch_id = b.id WHERE 1 = 1 AND t.merchant_id LIKE 'AAA%' AND trx_time BETWEEN '2010-12-28 00:00:00.000' AND '2012-02-02 23:59:59.999' AND amount = 42512 AND txn_source <> 36 ORDER BY t.trx_time DESC LIMIT 3001;
Under MySQL 5, explain looks like this:
+----+-------------+-------+--------+-------------------------------+----------------+---------+-----------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------+----------------+---------+-----------------------+--------+-----------------------------+
| 1 | SIMPLE | t | range | IX_client_time,IX_time_client | IX_client_time | 16 | NULL | 174660 | Using where; Using filesort |
| 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | t.ID | 1 | |
| 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 7 | t.merchant_id | 1 | |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | t.batch_id | 1 | |
+----+-------------+-------+--------+-------------------------------+----------------+---------+-----------------------+--------+-----------------------------+
Under MySQL 5.5 though, explain shows that it's more interested in avoiding filesort and using an index to satisfy the ORDER BY than it is in minimising the number of rows returned:
+----+-------------+-------+--------+-------------------------------+----------------+---------+-----------------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------+----------------+---------+-----------------------+----------+-------------+
| 1 | SIMPLE | t | range | IX_client_time,IX_time_client | IX_time_client | 16 | NULL | 31436337 | Using where |
| 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | t.ID | 1 | |
| 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 7 | t.merchant_id | 1 | |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | t.batch_id | 1 | |
+----+-------------+-------+--------+-------------------------------+----------------+---------+-----------------------+----------+-------------+
Interestingly, simply changing the ORDER BY conditions very slightly cause MySQL 5.5 to act like 5.0 normally does. Changing the ORDER BY from:
ORDER BY t.trx_time DESC
to
ORDER BY t.trx_time, 1 DESC
Results in almost identical EXPLAIN output.
The same modification to the ORDER BY clause solves the issue for all the cases I initially identified.