optimizing my query somehow?

bleastan · #1 (**permalink**) 11-03-10, 08:48

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.

dav1mo · #2 (**permalink**) 11-03-10, 09:23

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

bleastan · #3 (**permalink**) 11-03-10, 09:42

Quote:

Originally Posted by dav1mo

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?

Quote:

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

bchanan · #4 (**permalink**) 11-04-10, 04:13

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

dav1mo · #5 (**permalink**) 11-04-10, 12:11

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

bleastan · #6 (**permalink**) 11-05-10, 09:55

Quote:

Originally Posted by dav1mo

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.

dav1mo · #7 (**permalink**) 11-05-10, 10:48

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

bleastan · #8 (**permalink**) 11-05-10, 11:04

Quote:

Originally Posted by dav1mo

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'

dav1mo · #9 (**permalink**) 11-05-10, 13:31

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

bleastan · #10 (**permalink**) 11-08-10, 04:15

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 .

dav1mo · #11 (**permalink**) 11-08-10, 08:47

It seems this is not allowed in MySQL, after all.

Quote:

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

bleastan · #12 (**permalink**) 11-08-10, 09:24

Quote:

Originally Posted by dav1mo

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

Internet Services

Web Development

Digital Marketing

Consumer Tech