Combining Queries for better performance?

[plautzer]

Hi,

 

I have a 4 queries (3 Select, 1 Update) that run through the same dataset and I wonder if there is a way to combine (some of) these queries in order to gain a better performance. I need three things.

 

First (1) I need the lastest row within a certain time frame:

 

SELECT last_update, value
FROM table
WHERE 
pid = 1000
AND cdate >= '2008-12-12'
Order by cdate DESC

 

Second (2) I need to count a flag within that same time frame:

 

SELECT sum(flag)
FROM table
WHERE 
pid = 1000
AND cdate >= '2008-12-12'

 

Third (3) I want to count the same flag outside the time frame:

 

SELECT sum(flag)
FROM table
WHERE 
pid = 1000
AND cdate < '2008-12-12'

 

And last (4) I want to update the flag outside the time frame:

UPDATE table
SET flag = 1
WHERE 
pid = 1000
AND cdate < '2008-12-12'
and flag is null

 

I wrapped my head around the queries for quiet some time but I cant find way to improve the performance further. Do u see a way to optimize them by maybe combining one or the other? Or should be better to run them seperately?

 

Greetz

 

Plautzer

[plautzer]

One thought I had was to combine query 2 and 3 with something like that:

 

SELECT sum(flag)
FROM table
WHERE pid = 1000
Group by 
"cdate >= '2008-12-12'
and cdate < '2008-12-12'"

 

I know its not correct but is it possible to divide a table by the date and sum/group them individually?

[kickstart]

Hi

 

It would be possible using an if or case statement to check the value of cdate and set a pseudo column depending on that.

 

Something like this.

 

SELECT cdatemarker , sum(flag)
FROM (SELECT flag, if(cdate < '2008-12-12',0,1) AS cdatemarker FROM table WHERE pid = 1000) b
GROUP BY cdatemarker 

 

All the best

 

Keith

[plautzer]

Thank you,

 

First of all it works well and second the performance got a lil bit worse (from 70s to 89s in my script)

 

thats may EXPLAIN:

 

id select_type table       type possible_keys key       key_len     ref rows Extra

1 PRIMARY       <derived2> ALL NULL               NULL       NULL          NULL 482 Using temporary; Using filesort

2 DERIVED       table            ref pid_cdate        4                                     459

 

 

I set an index on (pid, cdate).

 

Do u know how I get rid of the filesort and the temporary?

[kickstart]

Hi

 

Slightly simplified code

 

SELECT if( cdate < '2008-12-12', 0, 1 ) AS cdatemarker, sum( flag ) 
FROM table
WHERE pid =1000
GROUP BY cdatemarker

 

Not sure it is going to save you anything.

 

All the best

 

Keith