High cpu usage by mysql query

Add an index:

 ... ON (deviceId, lastPositionTime, speed) 

and change the query. Replace this predicate:

TIMESTAMPDIFF(MINUTE, lastPositionTime,now()) <= '5'

with this:

lastPositionTime >= NOW() + INTERVAL -5 MINUTE

This will give you a covering index for the query (the EXPLAIN will show "Using index") which means the query can be satisfied from the index.

You want deviceId column first, because MySQL can optimize the GROUP BY operation using an index with that column as the leading column (avoids a sort operation).

You want a predicate on the bare lastPositionTime column, so MySQL can do an index range scan. When that column is buried inside a function (e.g. TIMESTAMPDIFF), MySQL has to evaluate that function for every single row. The index range scan is faster because it can eliminate the function evaluation on a whole lot of rows.

Since there's also a predicate on the speed column, you want that in the index as well.

There are no other columns referenced, so the query can be satisfied entirely from the index, without having to visit pages in the underlying table.

Here is my approach, and it should blow through the table ONCE with no joins using MySQL variables. The premise I'm applying here is as follows. The ordered results must be that of the device ID and the time reported. The MySQL variables will track... Am I working on the same device as the last record?, AND, is the current record within 5 minutes of the last record that either started the device, or last time over the given speed limit being broken and here's why. Lets say I have the a given device reporting over a 15 minute period, starting at 8:00 am, and reported every minute for simplistic purposes. The speeds are as follows:

8:00   58  -- Start group 8:00, set max time to still consider as 8:05
8:01   60  -- speeding... within the 8:00-8:05 range.  NEW end 5 minutes from now 8:06
8:02   58  -- not speeding
8:03   58  -- not speeding
8:04   59  -- not speeding
8:05   58  -- not speeding
8:06   59  -- not speeding... end of the 8:06 range, 1 over limit, ignore this
8:07   60  -- NEW cycle for device, start at 8:07 set ending time to 8:12 -- SPEEDING 1
8:08   61  -- STILL speeding max 61, extend ending time from 8:08 + 5 minutes to 8:13
8:09   62  -- STILL speeding max 62, extend ending time from 8:09 + 5 minutes to 8:14
8:10   59  -- not speeding
8:11   59  -- not speeding
8:12   60  -- SPEEDING AGAIN, within the 8:14 cutoff... reset 5 minutes from now 8:17
8:13   62  -- speeding still, extend to 8:18
8:14   64  -- speeding still, new max 64 and extend to 8:19
8:15   62  -- still speeding... total times 7 with max of 64 (so far)

if it is not considered in the above format, you would have to count all records from

8:00 to 8:05
8:01 to 8:06
8:02 to 8:07, etc.

For the sample data, you would then get speeding from

8:05 to 8:09
8:06 to 8:10
8:07 to 8:11, etc 

but this way keeps looking at a rolling 5 minute cutoff from the last SPEEDING time. if for example, between 8:20-8:26 the person cuts back to LESS than 60, then a new cycle would begin at the next time speeding was encountered. Otherwise, consider how many records would be returned if a person was speeding from 8:00 to 10:00. You would have 2*60 records IF the reporting was 1 per minute... if reporting is more frequent, it would show up even more.

As the device ID changes, the "first Time" identifies the beginning of the next cycle of speeding consideration. After each record is processed, that Device ID is put into the "@lastDevice" variable for the next cycle compare. The @nextTimeCutoff will either be the same as the previous row if no speeding encountered on the current record, or ADD 5 MINUTES regardless of device... if the device is changing as it's the last for an entry, the next row will be starting a new "LimitSequence" incremented by 1 anyhow.

Now, all that said, this query will simplify items for you. If you KNOW the speed limit you are concerned about (60kph), my inner query is prequerying ONLY those records that a device is marked as speeding. If you have 2M records to go through, and MOST of them are NOT speeding, then why even BOTHER with them. So, the inner query is qualifying ONLY those that are speeding and puts them in the proper order for mysql variable processing. It will handle the rolling time period from the first time the person was speeding as described above.

select
      r1.DeviceID,
      @overLimitSeq := if( r1.DeviceID = @lastDevice 
                        AND r1.lastPositionTime <= @nextTimeCutoff,
                        @overLimitSeq, @overLimitSeq +1 ) as LimitSequence,
      min( r1.lastPositionTime ) as SpeedingTimeStart,
      max( r1.lastPositionTime ) as SpeedingTimeEnd,
      max( r1.speed ) as MaxSpeed,
      count(*) as TimesOverLimit,
      @lastDevice := r1.DeviceID as CompareForNextID,
      @nextTimeCutoff := r1.lastPositionTime + interval 5 minute as NextCutoff
   from
      ( select rp.deviceID,
               rp.lastPositionTime,
               rp.speed
           from
              regPosition rp
           where
              rp.speed >= 60
           order by
              rp.deviceID,
              rp.lastPositionTime ) r1,
      ( select @lastDevice := 0,
               @overLimitSeq := 0,
               @nextTimeCutoff := now() ) sqlvars
   group by
      r1.DeviceID,
      LimitSequence
   having
      TimesOverLimit > 2
   order by
      r1.deviceID,
      r1.lastPositionTime