Help me to get fast query
Posted by:
gua syed
Date: January 08, 2012 12:59AM
I try to create view use sql yog, but i got so slow query. I believe it's coz new select query at 'transaction_xtras'. Please help me.
DELIMITER $$
USE `newdb`$$
DROP VIEW IF EXISTS `view_transactions`$$
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_transactions` AS (
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
`tx`.`value` AS `value`
FROM `transaction_xtras` `tx`
WHERE ((`tx`.`transaction_id` = `epstransaction`.`id`)
AND (`tx`.`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)$$
DELIMITER ;