Performance query count and subselect

[buxmac]

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

[carpii]

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

[buxmac]

Ok! thank!! i think that your query is better.

Bye

[buxmac]

[B]carpii wrote on Fri, 18 May 2007 20:15[/B]

Try this... CUT b.TRANSACTION_ID=‘580’ AND b.RESPONSE=‘0’