information tables such as GLOBAL_STATUS. This can be used to report certain performance metrics, such as the number of queries processed per second:
SELECT MAX( -- use MAX to force aggregation
IF(variable_name='Questions' -- no. of queries sent to server
, CAST(variable_value AS unsigned) -- make integer value
, 0 -- ignore if not 'Questions'
)
)
/ -- divide by
MAX( -- use MAX to force aggregation
IF(variable_name='Uptime' -- no. of seconds the server is up
, CAST(variable_value AS unsigned) -- make integer value
, 0 -- ignore if not 'Uptime'
)
) AS queries_per_second
FROM information_schema.GLOBAL_STATUS
WHERE variable_name in ('Questions', 'Uptime');
(This snippet is maintained on MySQL Forge)
This query could have been solved in a number of other ways involving subqueries, for example like this:
SELECT (SELECT CAST(variable_value AS unsigned)
FROM information_schema.GLOBAL_STATUS
WHERE variable_name = 'Questions')
/ (SELECT CAST(variable_value AS unsigned)
FROM information_schema.GLOBAL_STATUS
WHERE variable_name = 'Uptime') as queries_per_second
For now I'm sticking to my original snippet. The disadvantage to this approach is that it requires a hack to force aggregation of all rows from
GLOBAL_STATUS to one single row (using MAX), as well as some conditional expressions. The disadvantage of a using subqueries is that it would be slighly more verbose, and it would also require more scanning of
information_schema.GLOBAL_STATUS, although I am unsure if that would really matter that much.What do you think?