Search code examples
mysqlsqlsubqueryquery-optimization

Getting only fully filled rows from subqueries

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?

Solution

  • 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;