0

I am downloading MySql query. It is taking long time. Long solution I found is, download 2 months data at a time. I don't like to do it almost 25 times same thing. Is there a better way I can query and get more months data if not years data? Information: My data starts on 2018-11-01 and there are many sensors data. I am querying sensor 20 data. It is sunlight value. At night its value will be less than 20 and I am dropping that data as well. I want to select values during daytime only. Hence, final_value>20

My code:

SELECT datetime,sensor_value,sensor_id
FROM sensor.timeseries 
WHERE ((datetime<'2019-01-01') and (datetime> '2018-11-01') and sensor_id in (20) and  sensor_value>20);

Present output:

SELECT datetime,sensor_value,sensor_id
FROM sensor.timeseries 
WHERE ((datetime<'2019-01-01') and (datetime> '2018-11-01') and sensor_id in (20) and  sensor_value>20) 150755 row(s) returned  0.250 sec / 43.625 sec

screenshot: enter image description here

I want to query and store more rows? Also, how can I save this data automatically, not manually? At least this saves some time for me.

Update: based on the below received suggestions:

Query1: I will run the following first

ALTER TABLE sensor.timeseries ADD INDEX new_index (datetime,sensor_id,sensor_value) 

Query2: I will then run the following

SELECT datetime,sensor_value,sensor_id FROM sensor.timeseries  WHERE ((datetime<'2019-01-01') and (datetime> '2018-11-01') and sensor_id in (20) and  sensor_value>20); 

Am I correct?

9
  • 1
    Do you have an index on field measdatetime?
    – Jonathan Hernández
    Jun 2, 2022 at 18:23
  • @JonathanHernández can you specify what mean by index? I just shared a screenshot. Thanks
    – Mainland
    Jun 2, 2022 at 18:36
  • 1
  • 1
    @Mainland start with EXPLAIN SELECT to be able to see what caused such execution delay
    – user973254
    Jun 2, 2022 at 19:41
  • 1
    @user973254 I really appreciate for this wonderful solution. I just ran this and got the following result. ` id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE timeseries ref datetime_ID,modbus_id modbus_id 4 const 15501625 33.31 Using where` The question is, what does it mean? I understand it is trying to check 15 million rows. Am I correct?
    – Mainland
    Jun 2, 2022 at 20:13

3 Answers 3

1

You can change to modbus_readout_id = 20 and try that way. There is another answer about saving output to a file How to save MySQL query output to excel or .txt file?

1
  • That did not help as well.
    – Mainland
    Jun 2, 2022 at 18:35
1

To improve your query performance add a composite index:

CREATE INDEX da_sid_sva
ON timeseries (datetime, sensor_id , sensor_value);

There is no need for sensor_id in (20) , you could simply write sensor_id =20... this will make no performance improvement.

3
  • Query1: I will run the following first ALTER TABLE sensor.timeseries ADD INDEX new_index (datetime,sensor_id,sensor_value)
    – Mainland
    Jun 2, 2022 at 20:27
  • Query2: I will then run the following SELECT datetime,sensor_value,sensor_id FROM sensor.timeseries WHERE ((datetime<'2019-01-01') and (datetime> '2018-11-01') and sensor_id in (20) and sensor_value>20); Am I correct?
    – Mainland
    Jun 2, 2022 at 20:28
  • My queries failed. I tried four combinations of index. All 3 together and separately. All failed with common error 15:47:36 ALTER TABLE sensor.timeseries ADD INDEX new_index(sensor_value) Error Code: 2013. Lost connection to MySQL server during query 30.015 sec
    – Mainland
    Jun 2, 2022 at 20:50
1

Please provide SHOW CREATE TABLE timeseries

Can there ever be two readings for a sensor at exactly the same time? If so, can you avoid such?

Tentatively, you need this schema:

CREATE TABLE timeseries (
    sensor_id SMALLINT UNSIGNED NOT NULL,
    datetime DATETIME NOT NULL,
    sensor_value FLOAT NOT NULL,
    PRIMARY KEY(sensor_id, datetime)
);

150755 row(s) returned

That will take time -- for both performing the query and for shoveling it over the network.

No extra indexes will be beneficial for the query you mentioned. If you do have other queries, or you frequently request data from multiple sensors at the same time, let's see them.

(Minor bug: If a reading can happen exactly at midnight, note that the query as it is written eliminates the first midnight.)

I want to query and store more rows?

INSERT lets you store more rows.

Also, how can I save this data automatically, not manually?

Where does the data come from?

15 million rows

No problem. If you store a billion rows, there are some more things to look at.

ALTER TABLE sensor.timeseries ADD INDEX new_index (datetime, ...

It is almost never useful to have a composite index starting with a DATETIME column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.