SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query Optimisation, Is there is any other way to change this query?

    Query Optimisation, Is there is any other way to change this query. it takes some time to return result?

    SELECT count(pagerno) as new
    FROM tbldetails s1
    WHERE s1.ccode = '$StrCode'
    AND s1.col1 <> 'invalid'
    AND s1.col5 = '0'
    AND s1.col6 <='$strDte'
    AND s1.col6=(select MAX(s2.col6) FROM tbldetails s2
    WHERE s1.pagerno = s2.pagerno AND s2.ccode = '$StrCode'
    AND s2.col1 <> 'invalid' AND s1.col5 = '0' AND s2.col6 <='$strDte'

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Have you defined indexes on the columns you're using here?

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    Have you defined indexes on the columns you're using here?
    yes i did, following is the indexes columns.

    Action Keyname Type Unique Packed Field Cardinality Collation Null Comment
    t_idx BTREE No No detailid 38528 A YES
    t_id BTREE No No ccode 265 A YES
    col1 265 A YES
    col6 9632 A YES
    pagerno 38528 A YES
    col5 38528 A YES


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •