core: Single Entity history works very slow with MySQL

The problem

My history database is 180days. I have a lot of states and now entity history works very slow. Need wait serveral minutes to see it.

I get slow query error to mysql log:

WITH anon_1 AS
         (SELECT anon_2.context_id AS context_id
          FROM (SELECT events.context_id AS context_id
                FROM events LEFT OUTER JOIN event_data ON events.data_id = event_data.data_id
                WHERE events.time_fired > '2022-05-31 19:45:30.212000' AND events.time_fired < '2022-06-01 19:45:29.032544' AND events.event_type IN ('logbook_entry', 'automation_triggered', 'script_started') AND (JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), '$.\"entity_id\"') IN ('\"binary_sensor.vaiku_k_kondicionierius\"') OR JSON_EXTRACT(CAST(events.event_data AS CHAR), '$.\"entity_id\"') IN ('\"binary_sensor.vaiku_k_kondicionierius\"')) UNION ALL SELECT states.context_id AS context_id
                FROM states FORCE INDEX (ix_states_entity_id_last_updated)
                WHERE states.last_updated > '2022-05-31 19:45:30.212000' AND states.last_updated < '2022-06-01 19:45:29.032544' AND states.entity_id IN ('binary_sensor.vaiku_k_kondicionierius')) AS anon_2)
SELECT events.event_id AS event_id, events.event_type AS event_type, events.event_data AS event_data, events.time_fired AS time_fired, events.context_id AS context_id, events.context_user_id AS context_user_id, events.context_parent_id AS context_parent_id, event_data.shared_data AS shared_data, NULL AS state_id, NULL AS state, NULL AS entity_id, NULL AS icon, NULL AS old_format_icon, NULL AS context_only
FROM events LEFT OUTER JOIN event_data ON events.data_id = event_data.data_id
WHERE events.time_fired > '2022-05-31 19:45:30.212000' AND events.time_fired < '2022-06-01 19:45:29.032544' AND events.event_type IN ('logbook_entry', 'automation_triggered', 'script_started') AND (JSON_EXTRACT(CAST(event_data.shared_data AS CHAR), '$.\"entity_id\"') IN ('\"binary_sensor.vaiku_k_kondicionierius\"') OR JSON_EXTRACT(CAST(events.event_data AS CHAR), '$.\"entity_id\"') IN ('\"binary_sensor.vaiku_k_kondicionierius\"')) UNION ALL SELECT NULL AS event_id, NULL AS event_type, NULL AS event_data, states.last_updated AS time_fired, states.context_id AS context_id, states.context_user_id AS context_user_id, states.context_parent_id AS context_parent_id, NULL AS shared_data, states.state_id AS state_id, states.state AS state, states.entity_id AS entity_id, 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 AS icon, 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 AS old_format_icon, NULL AS context_only
FROM states FORCE INDEX (ix_states_entity_id_last_updated) 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
WHERE states.last_updated > '2022-05-31 19:45:30.212000' AND states.last_updated < '2022-06-01 19:45:29.032544' AND old_state.state_id IS NOT NULL AND states.state != old_state.state AND states.state IS NOT NULL AND ((states.entity_id NOT LIKE 'proximity.%' AND states.entity_id NOT LIKE 'sensor.%') OR ((states.entity_id LIKE 'proximity.%' OR states.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 = states.last_changed OR states.last_changed IS NULL) AND states.entity_id IN ('binary_sensor.vaiku_k_kondicionierius') UNION ALL SELECT events.event_id AS event_id, events.event_type AS event_type, events.event_data AS event_data, events.time_fired AS time_fired, events.context_id AS context_id, events.context_user_id AS context_user_id, events.context_parent_id AS context_parent_id, event_data.shared_data AS shared_data, NULL AS state_id, NULL AS state, NULL AS entity_id, NULL AS icon, NULL AS old_format_icon, '1' AS context_only
FROM events LEFT OUTER JOIN event_data ON events.data_id = event_data.data_id
WHERE events.context_id IN (SELECT anon_1.context_id
                            FROM anon_1) UNION ALL SELECT NULL AS event_id, NULL AS event_type, NULL AS event_data, states.last_updated AS time_fired, states.context_id AS context_id, states.context_user_id AS context_user_id, states.context_parent_id AS context_parent_id, NULL AS shared_data, states.state_id AS state_id, states.state AS state, states.entity_id AS entity_id, NULL AS icon, NULL AS old_format_icon, '1' AS context_only
FROM states
WHERE (states.entity_id NOT IN ('binary_sensor.vaiku_k_kondicionierius')) AND states.context_id IN (SELECT anon_1.context_id
                                                                                                    FROM anon_1) ORDER BY time_fired;

Query explain from mysql server. It try select all records in table (62167196).

What version of Home Assistant Core has the issue?

core-2022.6.0

What was the last working version of Home Assistant Core?

core-2022.5.x

What type of installation are you running?

Home Assistant Container

Integration causing the issue

No response

Link to integration documentation on our website

No response

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

No response

Additional information

No response