Mysql Optimizer 1 9 1

Return to site

Mysql Optimizer 1 9 1

optimizer_switch is a server variable that one can use to enable/disable specific optimizations.
Mysql Optimizer 1 9 12
Mysql Optimizer Trace
Mysql Query Optimizer
Select 1 Mysql
Mysql Optimizer 1 9 100
SyntaxMysql Optimizer 1 9 12To set or unset the various optimizations, use the following syntax:
The cmd takes the following format:
SyntaxDescription
defaultReset all optimizations to their default values.
optimization_name=defaultSet the specified optimization to its default value.
optimization_name=onEnable the specified optimization.
optimization_name=offDisable the specified optimization.
The directive 'querycachetype=1' turns MySQL caching on if it was turned off by default. The default ' querycachesize ' is 1MB and like we said above a value a range of around 10 MB is. If the optimizer determines that the range optimizer applies, it does not use histogram statistics. For columns that are indexed, row estimates can be obtained for equality comparisons using index dives (see Section 8.2.1.2, 'Range Optimization'). In this case, histogram statistics are not necessarily useful because index dives can yield. 17 Example: per-column statistics MariaDB SET OPTIMIZERUSECONDITIONSELECTIVITY=1; Query OK, 0 rows affected (0.01 sec) MariaDB SELECT. FROM departments JOIN deptemp USING (deptno). Optimizerswitch is a server variable that one can use to enable/disable specific optimizations. To set or unset the various optimizations, use the following syntax.
There is no need to list all flags - only those that are specified in the command will be affected.
Available FlagsBelow is a list of all optimizer_switch flags available in MariaDB:
Flag and MariaDB defaultSupported in MariaDB sinceSupported in MySQL since
condition_pushdown_for_derived=onMariaDB 10.2.2-
condition_pushdown_from_having=onMariaDB 10.4.3-
condition_pushdown_for_subquery=onMariaDB 10.4.0-
derived_merge=onMariaDB 5.3MySQL 5.7
derived_with_keys=onMariaDB 5.3-
defaultMariaDB 5.1MySQL 5.1
engine_condition_pushdown=offMariaDB 5.5 (deprecated in 10.1)MySQL 5.5
exists_to_in=onMariaDB 10.0-
extended_keys=onMariaDB 5.5.21-
firstmatch=onMariaDB 5.3MySQL 5.6
index_condition_pushdown=onMariaDB 5.3MySQL 5.6
index_merge=onMariaDB 5.1MySQL 5.1
index_merge_intersection=onMariaDB 5.1MySQL 5.1
index_merge_sort_intersection=offMariaDB 5.3-
index_merge_sort_union=onMariaDB 5.1MySQL 5.1
index_merge_union=on#MariaDB 5.1MySQL 5.1
in_to_exists=onMariaDB 5.3-
join_cache_bka=onMariaDB 5.3-
join_cache_hashed=onMariaDB 5.3-
join_cache_incremental=onMariaDB 5.3-
loosescan=onMariaDB 5.3MySQL 5.6
materialization=on (semi-join, non-semi-join)MariaDB 5.3MySQL 5.6
mrr=offMariaDB 5.3MySQL 5.6
mrr_cost_based=offMariaDB 5.3MySQL 5.6
mrr_sort_keys=offMariaDB 5.3-
optimize_join_buffer_size=onMariaDB 5.3, Defaults to ON from MariaDB 10.4.3-
orderby_uses_equalities=onMariaDB 10.1.15-
outer_join_with_cache=onMariaDB 5.3-
partial_match_rowid_merge=onMariaDB 5.3-
partial_match_table_scan=onMariaDB 5.3-
rowid_filter=onMariaDB 10.4.3-
semijoin=onMariaDB 5.3MySQL 5.6
semijoin_with_cache=onMariaDB 5.3-
split_materialized=on[1]MariaDB 10.3.4-
subquery_cache=onMariaDB 5.3-
table_elimination=onMariaDB 5.1-
↑ replaced split_grouping_derived, introduced in MariaDB 10.3.1
DefaultsFrom versionDefault optimizer_switch setting
MariaDB 10.4.3index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=on, table_elimination=on, extended_keys=on, exists_to_in=on, orderby_uses_equalities=on, condition_pushdown_for_derived=on, split_materialized=on, condition_pushdown_for_subquery=on, rowid_filter=on,condition_pushdown_from_having=on
MariaDB 10.4.0index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=on, orderby_uses_equalities=on, condition_pushdown_for_derived=on, split_materialized=on, condition_pushdown_for_subquery=on
MariaDB 10.3.4index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=on, orderby_uses_equalities=on, condition_pushdown_for_derived=on, split_materialized=on
MariaDB 10.2.2index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=on, orderby_uses_equalities=on, condition_pushdown_for_derived=on
MariaDB 10.1.15index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=on, orderby_uses_equalities=off
MariaDB 10.0.12index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=on
MariaDB 10.0.9index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=off
MariaDB 10.0.2index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=off, exists_to_in=off
MariaDB 5.5.21index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=off
MariaDB 5.5.20index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on
MariaDB 5.3.3index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on
MariaDB 5.3.0index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, index_condition_pushdown=off, derived_merge=off, derived_with_keys=off, firstmatch=off, loosescan=off, materialization=off, in_to_exists=on, semijoin=off, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=off, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=off, semijoin_with_cache=off, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on
< MariaDB 5.3.0index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on
Comments Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party. 
If your application is performing lot of deletes and updates on MySQL database, then there is a high possibility that your MySQL data files are fragmented.
This will result in lot of unused space, and also might affect performance.
So, it is highly recommended that you defrag your MySQL tables on an ongoing basis.
Softraid 5 8 1 – high quality raid management. This tutorial explains how to optimize MySQL to defrag tables and reclaim unused space.
1. Identify Tables for Optimization

Syntax	Description
default	Reset all optimizations to their default values.
optimization_name=default	Set the specified optimization to its default value.
optimization_name=on	Enable the specified optimization.
optimization_name=off	Disable the specified optimization.

Flag and MariaDB default	Supported in MariaDB since	Supported in MySQL since
condition_pushdown_for_derived=on	MariaDB 10.2.2	-
condition_pushdown_from_having=on	MariaDB 10.4.3	-
condition_pushdown_for_subquery=on	MariaDB 10.4.0	-
derived_merge=on	MariaDB 5.3	MySQL 5.7
derived_with_keys=on	MariaDB 5.3	-
default	MariaDB 5.1	MySQL 5.1
engine_condition_pushdown=off	MariaDB 5.5 (deprecated in 10.1)	MySQL 5.5
exists_to_in=on	MariaDB 10.0	-
extended_keys=on	MariaDB 5.5.21	-
firstmatch=on	MariaDB 5.3	MySQL 5.6
index_condition_pushdown=on	MariaDB 5.3	MySQL 5.6
index_merge=on	MariaDB 5.1	MySQL 5.1
index_merge_intersection=on	MariaDB 5.1	MySQL 5.1
index_merge_sort_intersection=off	MariaDB 5.3	-
index_merge_sort_union=on	MariaDB 5.1	MySQL 5.1
index_merge_union=on#	MariaDB 5.1	MySQL 5.1
in_to_exists=on	MariaDB 5.3	-
join_cache_bka=on	MariaDB 5.3	-
join_cache_hashed=on	MariaDB 5.3	-
join_cache_incremental=on	MariaDB 5.3	-
loosescan=on	MariaDB 5.3	MySQL 5.6
materialization=on (semi-join, non-semi-join)	MariaDB 5.3	MySQL 5.6
mrr=off	MariaDB 5.3	MySQL 5.6
mrr_cost_based=off	MariaDB 5.3	MySQL 5.6
mrr_sort_keys=off	MariaDB 5.3	-
optimize_join_buffer_size=on	MariaDB 5.3, Defaults to ON from MariaDB 10.4.3	-
orderby_uses_equalities=on	MariaDB 10.1.15	-
outer_join_with_cache=on	MariaDB 5.3	-
partial_match_rowid_merge=on	MariaDB 5.3	-
partial_match_table_scan=on	MariaDB 5.3	-
rowid_filter=on	MariaDB 10.4.3	-
semijoin=on	MariaDB 5.3	MySQL 5.6
semijoin_with_cache=on	MariaDB 5.3	-
split_materialized=on^[1]	MariaDB 10.3.4	-
subquery_cache=on	MariaDB 5.3	-
table_elimination=on	MariaDB 5.1	-

From version	Default optimizer_switch setting
MariaDB 10.4.3	index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=on, table_elimination=on, extended_keys=on, exists_to_in=on, orderby_uses_equalities=on, condition_pushdown_for_derived=on, split_materialized=on, condition_pushdown_for_subquery=on, rowid_filter=on,condition_pushdown_from_having=on
MariaDB 10.4.0	index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=on, orderby_uses_equalities=on, condition_pushdown_for_derived=on, split_materialized=on, condition_pushdown_for_subquery=on
MariaDB 10.3.4	index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=on, orderby_uses_equalities=on, condition_pushdown_for_derived=on, split_materialized=on
MariaDB 10.2.2	index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=on, orderby_uses_equalities=on, condition_pushdown_for_derived=on
MariaDB 10.1.15	index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=on, orderby_uses_equalities=off
MariaDB 10.0.12	index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=on
MariaDB 10.0.9	index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=on, exists_to_in=off
MariaDB 10.0.2	index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=off, exists_to_in=off
MariaDB 5.5.21	index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on, extended_keys=off
MariaDB 5.5.20	index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, engine_condition_pushdown=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on
MariaDB 5.3.3	index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, index_condition_pushdown=on, derived_merge=on, derived_with_keys=on, firstmatch=on, loosescan=on, materialization=on, in_to_exists=on, semijoin=on, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=on, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=on, semijoin_with_cache=on, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on
MariaDB 5.3.0	index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, index_merge_sort_intersection=off, index_condition_pushdown=off, derived_merge=off, derived_with_keys=off, firstmatch=off, loosescan=off, materialization=off, in_to_exists=on, semijoin=off, partial_match_rowid_merge=on, partial_match_table_scan=on, subquery_cache=off, mrr=off, mrr_cost_based=off, mrr_sort_keys=off, outer_join_with_cache=off, semijoin_with_cache=off, join_cache_incremental=on, join_cache_hashed=on, join_cache_bka=on, optimize_join_buffer_size=off, table_elimination=on
< MariaDB 5.3.0	index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on

The first step is to identify whether you have fragmentation on your MySQL database. Atv flash (black) 2 5.
Connect to your MySQL database, and execute the following query, which will display how much unused space are available in every table.
In the above output:
This will display list of all tables that has minimum of 500MB of unused space. As we see above, in this example, there are 3 tables that has more than 500MB of unused space.
data_length_mb column displays the total table size in MB. For example, EMPLOYEE table size is around 21GB.
data_free_mb column displays the total unused space in that particular table. For example, EMPLOYEE table has around 19GB of unused space in it.
All these three tables (EMPLOYEE, DEPARTMENT AND BENEFITS) are heavily fragmented and it needs to be optimized to reclaim the unused space.
From the filesystem level, you can see the size of the individual table files as shown below.
The file size will be the same as what you see under 'data_length_mb' column in the above output.
In this example, the EMPLOYEE.MYD file is taking up around 22GB at the filesystem level, but it has lot of unused space in it. If we optimize this table, the size of this file should go down dramatically.
2. Defrag using OPTIMIZE TABLE commandThere are two ways to optimize a table.
The first method is to use Optimize table command as shown below.
The following example will optimize EMPLOYEE table.
You can also optimize multiple tables in a single command as shown below.
Few points to keep in mind about optimize table:
Optimize table can be performed for InnoDB engine, or MyISAM engine, or ARCHIVE tables.
For MyISAM tables, it will analyze the table, it will defragment the corresponding MySQL datafile, and reclaim the unused space.
For InnoDB tables, optimize table will simply perform an alter table to reclaim the space.
If you have indexes, it will also resort the index pages, and update the statistics.
During optimization, MySQL will create a temporary table for the table, and after the optimization it will delete the original table, and rename this temporary table to the original table.
In the above optimization, the EMPLOYEE table is an MyISAM table.
For this example, before the optimization, you'll see the following .MYD file for the table.
When the 'OPTIMIZE TABLE' command is running, you can see that it has created a temporary file for this table with extension .TMD. The size of this temporary file will keep growing until the optimize table is running.
After the optimize table command finishes, you'll not see the temporary table. Instead, you'll see the original EMPLOYEE.MYD file that is optimized and with reduced file size.
Mysql Optimizer Trace3. Defrag using mysqlcheck commandThe second method to optimize a table is using mysqlcheck command as shown below.
https://ameblo.jp/66placenanba1x/entry-12649614836.html. The following example will optimize the DEPARTMENT table. You'll execute this command from the Linux prompt (and not on MySQL prompt).
Note: Internally mysqlcheck command uses 'OPTIMIZE TABLE' command.
https://jackson-free.mystrikingly.com/blog/how-to-backup-data-on-macbook. In the above example:
mysqlcheck is the command that is executed from the Linux prompt.
-o option is to indicate that mysqlcheck should perform 'optimize table' operation.
thegeekstuff is the database
DEPARTMENT is the table inside thegeekstuff database that should be optimized
-u root indicates that mysqlcheck command should use 'root' as the mysql user to connect
-p indicates the password for the root account of mysql. Please note that there is no space between -p option and the password.
Mysql Query OptimizerApart from optimization, you can also use mysqlcheck command to check, analyze and repair tables in your mysql database.
4. Defrag All Tables or All DatabasesIf you want to optimize all the tables in a particular MySQL database, use the following command.
The following command will optimize all the tables located in thegeekstuff database.
If you have multiple database running on your system, you can optimize all the tables located under all the database on your system using the following command.
The following will optimize ALL database on your system.
5. After OptimizationAfter the optimization, using the following query, check the total-size and unused-space-size for the three tables that we optimized in this example.
As we see from the above output, the data_length_mb is drastically reduced for these tables after the optimization. Also, the data_free_mb is now at 0, as there are no fragmentation anymore. Movavi pdf editor 3 0 10.
Select 1 MysqlThe file size for these tables are now lot less when compared to the original size. We have now reclaimed lot of unused space at the filsystem level for these tables.
In this example, we've reclaimed around 37GB of unused space after optimizing these three tables.
Mysql Optimizer 1 9 100> Add your comment
If you enjoyed this article, you might also like.

Next post: 12 Chef Knife Cookbook Command Examples
Previous post: 15 Essential Amazon AWS EC2 CLI Command Examples