3

I have a Raspberry Pi I have been using to log household temperature data collected from a bunch of 1-Wire sensors. The data has all been collected into a MySQL database over several years. I use the Raspbian distribution and the default MySQL configuration. I now have a table with over a million records and my Pi is running queries very slowly.

Here is a description of the TemperatureRecords table:

+--------------+----------+------+-----+---------------------+-------+
| Field        | Type     | Null | Key | Default             | Extra |
+--------------+----------+------+-----+---------------------+-------+
| timeRecorded | datetime | NO   | PRI | 0000-00-00 00:00:00 |       |
| sensorName   | char(3)  | NO   | PRI |                     |       |
| tempValue    | float    | YES  |     | NULL                |       |
+--------------+----------+------+-----+---------------------+-------+

I record the date and time, a 3 character sensor name and value for the temperature. I want to run two queries, one to select the current (most recent) temperature of a certain sensor, and one to select the highest temperature that day.

Current temperature example:

SELECT ROUND(tempValue, 1) as tempValue 
FROM TemperatureRecords 
WHERE DATE(timeRecorded) = '2015-01-20' AND HOUR(timeRecorded) = '20' AND sensorName = 'abc' 
ORDER BY timeRecorded DESC LIMIT 1;

Highest temperature example:

SELECT MAX(tempValue) 
FROM TemperatureRecords 
WHERE DATE(timeRecorded) = '2015-01-20' AND sensorName = 'abc';

Unfortunately these can take 10-20 seconds to complete, which is too slow, especially as I want to retrieve data from several sensors to display on a web page at once.

I have tried adding additional indexes to the table, but this has not made any improvement, and I am not sure I understand indexes fully anyway. I also tried using the MySQL configuration for small systems found in /usr/share/doc/mysql-server-5.5/examples/my-small.cnf, which I was told might improve performance but to no avail.

My knowledge of MySQL is somewhat basic. Am I expecting to much from a Raspberry Pi now my data has grown so large, or is there anything I can do to improve my setup?

Frank Elliot
  • 31
  • 1

3 Answers3

3

The culprit is WHERE DATE(timeRecorded) = '2015-01-20' - it calculates DATE() for all rows (whether they match or not) and disallows index use.

Try: WHERE timeRecorded >= '2015-01-20 00:00:00' and timeRecorded < '2015-01-21 00:00:00'

See this SO question for details!

Community
  • 1
  • 1
Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
1

Calling methods like DATE() will get you into trouble immediately. These cannot be indexed and without indexes you're dead in the water.

If you call these frequently, create a DATE column that contains exactly the data you're searching on. You may also need a column to capture the hour information you're extracting.

Database normalization principles apply until you get into a situation like this where the ideal form is unacceptably slow. In that case you need to carefully de-normalize to address those issues. Keeping data like this in sync may be tricky, so be sure to keep all these derived columns up to date whenever a change to the master date is made.

You may have an opportunity here to use the DATETIME as-is if you can narrow it down to a range of time, like:

WHERE timeRecorded BETWEEN '2015-01-20 20:00:00' AND '2015-01-20 20:59:99'

If that works, your existing index will apply.

tadman
  • 208,517
  • 23
  • 234
  • 262
1

I think what you are after is achievable on a Raspberry Pi, you just need to change how your heavy processing queries are handled.

If its historical data, have the data processed daily and saved into another (smaller, quicker) table where it can be quickly referenced by the web page.

Have hourly averages also run only every hour, saved into the separate table.

Have current temperatures placed in a buffer table as well as the record, but only have the web page access the buffer table.

It may not be as accurate but the hopefully the difference will be minor, but you should see a large speed increase

Gordan
  • 21
  • 2