SYS Schema Second Steps

The MySQL SYS Schema is a wonderful tool and the last installment of this blog covered its installation. This entry will cover a little deeper foray into the subject.

Be sure to also refer to Mark Leith’s slides from this years MySQL Central at Oracle Open World.

A Simple Query Using an Index
The following query is executed to get a baseline.
mysql> select * from schema_index_statistics where table_name='City' and table_schema='world'\G
*************************** 1. row ***************************
table_schema: world
table_name: City
index_name: PRIMARY
rows_selected: 0
select_latency: 0 ps
rows_inserted: 0
insert_latency: 0 ps
rows_updated: 0
update_latency: 0 ps
rows_deleted: 0
delete_latency: 0 ps
*************************** 2. row ***************************
table_schema: world
table_name: City
index_name: CountryCode
rows_selected: 0
select_latency: 0 ps
rows_inserted: 0
insert_latency: 0 ps
rows_updated: 0
update_latency: 0 ps
rows_deleted: 0
delete_latency: 0 ps
2 rows in set (0.02 sec)

In another tab, the query select * from City where CountryCode='USA'; is executed and the MySQL client reports that 274 rows are in the result set and 0.03 seconds were consumed.

mysql> select * from schema_index_statistics where table_name='City' and table_schema='world'\G
*************************** 1. row ***************************
table_schema: world
table_name: City
index_name: CountryCode
rows_selected: 275
select_latency: 1.55 ms
rows_inserted: 0
insert_latency: 0 ps
rows_updated: 0
update_latency: 0 ps
rows_deleted: 0
delete_latency: 0 ps
*************************** 2. row ***************************
table_schema: world
table_name: City
index_name: PRIMARY
rows_selected: 0
select_latency: 0 ps
rows_inserted: 0
insert_latency: 0 ps
rows_updated: 0
update_latency: 0 ps
rows_deleted: 0
delete_latency: 0 ps
2 rows in set (0.02 sec)

mysql>

And rerunning the query against the Sys Schema reports that the CountryCode index was used to get 275 rows.

So Who Is Fat Fingering Queries?
So lets run a bad query (I know, I know, — you never mistype or would purposely mung a query but this is just an example). mysql> select Mayor FROm City;
ERROR 1054 (42S22): Unknown column 'Mayor' in 'field list'
mysql>

mysql> select * from statements_with_errors_or_warnings where db='world'\G

*************************** 1. row ***************************
query: SELECT `Mayor` FROM `City`
db: world
exec_count: 1
errors: 1
error_pct: 100.0000
warnings: 0
warning_pct: 0.0000
first_seen: 2014-12-10 14:44:50
last_seen: 2014-12-10 14:44:50
digest: a53dc7f348252c05c7e0d5e5a06e7db8
3 rows in set (0.01 sec)

mysql>
And can see the bad query.

Next time statement process will be investigated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s