MySQL Forums
Forum List  »  Optimizer & Parser

Re: MySQL 5.5 Query Optimizer - Massive Regression (vs 5.0)
Posted by: Rick James
Date: February 03, 2012 11:57PM

For readability:
SELECT, 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 = 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 =
    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"?

Options: ReplyQuote

Written By
Re: MySQL 5.5 Query Optimizer - Massive Regression (vs 5.0)
February 03, 2012 11:57PM

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.