Retrieve latest 3 values from a large table Engine MySQL 5.6.37 
I have a continuously growing table (around 10M rows) which keeps the values from devices along with a timestamp. Below is the table structure:

CREATE TABLE `meterreadings` (
 `Id` bigint(20) NOT NULL AUTO_INCREMENT,
 `meterid` varchar(16) DEFAULT NULL,
 `metervalue` int(11) DEFAULT NULL,
 `date_time` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`Id`),
 KEY `meterid` (`meterid`,`date_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


I would like to know if one of the past 3 meter values has crossed zero (for all meter IDs). I tried to use the below query and iterate through all the device IDs using PHP, but this approach is rather slow when I have around 500 meterids to scan:

SELECT COUNT(CASE WHEN metervalue&gt;0 THEN 1 END) AS present 
FROM (SELECT * 
      FROM meterreadings 
      where meterid= ? 
      order by Id desc 
      LIMIT 3) AS a


Is there a better way I can get this done, preferably with one single query and no iteration?
Check:

SELECT meterid /* , IF(SUM(metervalue&gt;0),1,0) flag */
FROM ( SELECT t.*,
              IF(@meterid=t.meterid, 
                 @rownum:=@rownum+1, 
                 @rownum:=1+least(0,@meterid:=t.meterid)) rown 
       FROM meterreadings t, 
            (SELECT @rownum:=1, @meterid:=0) vars
       /* WHERE t.date_time &gt; @some_date_time */
       ORDER BY meterid, date_time DESC
     ) nums
WHERE rown &lt; 4
GROUP BY meterid
HAVING SUM(metervalue&gt;0) &gt; 0 


The resultset is a list of meterid's which have at least 1 positive value within last 3 values. 

Commented WHERE allows to decrease the amount of records processed by setting some "start point".

If you need full list of meterid's with a 'flag' of positive value existence - remove HAVING condition and uncomment flag field in the output list.