MySQL Forums
Forum List  »  Optimizer & Parser

Re: MySQL 5.5 Query Optimizer - Massive Regression (vs 5.0)
Posted by: Chris Ricks
Date: February 02, 2012 04:43AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL 5.5 Query Optimizer - Massive Regression (vs 5.0)
1144
February 02, 2012 04:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.