Back Matter – MySQL 8 Query Performance Tuning: A Systematic Method for Improving Execution Speeds

Index
A
Access method
range
ALTER TABLE algorithm
antijoin
auto-increment
B
Benchmark
tools
Database Factory
DBT2
DBT3
DVD Store
HammerDB
iiBench
Sysbench
TPC benchmarks
TPC-C
TPC-DI
TPC-DS
TPC-E
TPC-H
TPC-VMS
Best practice
Blocked nested loop
Build phase
Build table
C
Cache column
Cache table
Cardinality
SeeIndex cardinality
Character set
utf8mb3
utf8mb4
Clustered index
Collation
UCA 9.0.0
Common table expressions
Configuration options
autocommit
binlog_format
binlog_group_commit_sync_delay
binlog_row_event_max_size
binlog_row_image
binlog_row_value_options
binlog_transaction_dependency_history_size
binlog_transaction_dependency_tracking
cte_max_recursion_depth
eq_range_index_dive_limit
foreign_key_checks
histogram_generation_max_mem_size
information_schema_stats_expiry
innodb_adaptive_hash_index
innodb_adaptive_hash_index_parts
innodb_autoinc_lock_mode
innodb_buffer_pool_dump_now
innodb_buffer_pool_dump_pct
innodb_buffer_pool_in_core_file
innodb_buffer_pool_instances
innodb_buffer_pool_size
innodb_concurrency_tickets
innodb_deadlock_detect
innodb_flush_log_at_timeout
innodb_flush_log_at_trx_commit
innodb_flush_method
innodb_ft_aux_table
innodb_ft_sort_pll_degree
innodb_io_capacity
innodb_io_capacity_max
innodb_lock_wait_timeout
innodb_log_buffer_size
innodb_log_files_in_group
innodb_log_file_size
innodb_max_purge_lag
innodb_max_purge_lag_delay
innodb_monitor_disable
innodb_monitor_enable
innodb_monitor_enable_locks
innodb_monitor_reset
innodb_monitor_reset_all
innodb_old_blocks_pct
innodb_old_blocks_time
innodb_parallel_read_threads
innodb_print_all_deadlocks
innodb_purge_batch_size
innodb_purge_threads
innodb_rollback_on_timeout
innodb_sort_buffer_size
innodb_stats_auto_recalc
innodb_stats_include_delete_marked
innodb_stats_method
innodb_stats_on_metadata
innodb_stats_persistent
innodb_stats_persistent_sample_pages
innodb_stats_transient_sample_pages
innodb_status_output
innodb_status_output_locks
innodb_thread_concurrency
join_buffer_size
key_buffer_size
local_infile
lock_wait_timeout
log_output
log_queries_not_using_indexes
log_short_format
log_slow_admin_statements
log_slow_extra
log_slow_slave_statements
log_throttle_queries_not_using_indexes
log_timestamps
long_query_time
max_allowed_packet
max_binlog_size
max_digest_length
max_execution_time
metadata_locks
min_examined_row_limit
optimizer_prune_level
optimizer_search_depth
optimizer_switch
batched_key_access
block_nested_loop on
condition_fanout_filter
derived_merge
duplicateweedout
engine_condition_pushdown
firstmatch
hash_join
index_condition_pushdown
index_merge
index_merge_intersection
index_merge_sort_union
index_merge_union
loosescan
materialization
mrr
mrr_cost_based
semijoin
skip_scan
subquery_materialization_cost_based
use_index_extensions
use_invisible_indexes
optimizer_trace
optimizer_trace_limit
performance-schema-consumer-events-statements-history-long
performance_schema_digests_size
performance_schema_events_statements_history_long_size
performance_schema_events_statements_history_size
performance-schema-instrument
performance_schema_max_digest_length
performance_schema_max_digest_sample_age
print_identified_with_as_hex
range_optimizer_max_mem_size
read_rnd_buffer_size
replicate-do-db
replicate-do-table
replicate-ignore-db
replicate-ignore-table
replicate-wild-do-table
replicate-wild-ignore-table
report_host
report_port
server_id
server_uuid
slave_checkpoint_group
slave_checkpoint_period
slave_compressed_protocol
slave_parallel_type
slave_parallel_workers
slave_pending_jobs_size_max
slave_preserve_commit_order
slow_query_log
slow_query_log_file
sort_buffer_size
sql_log_bin
sql_mode
sql_require_primary_key
sync_binlog
sync_master_info
sync_relay_log
sync_relay_log_info
temptable_max_ram
temptable_use_mmap
time_zone
transaction_isolation
transaction_write_set_extraction
unique_checks
Covering index
CPU usage
CTE
SeeCommon table expressions
D
Data types
hybrid
enum
set
JSON
json
numeric
bigint
bit
bool
SeeData types, numeric, tinyint
decimal
double
float
int
mediumint
numeric
SeeData types, numeric, decimal
smallint
tinyint
spatial
geometry
geometrycollection
linestring
multilinestring
multipoint
multipolygon
point
polygon
strings and binary Data
binary
blob
char
longblob
longtext
mediumblob
mediumtext
text
tinyblob
tinytext
varbinary
varchar
temporal
date
datetime
time
timestamp
year
direct I/O
disk I/O
Seefile I/O
E
EER diagram
SeeMySQL Workbench, EER Diagram
Error number
1040
1099
1100
1205
1206
1213
1227
1267
1739
1845
3058
3130
5011
11958
ER_ALTER_OPERATION_NOT_SUPPORTED
SeeError number, 1845
ER_CANT_AGGREGATE_2COLLATIONS
SeeError number, 1267
ER_CON_COUNT_ERROR
SeeError number, 1040
ER_LOCK_DEADLOCK
SeeError number, 1213
ER_LOCK_TABLE_FULL
SeeError number, 1206
ER_LOCK_WAIT_TIMEOUT
SeeError number, 1205
ER_PLUGGABLE_PROTOCOL_COMMAND_NOT_SUPPORTED
SeeError number, 3130
ER_SPECIFIC_ACCESS_DENIED_ERROR
SeeError number, 1227
ER_TABLE_NOT_LOCKED
SeeError number, 1100
ER_TABLE_NOT_LOCKED_FOR_WRITE
SeeError number, 1099
ER_USER_LOCK_DEADLOCK
SeeError number, 3058
ER_WARN_INDEX_NOT_APPLICABLE
SeeError number, 1739
EXPLAIN output
access type
ALL
const
eq_ref
fulltext
index
index_merge
index_subquery
range
ref
ref_or_null
system
unique_subquery
auto_key0
Extra column
End temporary
FirstMatch
Impossible HAVING
Impossible WHERE
Impossible WHERE noticed after reading const tables
LooseScan
materialized_from_subquery
no matching row in const table
No tables used
Range checked for each record (index map: 0x1)
Recursive
Select tables optimized away
sort_union
Start temporary
Using filesort
Using index
Using index condition
Using index for group-by
Using index for skip scan
Using intersect
Using join buffer (Batched Key Access)
Using join buffer (Block Nested Loop)
Using MRR
Using temporary
Using union
Using where
filtered
functional_index
JSON format
access_type
attached_condition
attached_subqueries
cacheable
cost_info
dependent
duplicates_removal
first_match
grouping_operation
index_condition
intersect
key
loosescan
materialized_from_subquery
message
nested_loop
ordering_operation
partitions
query_block
query_specifications
range_checked_for_each_record
recursive
rows_examined_per_scan
rows_produced_per_join
select_id
sort_union
table
table_name
union
union_result
used_columns
used_key_parts
using_filesort
using_index
using_index_for_group_by
using_index_for_skip_scan
using_join_buffer
Using MRR
Using_MRR
using_temporary_table
windowing
select type
DELETE
DEPENDENT DERIVED
DEPENDENT SUBQUERY
DEPENDENT UNION
DERIVED
INSERT
MATERIALIZED
PRIMARY
REPLACE
SIMPLE
SUBQUERY
UNCACHEABLE SUBQUERY
UNCACHEABLE UNION
UNION
UNION RESULT
UPDATE
Extent
F
file I/O
Filtering effect
Fsync
FTS_DOC_ID
Full table scan
Functional index
G
GRACE hash join
Seehash join
Group Replication
H
hash join
Histogram
bucket
cumulative frequency
equi-height
frequency
singleton
I
Index cardinality
Index hint
FORCE INDEX
IGNORE INDEX
USE INDEX
Index-organized table
Index types
B+-tree
SeeIndex types, B-tree
B-tree
full text
hash
multi-valued
R-tree
SeeIndex types, spatial
spatial
Infimum record
Information Schema
views
CHARACTER_SETS
CHECK_CONSTRAINTS
COLLATION_CHARACTER_SET_APPLICABILITY
COLLATIONS
COLUMN_PRIVILEGES
COLUMNS
COLUMN_STATISTICS
ENGINES
EVENTS
FILES
INNODB_BUFFER_PAGE
INNODB_BUFFER_PAGE_LRU
INNODB_BUFFER_POOL_STATS
INNODB_CACHED_INDEXES
INNODB_CMP
INNODB_CMPMEM
INNODB_CMPMEM_RESET
INNODB_CMP_PER_INDEX
INNODB_CMP_PER_INDEX_RESET
INNODB_CMP_RESET
INNODB_COLUMNS
INNODB_DATAFILES
INNODB_FIELDS
INNODB_FOREIGN
INNODB_FOREIGN_COLS
INNODB_FT_BEING_DELETED
INNODB_FT_CONFIG
INNODB_FT_DEFAULT_STOPWORD
INNODB_FT_DELETED
INNODB_FT_INDEX_CACHE
INNODB_FT_INDEX_TABLE
INNODB_INDEXES
INNODB_METRICS
INNODB_SESSION_TEMP_TABLESPACES
INNODB_TABLES
INNODB_TABLESPACES
INNODB_TABLESPACES_BRIEF
INNODB_TABLESTATS
INNODB_TEMP_TABLE_INFO
INNODB_TRX
INNODB_VIRTUAL
KEY_COLUMN_USAGE
KEYWORDS
OPTIMIZER_TRACE
PARAMETERS
PARTITIONS
PLUGINS
PROCESSLIST
PROFILING
REFERENTIAL_CONSTRAINTS
RESOURCE_GROUPS
ROUTINES
SCHEMA_PRIVILEGES
SCHEMATA
STATISTICS
ST_GEOMETRY_COLUMNS
ST_SPATIAL_REFERENCE_SYSTEMS
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLES
TABLESPACES
TRIGGERS
USER_PRIVILEGES
VIEW_ROUTINE_USAGE
VIEWS
VIEW_TABLE_USAGE
InnoDB
adaptive hash indexes
buffer pool
buffer pool hit rate
doublewrite buffer
history list length
ib_buffer_pool file
metrics
adaptive_hash_pages_added
adaptive_hash_pages_removed
adaptive_hash_rows_added
adaptive_hash_rows_deleted_no_hash_entry
adaptive_hash_rows_removed
adaptive_hash_rows_updated
adaptive_hash_searches
adaptive_hash_searches_btree
cpu_n
cpu_stime_abs
cpu_stime_pct
cpu_utime_abs
cpu_utime_pct
lock_deadlocks
lock_timeouts
log_lsn_current
log_lsn_last_checkpoint
module_cpu
sampled_pages_read
sampled_pages_skipped
trx_active_transactions
trx_commits_insert_update
trx_nl_ro_commits
trx_on_log_no_waits
trx_on_log_wait_loops
trx_on_log_waits
trx_ro_commits
trx_rollback_active
trx_rollbacks
trx_rollbacks_savepoint
trx_rseg_current_size
trx_rseg_history_len
trx_rw_commits
trx_undo_slots_cached
trx_undo_slots_used
monitor
BACKGROUND THREAD
BUFFER POOL AND MEMORY
FILE I/O
INDIVIDUAL BUFFER POOL INFO
INSERT BUFFER AND ADAPTIVE HASH INDEX
LATEST DETECTED DEADLOCK
LATEST FOREIGN KEY ERROR
LOG
ROW OPERATIONS
SEMAPHORES
TRANSACTIONS
new blocks sublist
old blocks sublist
pages made young
redo log
undo log
young-making rate
innodb_memcache
InnoDB Memcached Plugin
innodb_ruby
I/O cache
I/O capacity
I/O latency
J
join order
K
kmalloc
L
Lifecycle
Lock contention
Locks
auto-increment lock
backup Locks
deadlock
exclusive
flush lock
gap before record
gap lock
insert intention lock
intention exclusive
intention shared
IS
SeeLocks, intention shared
IX
SeeLocks, intention exclusive
log lock
metadata lock
predicate lock
record lock
S
SeeLocks, shared
shared
table definition cache version lock
table lock
TDC version lock
SeeLocks, table definition cache lock
user-level lock
X
SeeLocks, exclusive
Lua language
M
MEM
See alsoMySQL Enterprise Monitor
CPU usage
file I/O
memory usage
Memcached
MEMORY storage engine
Memory usage
MySQL Enterprise Backup
MySQL Enterprise Monitor
advisor
Agent
configuration
database file I/O
event
InnoDB buffer pool
installation
lock waits
memory usage
metrics
processes
Query Analyzer
Service Manager
table statistics
timeseries graphs
topology
uninstall
user statistics
MySQL Installer
MySQL programs
mysqlbinlog
mysqlcheck
mysqldump
mysqldumpslow
mysqldumpslow.pl
SeeMySQL programs, mysqldumpslow
mysqlpump
mysqlslap
mysql schema
tables
engine_cost
general_log
gtid_executed
innodb_index_stats
innodb_table_stats
server_cost
slave_master_info
slave_relay_log_info
slow_log
user
MySQL Shell
command-line arguments
--help
--js
--py
--sql
commands
?
connect
js
py
reconnect
show
sql
use
watch
connection
environment variables
MYSQLSH_HOME
MYSQLSH_PROMPT_THEME
MYSQLSH_USER_CONFIG_HOME
PRODUCTION_SERVERS
files
init.js
init.py
mysqlshrc.js
mysqlshrc.py
installation
invoking
methods
db.get_table()
mysql.getClassicSession()
mysql.get_classic_session()
mysqlx.getSession()
mysqlx.get_session()
session.get_schema()
shell.add_extension_object_member()
shell.create_extension_object()
shell.dump_rows()
shell.getSession()
shell.get_session()
shell.help()
shell.register_global()
shell.register_report()
shell.reports.help()
shell.setSession()
shell.set_session()
util.importTable()
util.import_table()
objects
db
dba
mysql
mysqlx
session
shell
util
MySQL Workbench
client connections
connection
EER Diagram
execution plan
SeeMySQL Workbench, Visual Explain
installation
performance reports
Visual Explain
N
Nested loop
O
O_DIRECT
set direct I/O
O_DIRECT_NO_FSYNC
set direct I/O
O_DSYNC
Optimal join order
Seejoin order
Optimization
Batched Key Access
BKA
SeeOptimization, Batched Key Access
condition filtering
derived merge
engine condition pushdown
index condition pushdown
index extensions
index merge
Intersection
Sort-Union
Union
index visibility
loose index scan
MRR
SeeOptimization, Multi-Range Read
Multi-Range Read
range access method
semijoin
duplicate weedout
first match
loose scan
materialization
skip scan
subquery materialization
optimizer hints
BKA
BNL
HASH_JOIN
INDEX_MERGE
JOIN_FIXED_ORDER
JOIN_ORDER
JOIN_PREFIX
JOIN_SUFFIX
MAX_EXECUTION_TIME
MERGE
MRR
NO_BKA
NO_BNL
NO_HASH_JOIN
NO_ICP
NO_INDEX_MERGE
NO_MERGE
NO_MRR
NO_RANGE_OPTIMIZATION
NO_SEMIJOIN
NO_SKIP_SCAN
QB_NAME
RESOURCE_GROUP
SEMIJOIN
SET_VAR
SUBQUERY
O_SYNC
P
Page split
Performance Schema
actor
consumer
digest
event
functions
FORMAT_BYTES()
FORMAT_PICO_TIME()
PS_CURRENT_THREAD_ID()
PS_THREAD_ID()
instruments
error
idle
memory/%
memory/temptable/physical_disk
memory/temptable/physical_ram
stage/%
stage/sql/altering table
stage/sql/statistics
statement/%
statement/sp/set
statement/sp/stmt
statement/sql/call_procedure
statement/sql/select
transaction
wait/%
wait/io/file/%
wait/io/file/innodb/innodb_data_file
wait/io/file/innodb/innodb_log_file
wait/io/file/sql/binlog
wait/io/table/sql/handler
wait/lock/metadata/sql/mdl
wait/lock/table/sql/handler
wait/synch/%
NESTING_EVENT_ID
NO_GOOD_INDEX_USED
object
setup table
summary table
SUM_NO_GOOD_INDEX_USED
tables
accounts
clone_progress
clone_status
data_locks
data_lock_waits
events_errors_summary_by_account_by_error
events_errors_summary_by_host_by_error
events_errors_summary_by_thread_by_error
events_errors_summary_by_user_by_error
events_errors_summary_global_by_error
events_stages_current
events_stages_history
events_stages_history_long
events_statements_current
events_statements_histogram_by_digest
events_statements_histogram_global
events_statements_history
events_statements_history_long
events_statements_summary_by_account_by_event_name
events_statements_summary_by_digest
events_statements_summary_by_host_by_event_name
events_statements_summary_by_program
events_statements_summary_by_thread_by_event_name
events_statements_summary_by_user_by_event_name
events_statements_summary_global_by_event_name
events_transactions_current
events_transactions_history
events_transactions_summary_by_account_by_event_name
events_transactions_summary_by_host_by_event_name
events_transactions_summary_by_thread_by_event_name
events_transactions_summary_by_user_by_event_name
events_transactions_summary_global_by_event_name
events_waits_current
events_waits_history
events_waits_history_long
events_waits_summary_global_by_event_name
file_summary_by_event_name
file_summary_by_instance
global_status
global_variables
host_cache
hosts
keyring_keys
log_status
memory_summary_by_thread_by_event_name
memory_summary_global_by_event_name
metadata_locks
performance_timers
prepared_statements_instances
replication_applier_configuration
replication_applier_filters
replication_applier_global_filters
replication_applier_status
replication_applier_status_by_coordinator
replication_applier_status_by_worker
replication_connection_configuration
replication_connection_status
replication_group_members
replication_group_member_stats
session_account_connect_attrs
session_connect_attrs
session_status
session_variables
setup_actors
setup_consumers
setup_instruments
setup_objects
setup_threads
table_handles
table_io_waits_summary_by_index_usage
table_io_waits_summary_by_table
threads
users
thread
thread names
NESTING_EVENT_ID
NESTING_EVENT_TYPE
thread/innodb/parallel_read_thread
thread/mysqlx/worker
thread/sql/compress_gtid_table
Primary key
Probe input
Probe phase
ProxySQL
Pymemcache
Q
Query cost
Query state
Waiting for table flush
Waiting for table metadata lock
Queue
R
random I/O
Seefile I/O
read-write transaction
Replication
applier thread
binary log
channel
connection thread
I/O thread
SeeReplication, connection thread
multi-threaded
SeeReplication, parallel
Parallel
relay log
Seconds_Behind_Master
SQL thread
SeeReplication, applier thread
Resource group
Row format
S
Secondary index
sequential I/O
Seefile I/O
SKIP LOCKED
Spatial Reference System Identifier
SQL conditions
IN
EXISTS
OR
SQL functions
BIN_TO_UUID()
CONNECTION_ID()
FORMAT_BYTES()
SeePerformance Schema, functions, FORMAT_BYTES()
FORMAT_PICO_TIME()
SeePerformance Schema, functions, FORMAT_PICO_TIME()
GET_LOCK()
IS_FREE_LOCK()
IS_USED_LOCK()
JSON_CONTAINS()
JSON_OVERLAPS()
JSON_REMOVE()
JSON_REPLACE()
JSON_SET()
JSON_TABLE()
LAG()
MAX()
MBRContains()
MIN()
PS_CURRENT_THREAD_ID()
SeePerformance Schema, functions, PS_CURRENT_THREAD_ID()
PS_THREAD_ID()
SeePerformance Schema, functions, PS_THREAD_ID()
RELEASE_ALL_LOCKS()
RELEASE_LOCK()
STATEMENT_DIGEST()
STATEMENT_DIGEST_TEXT()
UUID_TO_BIN()
SQL mode
STRICT_ALL_TABLES
STRICT_TRANS_TABLES
SQL operator
<=>
SeeSQL operator, NULL-safe equal operator
MEMBER OF
NULL-safe equal operator
SQL statements
ALTER RESOURCE GROUP
ALTER TABLE
ANALYZE TABLE
CHANGE MASTER TO
CHANGE REPLICATION FILTER
CHECK TABLE
CREATE RESOURCE GROUP
DROP RESOURCE GROUP
DROP TABLE
EXPLAIN ANALYZE
EXPLAIN FOR CONNECTION
FLUSH LOGS
FLUSH OPTIMIZER_COSTS
FLUSH TABLES
INSERT
LOAD DATA
LOCK INSTANCE FOR BACKUP
LOCK TABLES
OPTIMIZE TABLE
SELECT … FOR SHARE
SELECT … FOR UPDATE
SELECT … LOCK IN SHARE MODE
SeeSQL statements, SELECT … FOR SHARE
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW CREATE USER
SHOW ENGINE INNODB STATUS
SHOW ENGINE PERFORMANCE_SCHEMA STATUS
SHOW GRANTS
SHOW INDEX
SHOW MASTER STATUS
SHOW PROCESSLIST
SHOW RELAYLOG EVENTS
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW VARIABLES
SHOW WARNINGS
UNLOCK INSTANCE
UNLOCK TABLES
WITH
SeeCommon table expressions
XA RECOVER
SRID
SeeSpatial Reference System Identifier
Stack
Status variables
Innodb_buffer_pool_read_requests
Innodb_pages_read
Innodb_row_lock_current_waits
Innodb_row_lock_time
Innodb_row_lock_time_avg
Innodb_row_lock_time_max
Innodb_row_lock_waits
Last_query_cost
Performance_schema_digest_lost
supremum record
sys schema
functions
extract_schema_from_file_name()
extract_table_from_file_name()
format_bytes()
format_path()
format_statement()
format_time()
list_add()
list_drop()
ps_is_consumer_enabled()
ps_setup_disable_thread()
ps_setup_enable_thread()
ps_thread_id()
quote_identifier()
sys_get_config()
version_major()
version_minor()
version_patch()
procedures
execute_prepared_stmt
ps_trace_statement_digest()
ps_trace_thread()
statement_performance_analyzer()
table_exists
settings
debug
diagnostics.allow_i_s_tables
diagnostics.include_raw
ignore_sys_config_triggers
ps_thread_trx_info.max_length
statement_performance_analyzer.limit
statement_performance_analyzer.view
statement_truncate_len
tables
sys_config
views
host_summary_by_file_io
host_summary_by_file_io_type
host_summary_by_statement_latency
host_summary_by_statement_type
innodb_lock_waits
io_by_thread_by_latency
io_global_by_file_by_bytes
io_global_by_file_by_latency
io_global_by_wait_by_bytes
io_global_by_wait_by_latency
memory_global_by_current_bytes
metrics
processlist
schema_auto_increment_columns
schema_index_statistics
schema_redundant_indexes
schema_table_lock_waits
schema_table_statistics
schema_table_statistics_with_buffer
schema_tables_with_full_table_scans
schema_unused_indexes
session
statement_analysis
statements_with_errors_or_warnings
statements_with_full_table_scans
statements_with_runtimes_in_95th_percentile
statements_with_sorting
statements_with_temp_tables
user_summary_by_file_io
user_summary_by_file_io_type
user_summary_by_statement_latency
user_summary_by_statement_type
System variables
SeeConfiguration options
T
table I/O
Table options
STATS_AUTO_RECALC
STATS_PERSISTENT
STATS_SAMPLE_PAGES
TempTable storage engine
Test data
employees
GeoJSON
sakila
United States Geological Survey (USGS)
Wikipedia
world
world_x
Time to live
Transaction isolation level
TTL
SeeTime to live
U
Unbuffered
unique index
UUID
V
Visual Explain
SeeMySQL Workbench, Visual Explain
W, X, Y, Z
Window functions
wrong index
wrong join order
Seejoin order