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?

Mainland
  • 2,798
  • 1
  • 12
  • 27
  • 1
    Do you have an index on field measdatetime? – Jonathan Hernández Jun 02 '22 at 18:23
  • @JonathanHernández can you specify what mean by index? I just shared a screenshot. Thanks – Mainland Jun 02 '22 at 18:36
  • 1
    Here are some useful references for MySQL indexes: https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html, https://www.w3schools.com/mysql/mysql_create_index.asp, https://www.mysqltutorial.org/mysql-index/, http://mysql.rjweb.org/doc.php/index_cookbook_mysql – WOUNDEDStevenJones Jun 02 '22 at 18:51
  • @WOUNDEDStevenJones can you specify what is index in my case? – Mainland Jun 02 '22 at 18:59
  • 1
    @Mainland start with `EXPLAIN SELECT` to be able to see what caused such execution delay –  Jun 02 '22 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 02 '22 at 20:13
  • @WOUNDEDStevenJones Query1: I will run the following first `ALTER TABLE sensor.timeseries ADD INDEX new_index (datetime,sensor_id,sensor_value)` – Mainland Jun 02 '22 at 20:29
  • @WOUNDEDStevenJones Query2: I will then run the following `SELECT datetime,sensor_value,sensor_id FROM sensor.timeseries WHERE ((datetime '2018-11-01') and sensor_id in (20) and sensor_value>20);` Am I correct? – Mainland Jun 02 '22 at 20:30
  • @JonathanHernández I have updated my question about creating `index`. Can you confirm will this is correct? – Mainland Jun 02 '22 at 20:38

2 Answers2

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?

Aleksandr Latman
  • 9
  • 3
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.

Ergest Basha
  • 4,865
  • 4
  • 5
  • 26
  • Query1: I will run the following first `ALTER TABLE sensor.timeseries ADD INDEX new_index (datetime,sensor_id,sensor_value)` – Mainland Jun 02 '22 at 20:27
  • Query2: I will then run the following `SELECT datetime,sensor_value,sensor_id FROM sensor.timeseries WHERE ((datetime '2018-11-01') and sensor_id in (20) and sensor_value>20);` Am I correct? – Mainland Jun 02 '22 at 20:28
  • 1
    @Mainland Query1: The syntax in my answer is the same as you are suggesting in the comment, practically they do the same. Query2. Yes, even though you have some extra parenthesis .. use `SELECT datetime,sensor_value,sensor_id FROM sensor.timeseries WHERE datetime '2018-11-01' and sensor_id =20 and sensor_value>20;` – Ergest Basha Jun 02 '22 at 20:41
  • 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 02 '22 at 20:50
  • @Mainland please check the suggestion in [this answer](https://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query) for the lost connection error – Ergest Basha Jun 02 '22 at 20:56