MySQL query time too long in sensor timestamped data table

0 votes
I have a very simple table to log reading from sensors. There's a column for sensor id number, one for sensor reading and one for the timestamp. This column is of SQL type Timestamp. There's a big amount of data in the table, a few million rows.

When I query for all rows before a certain timestamp with a certain sensor id number, sometimes it can take a very long time. If the timestamp is far in the past, the query is pretty fast but, if it's a recent timestamp, it can take up to 2 or 3 seconds.

It appears as if the SQL engine is iterating over the table until it finds the first timestamp that's larger than the queried timestamp. Or maybe the larger amount of queried data slows it down, I don't know.

In any case, I'm looking for design suggestions here, specifically to address to points: why is it so slow? and how can I make it faster?

Is there any design technique that could be applied here? I don't know much about SQL, maybe there's a way to let the SQL engine know the data is ordered (right now it's not but I could order it upon insertion I guess) and speed up the query. Maybe I should change the way the query is done or change the data type of the timestamp column.
Oct 12, 2018 in IoT (Internet of Things) by Upasana
• 8,430 points
25 views

1 answer to this question.

0 votes

Use EXPLAIN to see the execution plan, and verify that the query is using a suitable index. If not, verify that appropriate indexes are available.

An INDEX is stored "in order", and MySQL can make effective of use with some query patterns. (An InnoDB table is also stored in order, by the cluster key, which is the PRIMARY KEY of the table (if it exists) or the first UNIQUE KEY on non-NULL columns.)

With some query patterns, by using an index, MySQL can eliminate vast swaths of rows from being examined. When MySQL can't make user of an index (either because a suitable index doesn't exist, or because the query has constructs that prevent it), the execution plan is going to do a full scan, that is, examine every row in the table. And when that happens with very large tables, there's a tendency for things to get slow.

EDIT

Q: Why is it so slow?

A: There are several factors that affect the elapsed time. It could be contention, for example, an exclusive table lock taken by another session, or it could be time for I/O (disk reads), or a large "Using filesort" operation. Time for returning resultset over a slow network connection.

It's not possible to diagnose the issue with the limited information provided. We can only provide some suggestions about some common issue.

Q: How can I make it faster?

A: It's not possible to make a specific recommendation. We need to figure out where and what the bottleneck is, and the address that.

Take a look at the output from EXPLAIN to examine the execution plan. Is an appropriate index being used, or is it doing a full scan? How many rows are being examined? Is there "Using filesort" operation? et al.

Q: Is there any design technique that could be applied here?

A: In general, having an appropriate index available, and carefully crafting the SQL statement so the most efficient access plan is enabled.

Q: Maybe I should change the way the query is done

A: Changing the SQL statement may improve performance, that's a good place to start, after looking at the execution plan... can the query be modified to get a more efficient plan?

Q: or change the data type of the timestamp column.

A: I think it's very unlikely that changing the datatype of the TIMESTAMP column will improve performance. That's only 4 bytes. What would you change it to? Using DATETIME would take 7 bytes.

In general, we want the rows to be as short as possible, and to pack as many rows as possible into a block. Its also desirable to have the table physically organized in a way that queries can be satisfied from fewer blocks... the rows the query need are found in fewer pages, rather than the rows being scattered onesy-twosy over a large number of pages.

With InnoDB, increasing the size of the buffer pool may reduce I/O.

And I/O from solid state drives (SSD) will be faster than I/O from spinning hard disks (HDD), and this especially true if there is I/O contention on the HDD from other processes.

answered Oct 12, 2018 by Annie97
• 2,190 points

Related Questions In IoT (Internet of Things)

0 votes
1 answer

Detecting when a sensor is not sending data to Orion CB in FiWARE

Orion in itself has no such mechanism ...READ MORE

answered Nov 21, 2018 in IoT (Internet of Things) by Shubham
• 13,190 points
25 views
0 votes
1 answer

Display time in a Windows Core IoT app with a clock!

It is possible, but you should understand ...READ MORE

answered Jul 9, 2018 in IoT (Internet of Things) by nirvana
• 3,060 points
75 views
0 votes
1 answer

How to read data from MQTT in Eclipse Paho?

You don't read data from a MQTT ...READ MORE

answered Aug 9, 2018 in IoT (Internet of Things) by anonymous2
• 4,260 points
60 views
0 votes
1 answer

What is the time taken by a 200 byte message for transmission in a beacon-enabled network?

Now, data rates of IEEE 802.15.4 are ...READ MORE

answered Aug 23, 2018 in IoT (Internet of Things) by Upasana
• 8,430 points
32 views
0 votes
1 answer

Want a command to be executed in Salt only if a directory is empty

You should consider using this inside your ...READ MORE

answered Jun 12, 2018 in DevOps Tools by Damon Salvatore
• 5,490 points
32 views
0 votes
1 answer

Creating A New MySQL User In Amazon RDS Environment

AWS RDS security groups documentation (a common ...READ MORE

answered Jul 18, 2018 in AWS by Priyaj
• 56,160 points
90 views
0 votes
1 answer

Using conditional to execute a command in salt

You can use unless inside your state ...READ MORE

answered Jul 23, 2018 in Other DevOps Questions by DareDev
• 6,810 points
54 views
0 votes
1 answer
0 votes
1 answer

Identification of vulnerable code in an IoT node

The software running on a device is ...READ MORE

answered Aug 30, 2018 in IoT (Internet of Things) by Annie97
• 2,190 points
18 views