Differences

This shows you the differences between two versions of the page.

--- smart-home:home-assistant [2025/05/03 04:15] – [HA mysql] admin
+++ smart-home:home-assistant [2025/05/03 05:04] (current) – [HA mysql] admin
@@ Line 42: / Line 42: @@
 SELECT
     states_meta.entity_id,
-    count(*),
+    COUNT(*),
    -- states.state,
-    from_unixtime(min(states.last_updated_ts)) as last_updated_ts,
+    from_unixtime(MIN(IF(states.last_changed_ts IS NULL,
-    from_unixtime(min(IF(states.last_changed_ts IS NULL,
+        states.last_updated_ts,
+        states.last_changed_ts))) AS first_changed_ts,
+    from_unixtime(Max(IF(states.last_changed_ts IS NULL,
         states.last_updated_ts,
         states.last_changed_ts))) AS last_changed_ts
@@ Line 52: / Line 54: @@
         LEFT JOIN
     states_meta ON (states.metadata_id = states_meta.metadata_id)
-group by states_meta.entity_id
+GROUP BY states_meta.entity_id
-order by 2 desc
+ORDER BY 2 DESC
-limit 10;
+LIMIT 10;
 -- size of all tables
@@ Line 60: / Line 62: @@
   ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)`
   FROM information_schema.TABLES
-  ORDER BY (data_length + index_length) DESC;
+  ORDER BY (data_length + index_length) DESC
+  limit 10;
+-- optimize
+OPTIMIZE TABLE homeassistant.states;
 </code>
 ++++