Question 20278 in Mysql Question posted in
The official MySQL documentation can be found here.

Getting only fully filled rows from subqueries – Mysql

id Plot_name Measurement firmware_revision
1 InboundTraffic1Byte 300 1.5
2 InboundTraffic500Bytes 200 1.5
3 InboundTraffic1000Bytes 100 1.5
4 InboundTraffic1Byte 1.6
5 InboundTraffic500Bytes 200 1.6
6 InboundTraffic1000Bytes 100 1.6

A MySQL database stores measurements from performance tests, and there is a Grafana which has some queries to get the specific data for plots. I have a single plot for inbound traffic that shows all 3 inbound measurements for 1B, 500B and 1000B for each firmware_revision. So 3 lines on the plot.

In Grafana I need to get columns with firmware_revision and each measurement type:

firmware_revision inbound_1B inbound_500B inbound_1000B
1.5 300 100 200
1.6 200 100

I get it with this:

SELECT 
  firmware_revision, 
  (SELECT Measurement FROM db WHERE Plot_name like "InboundTraffic1Byte") as inbound_1B,
  (SELECT Measurement FROM db WHERE Plot_name like "InboundTraffic500Byte") as inbound_500B,
  (SELECT Measurement FROM db WHERE Plot_name like "InboundTraffic1000Byte") as inbound_1000B,
FROM 
  db
ORDER BY
  id

I’d like to get rid of the rows that have any of the measurements missing. So the whole row with firmware_revision 1.6 should be gone.
I add WHERE to the code above:

WHERE firmware_revision in (
    SELECT firmware_revision FROM db WHERE ... AND ... AND ... )

In the … I have a query that checks if firmware_revision is in each of the measurements.
If any is missing, this row is skipped.

This is very slow. What is a better way of having this initial data for Grafana without using subqueries?

Tags:

2

Answers


  1. 0

    Is there a better way of having this inital data for Grafan without
    using subqueries

    You can do it using CASE EXPRESSION, which is faster than Subqueries :

    SELECT 
  firmware_revision, 
  MAX(CASE WHEN Plot_name = 'InboundTraffic1Byte' THEN Measurement END) as inbound_1B,
  MAX(CASE WHEN Plot_name = 'InboundTraffic500Byte' THEN Measurement END) as inbound_500B,
  MAX(CASE WHEN Plot_name = 'InboundTraffic1000Byte' THEN Measurement END) as inbound_1000B
FROM 
  db
GROUP BY
  firmware_revision
HAVING 
  inbound_1B IS NOT NULL AND inbound_500B IS NOT NULL AND inbound_1000B IS NOT NULL;
    Login or Signup to reply.
  2. 0

    Slight alternative approach is to

    • first filter out records that are not null
    • apply the pivot (MAX + CASE expressions)
    • check when the count of records is 3
    SELECT firmware_revision,
       MAX(CASE WHEN Plot_name REGEXP '.*1Byte$'    
                THEN Measurement END) AS inbound_1B,
       MAX(CASE WHEN Plot_name REGEXP '.*500Bytes$'  
                THEN Measurement END) AS inbound_500B,
       MAX(CASE WHEN Plot_name REGEXP '.*1000Bytes$' 
                THEN Measurement END) AS inbound_1000B
FROM tab       
WHERE Measurement IS NOT NULL
GROUP BY firmware_revision
HAVING COUNT(id) = 3

    Output:

    firmware_revision inbound_1B inbound_500B inbound_1000B
    1.5 300 200 100

    Check the demo here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search