Correct composite indexing indexing order

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?

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.

StackExchange Database Administrators Q#292087, answer score: 5