MySQL Forums
Forum List  »  MySQL Query Browser

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 ;

Options: ReplyQuote


Subject
Written By
Posted
Help me to get fast query
January 08, 2012 12:59AM
January 08, 2012 12:21PM
January 08, 2012 12:28PM
January 08, 2012 08:25PM
January 09, 2012 08:30PM


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.