Very slow SQL Queries on MySQL 9 #146230
Description
The problem
Some SQL queries take long time and make logbook extremely slow.
What version of Home Assistant Core has the issue?
core-2025.5.3
What was the last working version of Home Assistant Core?
No response
What type of installation are you running?
Home Assistant Container
Integration causing the issue
recorder
Link to integration documentation on our website
No response
Diagnostics information
I'm currently running the database hosted on Oracle Heatwave MySQL version 9.3.1-u1-cloud.
- The
historyworks fine and returns data as expected. - However, the
logbookconsistently hangs or takes several minutes to respond. - This issue seems similar to what was reported in #137178.
Environment
- Database: Oracle Heatwave MySQL 9.3.1-u1-cloud
- Home Assistant version: core-2025.5.3
Additional Info
When I tried to test with core-2024, I encountered the following error, which prevented testing:
ERROR 3854 (HY000): Cannot convert string '\x97B:\\xDA\xD3...' from utf8mb4 to utf8mb3
Observations
Running SHOW PROCESSLIST; shows long-running queries when accessing the logbook:
MySQL [(none)]> SHOW PROCESSLIST;
+-------+-----------------+-------------------------------------------------------+---------------+---------+--------+-----------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-----------------+-------------------------------------------------------+---------------+---------+--------+-----------------------------+------------------------------------------------------------------------------------------------------+
| 24 | system user | | NULL | Daemon | 238976 | Suspending | NULL |
| 23 | event_scheduler | localhost | NULL | Connect | 10 | Waiting for next activation | NULL |
| 28 | ociadmin | localhost:57834 | NULL | Sleep | 4 | | NULL |
| 14534 | 3735943886 | armhub.subnet12181007.vcn12181007.oraclevcn.com:35228 | NULL | Query | 0 | executing | SHOW PROCESSLIST |
| 404 | ociadmin | localhost:53522 | NULL | Sleep | 414 | | NULL |
| 14710 | 3735943886 | armhub.subnet12181007.vcn12181007.oraclevcn.com:32976 | homeassistant | Query | 3 | waiting for handler commit | COMMIT |
| 14712 | 3735943886 | armhub.subnet12181007.vcn12181007.oraclevcn.com:35690 | homeassistant | Sleep | 19 | | NULL |
| 14713 | 3735943886 | armhub.subnet12181007.vcn12181007.oraclevcn.com:39926 | homeassistant | Query | 44 | executing | WITH anon_1 AS (SELECT anon_2.context_id_bin AS context_id_bin FROM (SELECT events.context_id_bin |
| 14714 | 3735943886 | armhub.subnet12181007.vcn12181007.oraclevcn.com:39934 | homeassistant | Sleep | 7 | | NULL |
| 14715 | 3735943886 | armhub.subnet12181007.vcn12181007.oraclevcn.com:39950 | homeassistant | Sleep | 3 | | NULL |
+-------+-----------------+-------------------------------------------------------+---------------+---------+--------+-----------------------------+------------------------------------------------------------------------------------------------------+
10 rows in set (0.001 sec)
MySQL [(none)]>
The problematic query looks like WITH anon_1 AS (SELECT anon_2.context_id_bin AS context_id_bin FROM (SELECT events.context_id_bin AS context_id_bin FROM events LEFT OUTER JOIN event_types ON events.event_type_id = event_types.event_type_id LEFT OUTER JOIN event_data ON events.data_id = event_data.data_id WHERE events.time_fired_ts > 1749074375.471e0 AND events.time_fired_ts < 1749160775.522281e0 AND events.event_type_id IN (18, 17, 25) AND (JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), '$.\"entity_id\"') IS NOT NULL AND CAST(JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), '$.\"entity_id\"') AS CHAR) IN ('\"binary_sensor.smart_presence_sensor_24100968862444662001c4e7ae0dbb19_sensor_presence_motion\"') OR JSON_EXTRACT(CAST(events.event_data AS CHAR), '$.\"entity_id\"') IS NOT NULL AND CAST(JSON_EXTRACT(CAST(events.event_data AS CHAR), '$.\"entity_id\"') AS CHAR) IN ('\"binary_sensor.smart_presence_sensor_24100968862444662001c4e7ae0dbb19_sensor_presence_motion\"')) UNION ALL SELECT states.context_id_bin AS context_id_bin FROM states FORCE INDEX (ix_states_metadata_id_last_updated_ts) WHERE states.last_updated_ts > 1749074375.471e0 AND states.last_updated_ts < 1749160775.522281e0 AND states.metadata_id IN (753)) AS anon_2 GROUP BY anon_2.context_id_bin) SELECT events.event_id AS row_id, event_types.event_type AS event_type, CASE WHEN (event_data.shared_data IS NULL) THEN events.event_data ELSE event_data.shared_data END AS event_data, events.time_fired_ts AS time_fired_ts, events.context_id_bin AS context_id_bin, events.context_user_id_bin AS context_user_id_bin, events.context_parent_id_bin AS context_parent_id_bin, NULL AS state, NULL AS entity_id, NULL AS icon, NULL AS context_only FROM events LEFT OUTER JOIN event_types ON events.event_type_id = event_types.event_type_id LEFT OUTER JOIN event_data ON events.data_id = event_data.data_id WHERE events.time_fired_ts > 1749074375.471e0 AND events.time_fired_ts < 1749160775.522281e0 AND events.event_type_id IN (18, 17, 25) AND (JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), '$.\"entity_id\"') IS NOT NULL AND CAST(JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), '$.\"entity_id\"') AS CHAR) IN ('\"binary_sensor.smart_presence_sensor_24100968862444662001c4e7ae0dbb19_sensor_presence_motion\"') OR JSON_EXTRACT(CAST(events.event_data AS CHAR), '$.\"entity_id\"') IS NOT NULL AND CAST(JSON_EXTRACT(CAST(events.event_data AS CHAR), '$.\"entity_id\"') AS CHAR) IN ('\"binary_sensor.smart_presence_sensor_24100968862444662001c4e7ae0dbb19_sensor_presence_motion\"')) UNION ALL SELECT states.state_id AS row_id, NULL AS event_type, NULL AS event_data, states.last_updated_ts AS time_fired_ts, states.context_id_bin AS context_id_bin, states.context_user_id_bin AS context_user_id_bin, states.context_parent_id_bin AS context_parent_id_bin, states.state AS state, states_meta.entity_id AS entity_id, CASE WHEN (JSON_EXTRACT(CAST(state_attributes.shared_attrs AS CHAR), '$.\"icon\"') IS NULL) THEN CASE JSON_EXTRACT(CAST(states.attributes AS CHAR), '$.\"icon\"') WHEN 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(CAST(states.attributes AS CHAR), '$.\"icon\"')) END ELSE CASE JSON_EXTRACT(CAST(state_attributes.shared_attrs AS CHAR), '$.\"icon\"') WHEN 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(CAST(state_attributes.shared_attrs AS CHAR), '$.\"icon\"')) END END AS icon, NULL AS context_only FROM states FORCE INDEX (ix_states_metadata_id_last_updated_ts) LEFT OUTER JOIN states AS old_state ON states.old_state_id = old_state.state_id LEFT OUTER JOIN state_attributes ON states.attributes_id = state_attributes.attributes_id LEFT OUTER JOIN states_meta ON states.metadata_id = states_meta.metadata_id WHERE states.last_updated_ts > 1749074375.471e0 AND states.last_updated_ts < 1749160775.522281e0 AND old_state.state_id IS NOT NULL AND states.state != old_state.state AND states.state IS NOT NULL AND ((states_meta.entity_id NOT LIKE 'counter.%' AND states_meta.entity_id NOT LIKE 'proximity.%' AND states_meta.entity_id NOT LIKE 'sensor.%') OR ((states_meta.entity_id LIKE 'sensor.%') AND (state_attributes.shared_attrs NOT LIKE '%\"unit_of_measurement\":%' OR states.attributes NOT LIKE '%\"unit_of_measurement\":%'))) AND (states.last_updated_ts = states.last_changed_ts OR states.last_changed_ts IS NULL) AND states.metadata_id IN (753) UNION ALL SELECT events.event_id AS row_id, event_types.event_type AS event_type, CASE WHEN (event_data.shared_data IS NULL) THEN events.event_data ELSE event_data.shared_data END AS event_data, events.time_fired_ts AS time_fired_ts, events.context_id_bin AS context_id_bin, events.context_user_id_bin AS context_user_id_bin, events.context_parent_id_bin AS context_parent_id_bin, NULL AS state, NULL AS entity_id, NULL AS icon, '1' AS context_only FROM anon_1 LEFT OUTER JOIN events FORCE INDEX (ix_events_context_id_bin) ON anon_1.context_id_bin = events.context_id_bin LEFT OUTER JOIN event_types ON events.event_type_id = event_types.event_type_id LEFT OUTER JOIN event_data ON events.data_id = event_data.data_id UNION ALL SELECT states.state_id AS row_id, NULL AS event_type, NULL AS event_data, states.last_updated_ts AS time_fired_ts, states.context_id_bin AS context_id_bin, states.context_user_id_bin AS context_user_id_bin, states.context_parent_id_bin AS context_parent_id_bin, states.state AS state, states_meta.entity_id AS entity_id, NULL AS icon, '1' AS context_only FROM anon_1 LEFT OUTER JOIN states FORCE INDEX (ix_states_context_id_bin) ON anon_1.context_id_bin = states.context_id_bin LEFT OUTER JOIN states_meta ON states.metadata_id = states_meta.metadata_id ORDER BY time_fired_ts
Example YAML snippet
Anything in the logs that might be useful for us?
Additional information
No response