Re: MySQL 5.5 Query Optimizer - Massive Regression (vs 5.0)

[Rick James]

For readability:

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;

Please provide SHOW CREATE TABLE, or at least the INDEX parts of it, together with the definitions of the fields used, and the ENGINE used.

The EXPLAIN talks about IX_client_time,IX_time_client -- but I don't see any mention of "client" in the WHERE or ORDER BY ??

What do the EXPLAINs say without the LIMIT clause?

Are those really "OUTER"?