1-- Copyright (c) 2014, 2015, Oracle and/or its affiliates. All rights reserved.
2--
3-- This program is free software; you can redistribute it and/or modify
4-- it under the terms of the GNU General Public License as published by
5-- the Free Software Foundation; version 2 of the License.
6--
7-- This program is distributed in the hope that it will be useful,
8-- but WITHOUT ANY WARRANTY; without even the implied warranty of
9-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
10-- GNU General Public License for more details.
11--
12-- You should have received a copy of the GNU General Public License
13-- along with this program; if not, write to the Free Software
14-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA
15
16--
17-- View: x$schema_index_statistics
18--
19-- Statistics around indexes.
20--
21-- Ordered by the total wait time descending - top indexes are most contended.
22--
23-- mysql> SELECT * FROM x$schema_index_statistics LIMIT 5;
24-- +---------------+----------------------+-------------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
25-- | table_schema  | table_name           | index_name        | rows_selected | select_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency |
26-- +---------------+----------------------+-------------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
27-- | common_schema | _global_sql_tokens   | PRIMARY           |          1886 |     1129676730 |             0 |              0 |            0 |              0 |         1878 |              0 |
28-- | common_schema | _script_statements   | PRIMARY           |          4606 |     4212160680 |             0 |              0 |            0 |              0 |            0 |              0 |
29-- | common_schema | _global_qs_variables | declaration_depth |           256 |     1650193090 |             0 |              0 |           32 |     1372148050 |            0 |              0 |
30-- | common_schema | _global_qs_variables | PRIMARY           |             0 |              0 |             0 |              0 |            0 |              0 |           16 |              0 |
31-- | common_schema | metadata             | PRIMARY           |             5 |       76730810 |             0 |              0 |            4 |      114310170 |            0 |              0 |
32-- +---------------+----------------------+-------------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
33--
34
35CREATE OR REPLACE
36  ALGORITHM = MERGE
37  DEFINER = 'root'@'localhost'
38  SQL SECURITY INVOKER
39VIEW x$schema_index_statistics (
40  table_schema,
41  table_name,
42  index_name,
43  rows_selected,
44  select_latency,
45  rows_inserted,
46  insert_latency,
47  rows_updated,
48  update_latency,
49  rows_deleted,
50  delete_latency
51) AS
52SELECT OBJECT_SCHEMA AS table_schema,
53       OBJECT_NAME AS table_name,
54       INDEX_NAME as index_name,
55       COUNT_FETCH AS rows_selected,
56       SUM_TIMER_FETCH AS select_latency,
57       COUNT_INSERT AS rows_inserted,
58       SUM_TIMER_INSERT AS insert_latency,
59       COUNT_UPDATE AS rows_updated,
60       SUM_TIMER_UPDATE AS update_latency,
61       COUNT_DELETE AS rows_deleted,
62       SUM_TIMER_INSERT AS delete_latency
63  FROM performance_schema.table_io_waits_summary_by_index_usage
64 WHERE index_name IS NOT NULL
65 ORDER BY sum_timer_wait DESC;
66