Question: How to improve speed of query when using alias

[NoPlayBack]

This is a question: I use queries in mysql to fetch data, and i would like to reference the datapoint via alias. My query looks like this:

SELECT iobroker.ts_number.ts as time, iobroker.ts_number.val as Netz FROM iobroker.ts_number WHERE iobroker.ts_number.id = (SELECT iobroker.datapoints.id from iobroker.datapoints WHERE iobroker.datapoints.name = "power") AND iobroker.ts_number.ts >= 1739401200000 AND iobroker.ts_number.ts <= 1739404800000 ;

All out of sudden this kind of query is not fast any more, it needs 40 sec. Maybe the reason is that i have added new datapoints with lots of data... The reason for using so much time is the query (SELECT iobroker.datapoints.id from iobroker.datapoints WHERE iobroker.datapoints.name = "power") within the query, which should select the correct datapoint-id. When i did introduce this kind of query in query i was told that the optimiser from mysql would fetch the id once and then process the query with the correct number.

What i know is that when i replace the query with the fix number then the respond time is 4 milliseconds.

I would like to try to add an additional index to the table datapoints for testing... i would like to add an index on name. Would this be possible or would it have no effect.... or would it crash the system ?

[Apollon77]

What about just adding your own custom indices to the tables if you need them? The existing indices are optimized for the standard queries that the adapter does itself.

[NoPlayBack]

well yes that is my question.

Can i simply add another index on datapoints.... sorting per name, and then test speed? If that does not help i would delete the index again... keep the original Primary Key on id, and then everything is back to normal.

The question simply is: Does this hurt the normal running adapter ?

[Apollon77]

In fact the adapter will not touch the table structures if the tables exist, so you can modify the tables as you like. maybe stop the adapter when you do the index addition because this usually locks the table and could mean insert delays or such, depending on the number of rows in it.

[NoPlayBack]

Did the test.....

• query as mentioned on first post did need 42,737 sec to deliver 1171 rows.
• stop sql.0 instance
• add key on datapoints.name
• start sql.0 instance
• same query needs 0,003 sec to deliver 1171 rows.

so i will keep that index active.

Still no idea why suddenly the performance did drop ... will make a reboot at some later time and then check speed again without that additional index, just to make sure it is not something bad in addition. But for now i'm happy