3

I have MySQL (MariaDB) database. I have a table 'sensors' ,which collects data from IoT devices.

Each device may have 4-6 parameters that it records, like temperature, humidity, air quality, etc. Each device sends a measurement once every minute.

There are 10-15 such devices. Each device has its deviceid.

Six columns:

`autoid`(INT,AUTOICREMENT)
`deviceid`(varchar)
`pname`(varchar)         /* name of parameter like temperature,humidty */
`pcode`(INT)             /* code for each parameter like for temperature its 11,humidty its 12 etc */
`datavalue`(double)      /* value of parameter */
`rectime`(INT)           /* UNIX timestamp */

Here is sample of table data:

autoid deviceid pname pcode datavalue rectime
1 sdbjs4b temp 11 30.54 1621702300
2 sdbjs4b hum 12 104 1621702300
3 sdbjs4b gas 13 768 1621702300
4 vsf5bjs temp 11 31.45 1621702300
5 vsf5bjs volt 15 5.10 1621702300

There are almost 4-5 million rows in the sensors table.

My query requirements: I have to get data for some arbitrary time values for each day for each device and parameter.

Here is query that is used:

SELECT * from sensors where deviceid =? AND  pcode = ? AND rectime =?

This is guaranteed to give me only one result. The problem is I need to run this query inside nested loops, worst case 500 times. Why I need looping?. I need to create a report between two dates for each device, parameter, and for a given set of time slots between two dates. I have to loop for values of time slots.

I have a composite index on (deviceid,rectime,pcode).

What is the difference if I change this index to (rectime,deviceid,pcode)?

In general, will column order matter in composite indexing if my query uses all indexed columns in the where clause?

0
5

I think your approach with nested loops is suboptimal.

Why can't you do something like:

select * from sensors where deviceid =? AND  pcode = ? AND rectime between ? and ?

This would return the whole dataset and you could process it locally. Selecting 500 or even more rows in one correct select is better then 500 single row selects.

In this case I would change the the clustered index to

(deviceid,pcode,rectime)

you can still keep the autoId primary key, just make it nonclustered.

In addition, the way your select works, it has to do key lookup afterwards to get one single extra row. MySQL AFAIK doesn't support include on indexes, but even then, it would be a de facto reordered copy of the table so clustered index makes sense.

As for if the order matters.... Well, yes and no. Given your datatypes and size of table, the difference would be minimal.

5
  • Good answer, covers everything I can think of. The only thing I'd say is even though a few million records isn't a lot, it's possible there might be a small measurable difference of performance when the fields in the index are ordered most optimally based on the cardinality of the data. @ranjitthorat would have to test the different combinations based on his data, but my guess is if his query uses a predicate value for a specific day at a time, then the rectime field might make the most sense to be first in the index definition.
    – J.D.
    May 23 at 13:45
  • 1
    @j.d. i agree. The iasue is this depends on usage and other if any queries. May 23 at 14:45
  • 1
    If the query does end up using a between filter on the date column, then the optimal index would have this column at the end of the index. If it was anywhere else, the columns to the right of it would not be usable as seek conditions (so you'd end up reading the full days worth of data when you only need to read the days worth of data for a particular sensor). May 23 at 19:44
  • @AndrewSayer Yes. But if you want all the data for a particular da May 23 at 22:41
  • @J.D. - No, rectime = constant does not benefit from having rectime before the other = columns in the index. Cardinality applies to the entire index as if the columns were concatenated together. (More in my Answer.)
    – Rick James
    May 24 at 1:46
1

Consider trying to make these changes:

  • One row per minute.
  • The various metrics are in columns, not rows.
  • The Primary key is the time, truncated to the minute. (No AUTO_INCREMENT column is needed.)
  • INSERT .. ON DUPLICATE KEY UPDATE .. is used for each of the 4-6 updates, the first of which will be an insert. (That is, it does not matter which device gets there first.
  • The metrics are DEFAULT NULL.

No looping. I think your desired query can be performed in a single query. Example:

SELECT temp, humidity
    FROM metrics
    WHERE device = ?
      AND rectime >= ?
      AND rectime  < ? + INTERVAL 1 MONTH

will get two readings from one device over a period of a month (assuming the last two "?" are the same).

This is likely to be optimal, both for the updates and for many queries:

PRIMARY KEY(device, rectime)

If you are testing against a date range, rectime must be after the columns being tested with =. This is not about cardinality, it is about equality versus range.

Furthermore, for multiple columns tested with = cardinality does not impact the optimal order of such columns in the index.

I used that way of testing a date/datetime range because BETWEEN includes both end-points. (This is a mistake a lot of people make.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.