Skip to content

Instantly share code, notes, and snippets.

@morgo

morgo/gist:d0a12ed4479343352b6d

Created October 15, 2014 15:52
Show Gist options
  • Save morgo/d0a12ed4479343352b6d to your computer and use it in GitHub Desktop.
Save morgo/d0a12ed4479343352b6d to your computer and use it in GitHub Desktop.
Download ZIP
MySQL Bug #72322 - EXPLAIN in DD release
Raw
gistfile1.sql
mysql> EXPLAIN SELECT concat('Select ''', a.TABLE_SCHEMA,',',a.TABLE_NAME,',', DATA_LENGTH, ',', INDEX_LENGTH, ',', DATA_FREE, ','', count(*),', ''','', i on ec OM I ma
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: cat
partitions: NULL
type: index
possible_keys: PRIMARY
key: name
key_len: 194
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: cat
partitions: NULL
type: index
possible_keys: PRIMARY
key: name
key_len: 194
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index; Using join buffer (Block Nested Loop)
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: sch
partitions: NULL
type: ref
possible_keys: PRIMARY,catalog_id
key: catalog_id
key_len: 8
ref: mysql.cat.id
rows: 5
filtered: 50.00
Extra: Using where; Using index
*************************** 4. row ***************************
id: 1
select_type: PRIMARY
table: sch
partitions: NULL
type: eq_ref
possible_keys: PRIMARY,catalog_id
key: catalog_id
key_len: 202
ref: mysql.cat.id,mysql.sch.name
rows: 1
filtered: 100.00
Extra: Using index
*************************** 5. row ***************************
id: 1
select_type: PRIMARY
table: tbl
partitions: NULL
type: ref
possible_keys: PRIMARY,schema_id
key: schema_id
key_len: 8
ref: mysql.sch.id
rows: 56
filtered: 100.00
Extra: Using index
*************************** 6. row ***************************
id: 1
select_type: PRIMARY
table: tbl
partitions: NULL
type: eq_ref
possible_keys: schema_id
key: schema_id
key_len: 202
ref: mysql.sch.id,mysql.tbl.name
rows: 1
filtered: 100.00
Extra: Using index condition
*************************** 7. row ***************************
id: 1
select_type: PRIMARY
table: col
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: mysql.tbl.collation_id
rows: 1
filtered: 100.00
Extra: Using index
*************************** 8. row ***************************
id: 1
select_type: PRIMARY
table: stat
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where; Using join buffer (Block Nested Loop)
*************************** 9. row ***************************
id: 1
select_type: PRIMARY
table: col
partitions: NULL
type: ref
possible_keys: table_id,table_id_2,collation_id
key: table_id
key_len: 8
ref: mysql.tbl.id
rows: 7
filtered: 100.00
Extra: Using index condition; Using where
*************************** 10. row ***************************
id: 1
select_type: PRIMARY
table: coll
partitions: NULL
type: eq_ref
possible_keys: PRIMARY,character_set_id
key: PRIMARY
key_len: 8
ref: mysql.col.collation_id
rows: 1
filtered: 100.00
Extra: NULL
*************************** 11. row ***************************
id: 1
select_type: PRIMARY
table: cs
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: mysql.coll.character_set_id
rows: 1
filtered: 100.00
Extra: Using index
*************************** 12. row ***************************
id: 1
select_type: PRIMARY
table: <derived6>
partitions: NULL
type: ref
possible_keys: <auto_key0>
key: <auto_key0>
key_len: 8
ref: mysql.col.id
rows: 10
filtered: 100.00
Extra: NULL
*************************** 13. row ***************************
id: 6
select_type: DERIVED
table: column_type_elements
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 12
ref: NULL
rows: 842
filtered: 100.00
Extra: NULL
*************************** 14. row ***************************
id: 2
select_type: UNION
table: cat
partitions: NULL
type: index
possible_keys: PRIMARY
key: name
key_len: 194
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index
*************************** 15. row ***************************
id: 2
select_type: UNION
table: sch
partitions: NULL
type: ref
possible_keys: PRIMARY,catalog_id
key: catalog_id
key_len: 8
ref: mysql.cat.id
rows: 5
filtered: 50.00
Extra: Using where; Using index
*************************** 16. row ***************************
id: 2
select_type: UNION
table: tbl
partitions: NULL
type: ref
possible_keys: schema_id
key: schema_id
key_len: 8
ref: mysql.sch.id
rows: 56
filtered: 100.00
Extra: Using index condition; Using where
*************************** 17. row ***************************
id: 2
select_type: UNION
table: col
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: mysql.tbl.collation_id
rows: 1
filtered: 100.00
Extra: Using index
*************************** 18. row ***************************
id: 2
select_type: UNION
table: stat
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where; Using join buffer (Block Nested Loop)
*************************** 19. row ***************************
id: 3
select_type: SUBQUERY
table: cat
partitions: NULL
type: index
possible_keys: PRIMARY
key: name
key_len: 194
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index
*************************** 20. row ***************************
id: 3
select_type: SUBQUERY
table: sch
partitions: NULL
type: ref
possible_keys: PRIMARY,catalog_id
key: catalog_id
key_len: 8
ref: mysql.cat.id
rows: 5
filtered: 100.00
Extra: Using index
*************************** 21. row ***************************
id: 3
select_type: SUBQUERY
table: tbl
partitions: NULL
type: ref
possible_keys: PRIMARY,schema_id
key: schema_id
key_len: 8
ref: mysql.sch.id
rows: 56
filtered: 100.00
Extra: Using index
*************************** 22. row ***************************
id: 3
select_type: SUBQUERY
table: col
partitions: NULL
type: ref
possible_keys: table_id,table_id_2,collation_id
key: table_id
key_len: 8
ref: mysql.tbl.id
rows: 7
filtered: 100.00
Extra: Using index condition; Using where
*************************** 23. row ***************************
id: 3
select_type: SUBQUERY
table: coll
partitions: NULL
type: eq_ref
possible_keys: PRIMARY,character_set_id
key: PRIMARY
key_len: 8
ref: mysql.col.collation_id
rows: 1
filtered: 100.00
Extra: NULL
*************************** 24. row ***************************
id: 3
select_type: SUBQUERY
table: cs
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: mysql.coll.character_set_id
rows: 1
filtered: 100.00
Extra: Using index
*************************** 25. row ***************************
id: 3
select_type: SUBQUERY
table: <derived9>
partitions: NULL
type: ref
possible_keys: <auto_key0>
key: <auto_key0>
key_len: 8
ref: mysql.col.id
rows: 10
filtered: 100.00
Extra: NULL
*************************** 26. row ***************************
id: 9
select_type: DERIVED
table: column_type_elements
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 12
ref: NULL
rows: 842
filtered: 100.00
Extra: NULL
*************************** 27. row ***************************
id: NULL
select_type: UNION RESULT
table: <union1,2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Using temporary; Using filesort
27 rows in set, 1 warning (0.01 sec)
mysql>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment