Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2010
    Posts
    10

    Unanswered: optimizing my query somehow?

    Hello peoples of dbforums i got a question. After quite a while i finally managed to get my query working (i dont work with databases alot so it might look simple to you all but ive been busy with it along time). Anyways it looks like this:

    SELECT d1.date, ROUND( (
    d1.avR + 0.8 * d2.avR + 0.4 * d3.avR + 0.2 * d4.avR
    ) / 2.4, 1 ) AS Result
    FROM (

    SELECT date, (
    MAX( ts ) /10 + MIN( ts ) /10
    ) /2 AS avR
    FROM tbl_aviar
    GROUP BY date
    ) AS d1
    JOIN (

    SELECT date, (
    MAX( ts ) /10 + MIN( ts ) /10
    ) /2 AS avR
    FROM tbl_aviar
    GROUP BY date
    ) AS d2 ON d2.date = ( d1.date - INTERVAL 1
    DAY )
    JOIN (

    SELECT date, (
    MAX( ts ) /10 + MIN( ts ) /10
    ) /2 AS avR
    FROM tbl_aviar
    GROUP BY date
    ) AS d3 ON d3.date = ( d1.date - INTERVAL 2
    DAY )
    JOIN (

    SELECT date, (
    MAX( ts ) /10 + MIN( ts ) /10
    ) /2 AS avR
    FROM tbl_aviar
    GROUP BY date
    ) AS d4 ON d4.date = ( d1.date - INTERVAL 3
    DAY )
    WHERE d1.date
    BETWEEN 19650101
    AND 19800101

    Now the reason why i post is, it takes quite a long time to execute this query. According to sql it took 77.2756 secs to execute it. Now i was wondering is there any way to make it go quicker? Mayb im using a syntax which isnt optimal or something like that?

    If anyone have good suggestions then please enlighten me by your wisdom :P
    Thx in advance.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Copy your ON clause into each subselect as a WHERE predicate. The way you are doing it now, each of those tables are being grouped by the date for the entire table, then you are saying just give me the one that I want. By adding a where clause to each of the nested selects, you would only do the grouping for the date that you are after.
    Dave Nance

  3. #3
    Join Date
    Aug 2010
    Posts
    10

    anotedec often

    Quote Originally Posted by dav1mo View Post
    Copy your ON clause into each subselect as a WHERE predicate. The way you are doing it now, each of those tables are being grouped by the date for the entire table, then you are saying just give me the one that I want. By adding a where clause to each of the nested selects, you would only do the grouping for the date that you are after.
    Dave Nance
    Thank you for the reply but i dont really understand what you maen (as said i rarely work with databases and such and this already cost mealot of time). Can u mayb give an example?

    did you mean like this?
    SELECT d1.date, ROUND( (
    d1.avR + 0.8 * d2.avR + 0.4 * d3.avR + 0.2 * d4.avR
    ) / 2.4, 1 ) AS Result
    FROM (

    SELECT date, (
    MAX( ts ) /10 + MIN( ts ) /10
    ) /2 AS avR
    FROM tbl_aviar
    GROUP BY date
    ) AS d1
    JOIN (

    SELECT date, (
    MAX( ts ) /10 + MIN( ts ) /10
    ) /2 AS avR
    FROM tbl_aviar where date = date - INTERVAL 1 DAY
    GROUP BY date
    ) AS d2 ON d2.date = d1.date
    JOIN (

    SELECT date, (
    MAX( ts ) /10 + MIN( ts ) /10
    ) /2 AS avR
    FROM tbl_aviar where date = date - INTERVAL 2 DAY
    GROUP BY date
    ) AS d3 ON d3.date = d1.date
    JOIN (

    SELECT date, (
    MAX( ts ) /10 + MIN( ts ) /10
    ) /2 AS avR
    FROM tbl_aviar WHERE date = date - INTERVAL 3 DAY
    GROUP BY date
    ) AS d4 ON d4.date = d1.date
    WHERE d1.date
    BETWEEN 19650101
    AND 19800101
    Last edited by bleastan; 11-03-10 at 11:41.

  4. #4
    Join Date
    Dec 2009
    Posts
    27

    idea

    since you are running your query on specific date, and from that you are query it minus 1 day, minus 2 days, minus 3 days

    it's better to create a stored procedure where:

    1) you retrieve all records between 19650101 - 19800101 into a temporary table and from the last 3 days.
    2) run the join queries on the temporary table.

    assume the timestamp is indexed, you will get better result, since the havy queries will run on the temporary table, and not on the original table.

    All - correct me if i am wrong.

    thanks
    Chanan

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Thought I had answered this earlier. You are almost there. You still need the original ON clause as well. Something like:

    Code:
    SELECT date, (MAX( ts ) /10 + MIN( ts ) /10) /2 AS avR
        FROM tbl_aviar 
    where date = date - INTERVAL 1 DAY
    GROUP BY date) AS d2
        ON d2.date = ( d1.date - INTERVAL 1 DAY )
    Dave

  6. #6
    Join Date
    Aug 2010
    Posts
    10
    Quote Originally Posted by dav1mo View Post
    Thought I had answered this earlier. You are almost there. You still need the original ON clause as well. Something like:

    Code:
    SELECT date, (MAX( ts ) /10 + MIN( ts ) /10) /2 AS avR
        FROM tbl_aviar 
    where date = date - INTERVAL 1 DAY
    GROUP BY date) AS d2
        ON d2.date = ( d1.date - INTERVAL 1 DAY )
    Dave
    So ive made the query like this now:

    Code:
    SELECT d1.date, ROUND( (
    d1.avR + 0.8 * d2.avR + 0.4 * d3.avR + 0.2 * d4.avR
    ) / 2.4, 1 ) AS Result
    FROM (
    
    SELECT date, (
    MAX( ts ) /10 + MIN( ts ) /10
    ) /2 AS avR
    FROM tbl_aviar
    GROUP BY date
    ) AS d1
    JOIN (
    
    SELECT date, (
    MAX( ts ) /10 + MIN( ts ) /10
    ) /2 AS avR
    FROM tbl_aviar where date = date - INTERVAL 1 DAY
    GROUP BY date
    ) AS d2 ON d2.date = ( d1.date - INTERVAL 1
    DAY )
    JOIN (
    
    SELECT date, (
    MAX( ts ) /10 + MIN( ts ) /10
    ) /2 AS avR
    FROM tbl_aviar where date = date - INTERVAL 2 DAY
    GROUP BY date
    ) AS d3 ON d3.date = (d1.date - INTERVAL 2 DAY)
    JOIN (
    
    SELECT date, (
    MAX( ts ) /10 + MIN( ts ) /10
    ) /2 AS avR
    FROM tbl_aviar WHERE date = date - INTERVAL 3 DAY
    GROUP BY date
    ) AS d4 ON d4.date = (d1.date - INTERVAL 3 DAY)
    WHERE d1.date
    BETWEEN 19650101
    AND 19800101
    And.. it does execute really quick its just that i get an empty resultset back which isnt really what i need :P.

    So am i makign a mistake somewhere in the query? Thx for the replies so far though

    @bchanan
    In this particular example its a solid date but it can differ (the date is being passed on by php) so its not always the same set. Still ill test it out tomorrow.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The section:

    Code:
    FROM tbl_aviar where date = date - INTERVAL 2 DAY
    Should probably be:
    Code:
    FROM tbl_aviar where date = d1.date - INTERVAL 2 DAY
    and the same for all the other subselects

    Dave Nance

  8. #8
    Join Date
    Aug 2010
    Posts
    10
    Quote Originally Posted by dav1mo View Post
    The section:

    Code:
    FROM tbl_aviar where date = date - INTERVAL 2 DAY
    Should probably be:
    Code:
    FROM tbl_aviar where date = d1.date - INTERVAL 2 DAY
    and the same for all the other subselects

    Dave Nance
    I just tried that but it gives the error:
    #1054 - Unknown column 'd1.date in 'where clause'

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    That is probably because it is thought of as a nested View Expression. If you explicitly say that those are Nested Table Expressions it should work. I don't think all of them need to be, but can't think of the rule off the top of my head right now, in any case you can call them all tables, as in:
    Code:
    SELECT d1.date
         , ROUND( (d1.avR + 0.8 * d2.avR + 0.4 * d3.avR + 0.2 * d4.avR) / 2.4, 1 ) AS Result
    FROM TABLE(SELECT date
              , (MAX( ts ) /10 + MIN( ts ) /10) /2 AS avR
            FROM tbl_aviar
          GROUP BY date) AS d1
    JOIN TABLE(SELECT date
              , (MAX( ts ) /10 + MIN( ts ) /10) /2 AS avR
            FROM tbl_aviar
          GROUP BY date) AS d2
       ON d2.date = ( d1.date - INTERVAL 1DAY )
    JOIN TABLE(SELECT date
              , (MAX( ts ) /10 + MIN( ts ) /10) /2 AS avR
            FROM tbl_aviar
          GROUP BY date) AS d3
       ON d3.date = ( d1.date - INTERVAL 2DAY )
    JOIN TABLE(SELECT date
              , (MAX( ts ) /10 + MIN( ts ) /10) /2 AS avR
            FROM tbl_aviar
          GROUP BY date) AS d4
       ON d4.date = ( d1.date - INTERVAL 3DAY )
    WHERE d1.date BETWEEN 19650101
                      AND 19800101
    Oops. copied this from your original query, you want to do this with the where clauses that we already discussed referring to D1.

    Dave Nance

  10. #10
    Join Date
    Aug 2010
    Posts
    10
    So ive made it like this now
    Code:
    SELECT d1.date, ROUND( (
    d1.avR + 0.8 * d2.avR + 0.4 * d3.avR + 0.2 * d4.avR
    ) / 2.4, 1 ) AS Result
    FROM TABLE (
    
    SELECT date, (
    MAX( ts ) /10 + MIN( ts ) /10
    ) /2 AS avR
    FROM tbl_aviar
    GROUP BY date
    ) AS d1
    JOIN TABLE (
    
    SELECT date, (
    MAX( ts ) /10 + MIN( ts ) /10
    ) /2 AS avR
    FROM tbl_aviar where date = (d1.date - INTERVAL 1 DAY)
    GROUP BY date
    ) AS d2 ON d2.date = ( d1.date - INTERVAL 1
    DAY )
    JOIN TABLE (
    
    SELECT date, (
    MAX( ts ) /10 + MIN( ts ) /10
    ) /2 AS avR
    FROM tbl_aviar where date = (d1.date - INTERVAL 2 DAY)
    GROUP BY date
    ) AS d3 ON d3.date = (d1.date - INTERVAL 2 DAY)
    JOIN TABLE (
    
    SELECT date, (
    MAX( ts ) /10 + MIN( ts ) /10
    ) /2 AS avR
    FROM tbl_aviar WHERE date = (d1.date - INTERVAL 3 DAY)
    GROUP BY date
    ) AS d4 ON d4.date = (d1.date - INTERVAL 3 DAY)
    WHERE d1.date
    BETWEEN 19650101
    AND 19800101
    however, unfortunately it generates an error on the 'TABLE' expression like this:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE ( SELECT date, ( ' at line 4

    Ive tried moving around the table and removing them at some points but with no succes .
    Last edited by bleastan; 11-08-10 at 05:38.

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    It seems this is not allowed in MySQL, after all.
    MySQL :: MySQL 5.0 Reference Manual :: 12.2.9.8 Subqueries in the FROM Clause
    "Subqueries in the FROM clause cannot be correlated subqueries, unless used within the ON clause of a JOIN operation"

    This being the case, you may want to try unnesting them and then doing the group by on the entire subset.

    Dave Nance

  12. #12
    Join Date
    Aug 2010
    Posts
    10
    Quote Originally Posted by dav1mo View Post
    It seems this is not allowed in MySQL, after all.



    This being the case, you may want to try unnesting them and then doing the group by on the entire subset.

    Dave Nance
    Allright thanks so far. Ill look into it when i get back home

Posting Permissions

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