Mysql – Retrieve latest 3 values from a large table

greatest-n-per-groupMySQLperformancequery-performance

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>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?

Best Answer

Check:

SELECT meterid /* , IF(SUM(metervalue>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 > @some_date_time */
       ORDER BY meterid, date_time DESC
     ) nums
WHERE rown < 4
GROUP BY meterid
HAVING SUM(metervalue>0) > 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.

Related Solutions

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

Mysql – #1093 – You can’t specify target table ‘R’ for update in FROM clause

Based on the previous question and this, the following should do what you want. As with Pieter's update it doesn't assume that the ID's are in sequential order, rather it uses the ReqTime to determine order.

UPDATE Visits R
  INNER JOIN (
    SELECT ID,
      TIME_TO_SEC(TIMEDIFF(
        (SELECT B.ReqTime
         FROM Visits B
         WHERE B.ReqTime > A.ReqTime
         ORDER BY B.ReqTime
         LIMIT 1
         )
        ,A.ReqTime)) Duration
    FROM Visits A
   ) N ON R.ID = N.ID
SET R.Duration = N.Duration
WHERE Duration IS NULL;

Best Answer

Related Solutions

Mysql – Adding index to large thesql tables

Mysql – #1093 – You can’t specify target table ‘R’ for update in FROM clause

Related Question