Differences

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

--- mysqlperformance [2019/08/05 15:33] – [Performance Schema Scripts] z0hpvk
+++ mysqlperformance [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 16: / Line 16: @@
 <code SQL>
 -- Unused Indexes
+select * from sys.schema_unused_indexes;
 select object_schema, object_name, index_name
   from performance_schema.table_io_waits_summary_by_index_usage
  where index name is not null and count_star = 0
  order by object_schema, object_name;
+-- Queries not using Indexes
+SELECT THREAD_ID TID, SUBSTR(SQL_TEXT, 1, 50) SQL_TEXT, ROWS_SENT RS,
+       ROWS_EXAMINED RE,CREATED_TMP_TABLES,NO_INDEX_USED,NO_GOOD_INDEX_USED
+  FROM performance_schema.events_statements_history
+ WHERE NO_INDEX_USED=1 OR NO_GOOD_INDEX_USED=1;
+-- Queries using Full Table Scans
+SELECT query, db, total_latency, no_index_used_count, rows_sent, rows_examined, last_seen
+  FROM sys.statements_with_full_table_scans
+ WHERE db not in ('performance_schema','sys')
+ORDER BY last_seen DESC limit 50;
 -- Process List
@@ Line 68: / Line 82: @@
 select user,statement,total,rows_sent,rows_examined,rows_affected,full_scans
   from sys.user_summary_by_statement_type limit 100;
+-- Active Queries
+SELECT CONCAT_WS(
+'','# Time: ', date_format(CURDATE(),'%y%m%d'),' ',TIME_FORMAT(NOW(6),'%H:%i:%s.%f'),'\n'
+,'# User@Host: ',t.PROCESSLIST_USER,'[',t.PROCESSLIST_USER,'] @ ',PROCESSLIST_HOST,' []  Id: ',t.PROCESSLIST_ID,'\n'
+,'# Schema: ',CURRENT_SCHEMA,'  Last_errno: ',MYSQL_ERRNO,'  ','\n'
+,'# Query_time: ',ROUND(s.TIMER_WAIT / 1000000000000, 6),' Lock_time: ',ROUND(s.LOCK_TIME / 1000000000000, 6),'  Rows_sent: ',ROWS_SENT,'  Rows_examined: ',ROWS_EXAMINED,'  Rows_affected: ',ROWS_AFFECTED,'\n'
+,'# Tmp_tables: ',CREATED_TMP_TABLES,'  Tmp_disk_tables: ',CREATED_TMP_DISK_TABLES,'  ','\n'
+,'# Full_scan: ',IF(SELECT_SCAN=0,'No','Yes'),'  Full_join: ',IF(SELECT_FULL_JOIN=0,'No','Yes'),'  Tmp_table: ',IF(CREATED_TMP_TABLES=0,'No','Yes'),'  Tmp_table_on_disk: ',IF(CREATED_TMP_DISK_TABLES=0,'No','Yes'),'\n'
+, t.PROCESSLIST_INFO,';')
+FROM performance_schema.events_statements_history s
+JOIN performance_schema.threads t using(thread_id)
+WHERE t.TYPE = 'FOREGROUND'
+  AND t.PROCESSLIST_INFO IS NOT NULL
+  AND t.PROCESSLIST_ID != connection_id()
+ORDER BY t.PROCESSLIST_TIME desc;
 </code>