SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How can i rewrite this query for faster execution

    Code:
    SELECT s1.ID FROM binventory_ostemp s1 JOIN 
    ( SELECT Cust_FkId, ProcessID, MAX(Service_Duration) AS duration 
     FROM binventory_ostemp WHERE ProcessID='4d2d6068678bc' AND Overall_Rank IN 
     (
      SELECT MIN(Overall_Rank) FROM binventory_ostemp WHERE ProcessID='4d2d6068678bc' GROUP BY Cust_FkId
     )
     GROUP BY Cust_FkId
    ) AS s2 ON s1.Cust_FkId = s2.Cust_FkId AND s1.ProcessID=s2.ProcessID 
    AND s1.Service_Duration=s2.duration AND s1.ProcessID='4d2d6068678bc' 
    GROUP BY s1.Cust_FkId
    It just goes away if there are more than 10K rows in that table. What it does is find rows for each customer who has min. of overall rank and in those max. of service duration for a given processid

    Table Data

    Code:
    ID Cust_FkId  Overall_Rank Service_Duration  ProcessID
    1     23       2            30             4d2d6068678bc
    2     23       1            45             4d2d6068678bc
    3     23       1            60             4d2d6068678bc
    4     56       3            90             4d2d6068678bc
    5     56       2            50             4d2d6068678bc
    6     56       2            85             4d2d6068678bc
    Result Data

    Result ID values must be 3 and 6 only

  2. #2
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you post the output of SHOW CREATE TABLE for each of those and output of EXPLAIN SELECT [that query you got there]?

  3. #3
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Showing the SHOW CREATE TABLE will help. You may be able to speed it up by making indexes for some columns. It becomes slightly more expensive to INSERT data into an index, but is less expensive to SELECT from an index.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mrbinky3000 View Post
    It becomes slightly more expensive to INSERT data into an index, but is less expensive to SELECT from an index.
    i would just like to make a minor correction to this above sentence, which could give some people the mistaken idea that you can actually insert stuff into an index or select from an index

    the revision i would suggest is:
    It becomes slightly more expensive to INSERT data into a table that has an index, but is less expensive to SELECT from a table that has an index that the query can use
    not every additional index is going to help a given SELECT, but every additional index will slow down INSERTs (and DELETEs)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

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
  •