Random number of records on the server's list

[JulioV]

I have the latest version of the server along the latest one of the client. Every time I refresh the study general view page, the number of records for high frequency sensors changes randomly (i.e. the accelerometer records can be 5,020 and then change to 3,982). I only have one device joined to the study and it is collecting accelerometer, battery, wifi and a custom plugin which has a similar refresh rate as the accelerometer. All the other non-intensive sensors and aware info tables are stable and constant.

[denzilferreira]

Are you using InnoDB on your server? When the record number is too great, InnoDB queries report an approximate count. See here: http://stackoverflow.com/questions/22803827/mysql-giving-randomly-count-rows-bad-result-when-table-having-huge-numbers-of-ro

That explains why you are seeing this behavior.

[JulioV]

I just checked and yeah, all the tables are using InnoDB. Do you recommend leaving them like this or are there any advantages of using a different engine?

[denzilferreira]

InnoDB is best for writing speed, not so much for queries. MyISAM is the opposite :) so it really depends on how important is it for you to know the exact number of rows. There is some overhead of using InnoDB in terms of query performance, but from previous experience, InnoDB has proved to be more reliable (in case of server power outage etc).

Just as example, if the idea is to know if a specific device has sent data today or within a given hour, a simple count query with a group by device_id and hour/day is fast and will be > 0 for devices that are uploading the data.

Cheers, D;