MySql query taking long time while querying 5s resolution 4 years data-CodePudding

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:

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?

CodePudding user response：

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?

CodePudding user response：

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.