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.
`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:
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
What is the difference if I change this index to
In general, will column order matter in composite indexing if my query uses all indexed columns in the where clause?