SQL Query - replace hardcoded values... (MySQL)

Question

Hi,

We use a monitoring solution (zabbix). It stores trends data (MySQL 5.X) using a table called trends_uint with the following structure (simplified):

itemid    clock        value_avg
1         1238774400   100
1         1250773900   70
1         1250773200   50

The clock field stores datetime values using Unix time.

I'm trying to extract a report to see the consumption of a monitored item. I thought doing this by selecting the oldest record and the newest record. I achieved it doing the following:

SELECT
(SELECT t.clock FROM trends_uint t WHERE t.itemid = 1 AND t.clock IN (SELECT MIN(t.clock) FROM trends_uint t WHERE t.itemid = 1)) AS minClock,
(SELECT t.value_avg FROM trends_uint t WHERE t.itemid = 1 AND t.clock IN (SELECT MIN(t.clock) FROM trends_uint t WHERE t.itemid = 1)) AS minValueAvg,
(SELECT t.clock FROM trends_uint t WHERE t.itemid = 1 AND t.clock IN (SELECT MAX(t.clock) FROM trends_uint t WHERE t.itemid = 1)) AS maxClock,
(SELECT t.value_avg FROM trends_uint t WHERE t.itemid = 1 AND t.clock IN (SELECT MAX(t.clock) FROM trends_uint t WHERE t.itemid = 1)) AS maxValueAvg,
(SELECT maxClock - minClock) AS timeGap,
(SELECT minValueAvg - maxValueAvg) AS valueGap,
(SELECT timeGap/86400) AS daysGap,
(SELECT valueGap/daysGap/1024/1024) AS consumeMB

My problem is that I have lot's servers from which I want to calculate the consumption. I don't want to change the query for which one (changing the itemid).

The system has another table (items) from which I could extract the items that I need to put into the report. Something like SELECT itemid FROM items WHERE ...

So this is my question: instead of using a hardcoded itemid, is it possible to make it "dynamic", for instance using the returned codes from another query (ex.: SELECT ... FROM ... WHERE itemid IN (SELECT itemid FROM items WHERE ...)?

TIA,

Bob

Answer 1

Yes, that is possible. However, it will SEVERELY decrease your performance.

For one example, see: http://www.1keydata.com/sql/sql-subquery.html

If possible, you should avoid using the IN keyword.

Answer 2

It looks like what you want is to use either your programming language's or MySQL user-defined variables to store the ID you selected once and reuse it.

I'm assuming you're using some sort of a language, say PHP, to wrap around these calls.

You can then:

• do a SELECT on the itemid, save it as $itemid, and then pass in to your SELECT or

• use MySQL user-defined variables and save the itemid, with something like

  SET @itemid=SELECT itemid FROM trends_uint WHERE ....

  SELECT .... WHERE t.itemid = @itemid ....

Answer 3

select id,
min(clock) as minClock,
max(clock) as maxClock,
maxClock - minClock as timeGap,
(timeGap/86400) AS daysGap,
min(value_avg) as minValueAvg,
max(value_avg) as maxValueAvg,
(valueGap/daysGap/1024/1024) AS consumeMB
from trends_uint
group by id;

That should do all of them at once.

In the future, in case you do need something as a loop (though it looks to me like this is better as a single statement with a "group by), the reference is http://dev.mysql.com/doc/refman/5.0/en/flow-control-constructs.html