Re: Help me to get fast query

[gua syed]

sir,
here it is..


mysql> use zakatdb;
Database changed
mysql> explain extended
-> select `epstransaction`.`id` AS `id`,
-> `epstransaction`.`id` AS `eps_trans_id`,
-> `epstransaction`.`receipt_no` AS `receipt_no`,
-> `epstransaction`.`payment_datetime` AS `payment_datetime`,
-> `epstransaction`.`reconciled_datetime` AS `reconciled_datetime`,
-> `epstransaction`.`payment_clear_datetime` AS `payment_clear_datetime`,
-> `gatewayaccount`.`paymentmode` AS `payment_mode`,
-> `epstransaction`.`merchant_trans_id` AS `merchant_trans_id`,
-> `epstransaction`.`exact_amount` AS `amount`,
-> `epstransaction`.`eps_status` AS `status`,
-> `epstransaction`.`status_code` AS `status_code`,
-> `epstransaction`.`reconciled` AS `reconciled`,
-> DAYOFMONTH(`epstransaction`.`payment_datetime`) AS `epsday`,
-> MONTH(`epstransaction`.`payment_datetime`) AS `epsmonth`,
-> WEEK(`epstransaction`.`payment_datetime`,1) AS `epsweek`,
-> YEAR(`epstransaction`.`payment_datetime`) AS `epsyear`,
-> `epstransaction`.`amount` AS `exact_amount`,
-> `epstransaction`.`gateway_account_id` AS `gateway_account_id`,
-> `gatewayaccount`.`merchant_id` AS `merchant_id`,
-> (SELECT
-> `transaction_xtras`.`value` AS `value`
-> FROM `transaction_xtras`
-> WHERE ((`transaction_xtras`.`transaction_id` = `epstransaction`.`id`)
-> AND (`transaction_xtras`.`key` = 'no_kp_lama'))) AS `nokp`
-> FROM (`eps_transactions` `epstransaction`
-> LEFT JOIN `gateway_accounts` `gatewayaccount`
-> ON ((`epstransaction`.`gateway_account_id` = `gatewayaccount`.`id`)))
-> ORDER BY `epstransaction`.`payment_datetime` DESC;
+----+--------------------+-------------------+--------+---------------+---------+---------+-------------------------------------------+-------+----------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------------------+--------+---------------+---------+---------+-------------------------------------------+-------+----------+----------------+
| 1 | PRIMARY | epstransaction | ALL | NULL | NULL | NULL | NULL | 4485 | 100.00 | Using filesort |
| 1 | PRIMARY | gatewayaccount | eq_ref | PRIMARY | PRIMARY | 4 | zakatdb.epstransaction.gateway_account_id | 1 | 100.00 | |
| 2 | DEPENDENT SUBQUERY | transaction_xtras | ALL | NULL | NULL | NULL | NULL | 55271 | 100.00 | Using where |
+----+--------------------+-------------------+--------+---------------+---------+---------+-------------------------------------------+-------+----------+----------------+
3 rows in set, 2 warnings (0.00 sec)