| Performance query count and subselect [message #1270] |
Fri, 18 May 2007 12:27  |
 buxmac
Messages: 4
Registered: May 2007 |
Junior Member |
|
|
Hi,
I have this query:
SELECT COUNT(*) FROM `transaction_table`
WHERE RESPONSE='0' AND DATEDIFF(CURDATE(), TRANSACTION_DATE) <= 3
AND PAN_ENCRYPTED=
(
SELECT PAN_ENCRYPTED FROM `transaction_table`
WHERE TRANSACTION_ID='580' AND RESPONSE='0'
)
My db is a InnoDb and i use mysql 5.
How to improve the speed of my query?
It's better to use ROW_COUNT and not COUNT(*) ?
It's better make two distinct queries or a subselect?
Thank you
Andrea
|
|
|
| Re: Performance query count and subselect [message #1271 is a reply to message #1270 ] |
Fri, 18 May 2007 14:15  |
carpii
Messages: 46
Registered: November 2006 |
Member |
|
|
Try this...
SELECT
COUNT(*)
FROM
transaction_table a
inner join transaction_table b ON (a.PAN_ENCRYPTED = b.PAN_ENCRYPTED)
WHERE
a.RESPONSE='0' AND
a.TRANSACTION_DATE >= DATE_SUB(CURDATE(), interval 3 day) AND
b.TRANSACTION_ID='580' AND
b.RESPONSE='0'
If this isnt any faster, please post the EXPLAIN for both yours and my query
[Updated on: Fri, 18 May 2007 14:15]
|
|
|
|
| Re: Performance query count and subselect [message #1279 is a reply to message #1271 ] |
Mon, 21 May 2007 04:52 |
buxmac
Messages: 4
Registered: May 2007 |
Junior Member |
|
|
| carpii wrote on Fri, 18 May 2007 20:15 |
Try this...
CUT
b.TRANSACTION_ID='580' AND
b.RESPONSE='0'
|
[Updated on: Mon, 21 May 2007 04:55]
|
|
|
Members
Search
Help
Register
Login
Home
