GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Performance query count and subselect

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Performance query count and subselect

    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

  • #2
    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

    Comment


    • #3
      Ok! thank!! i think that your query is better.

      Bye

      Comment


      • #4
        carpii wrote on Fri, 18 May 2007 20:15

        Try this...


        CUT b.TRANSACTION_ID='580' AND b.RESPONSE='0'



        Comment

        Working...
        X