Re: Slow Query

[Rick James]

groupwise max:

SELECT *
    FROM (
        SELECT  SQL_BIG_RESULT
            d.Descriptor,
            r.Device_Address, 
            r.Reading_Date,
            r.Calibration_Date, 
            CASE WHEN r.Low_Battery = 0 then 'FALSE' ELSE 'TRUE' END as Low_Battery,
            CASE WHEN r.Calibration_Over_Due = 0 then 'FALSE' ELSE 'TRUE' END as Calibration_Over_Due
        FROM  readings AS r
        JOIN  descriptors AS d ON r.device_address = d.device_address
        ORDER BY r.Device_Address, r.Reading_Date DESC
      )
    GROUP BY Device_Address
    ORDER BY Device_Address

r should have
INDEX(Device_Address, Reading_Date)

There are lots of Reading_Dates for each Device_Address, correct?

Since you have only one minor use for d, this might be even better:

SELECT  sq2.Device_Address,
        d.Descriptor ,
        sq2.Reading_Date,
        sq2.Calibration_Date, 
        CASE WHEN sq2.Low_Battery = 0 then 'FALSE' ELSE 'TRUE' END as Low_Battery,
        CASE WHEN sq2.Calibration_Over_Due = 0 then 'FALSE' ELSE 'TRUE' END as Calibration_Over_Due
    FROM (
        SELECT *
            FROM (
                SELECT
                        Device_Address, 
                        Reading_Date,
                        Calibration_Date, 
                        Low_Battery,
                        Calibration_Over_due
                    FROM  readings
                    ORDER BY Device_Address, Reading_Date DESC
                  ) sq1
            GROUP BY Device_Address
         ) sq2
    JOIN  descriptors AS d ON sq2.device_address = d.device_address
    ORDER BY sq2.Device_Address

So, you ask, what the heck is going on?

1. sq1 gathers the needed information from `readings`. And it sorts the rows in the order needed for the next step.

2. sq2 plays a trick with GROUP BY. Since you don't really have aggregates, GROUP BY simply takes the 'first' row of for each `Device_Address`, which, because of the previous ORDER BY, will be the one with the MAX(Reading_Date). This step pares down the amount of data to one row per Device_Address.

3. Now, and only now, JOIN to `descriptors` to get Descriptor. The fetch into descriptors is somewhat costly since it is a random probe. But we are doing it only the necessary number of times. And finally do the desired ORDER BY.