A large numeric value is stored in far fewer bytes than the equivalent string value. It is therefore faster to move and compare numeric data, so it's best to choose numeric columns for unique id's and other similar fields.

_{This page is licensed: CC BY-SA / Gnu FDL}

Comparing String Columns

When values from different columns are compared, the comparison runs more quickly when the columns are of the same character set and collation. If they are different, the strings need to be converted while the query runs. So, where possible, declare string columns using the same character set and collation when you may need to compare them.

VARCHAR vs BLOB

ORDER BY and GROUP BY clauses can generate temporary tables in memory (see ) if the original table doesn't contain any BLOB fields. If a column is less than 8KB, you can make use of a Binary VARCHAR rather than a BLOB.

_{This page is licensed: CC BY-SA / Gnu FDL}

This article documents thread states that are related to scheduling and execution. These include the Event Scheduler thread, threads that terminate the Event Scheduler, and threads for executing events.

These correspond to the STATE values listed by the statement or in the as well as the PROCESSLIST_STATE value listed in the

This article documents thread states that are related to the . These correspond to the STATE values listed by the statement or in the as well as the PROCESSLIST_STATE value listed in the .

This article documents thread states that are related to connection threads that occur on a replicatioin replica. These correspond to the STATE values listed by the statement or in the as well as the PROCESSLIST_STATE value listed in the .

are a good choice for data that needs to be accessed often, and is rarely updated. Being in memory, it's not suitable for critical data or for storage, but if data can be moved to memory for reading without needing to be regenerated often, if at all, it can provide a significant performance boost.

The has a key feature in that it permits its indexes to be either B-tree or Hash. Choosing the best index type can lead to better performance. See for more on the characteristics of each index type.

_{This page is licensed: CC BY-SA / Gnu FDL}

This article documents thread states that are related to the handler thread that inserts the results of INSERT DELAYED statements.

These correspond to the STATE values listed by the SHOW PROCESSLIST statement or in the Information Schema PROCESSLIST Table as well as the PROCESSLIST_STATE value listed in the Performance Schema threads Table.

_{This page is licensed: CC BY-SA / Gnu FDL}

This article documents thread states that are related to replication master threads. These correspond to the STATE values listed by the SHOW PROCESSLIST statement or in the Information Schema PROCESSLIST Table as well as the PROCESSLIST_STATE value listed in the Performance Schema threads Table.

_{This page is licensed: CC BY-SA / Gnu FDL}

This article documents thread states that are related to replication slave SQL threads. These correspond to the Slave_SQL_State shown by SHOW SLAVE STATUS as well as the STATE values listed by the SHOW PROCESSLIST statement and the Information Schema PROCESSLIST as well as the PROCESSLIST_STATE value listed in the Performance Schema threads Table.

_{This page is licensed: CC BY-SA / Gnu FDL}

Suitability of Filesystems

The filesystem is not the most important aspect of MariaDB performance. More important are the available memory (RAM), the drive speed, and the system variable settings (see Hardware Optimization and System Variables).

Optimizing the filesystem can, however, make a noticeable difference in some cases. Among the best suited Linux filesystems are ext4, XFS and Btrfs. They are all included in the mainline Linux kernel and are widely supported and available on most Linux distributions.

The following theoretical file size and filesystem size limits apply to those filesystems:

Each has unique characteristics that are worth understanding to get the most from their usage.

Disabling Access Time

It's unlikely you'll need to record file access time on a database server, and mounting your filesystem with this disabled can give an easy improvement in performance. To do so, use the noatime option.

If you want to keep access time for or other system files, these can be stored on a separate drive.

Using NFS

Generally, we recommend not to use (Network File System) with MariaDB, for these reasons:

• MariaDB data and log files on NFS volumes can become locked and unavailable for use. Locking issues may occur in cases where multiple instances of MariaDB access the same data directory, or when MariaDB is shut down improperly, for instance, due to a power outage. In particular, sharing a data directory among MariaDB instances is not recommended.

• Data inconsistencies due to messages received out of order or lost network traffic. To avoid this issue, use TCP with hard and intr mount options.

Using NFS within a professional SAN environment or other storage system tends to offer greater reliability than using NFS outside of such an environment. However, NFS within a SAN environment may be slower than directly attached or bus-attached non-rotational storage.

_{This page is licensed: CC BY-SA / Gnu FDL}

MariaDB deals with primary keys over nullable columns according to the SQL standards.

Take the following table structure:

CREATE TABLE t1(
  c1 INT NOT NULL AUTO_INCREMENT, 
  c2 INT NULL DEFAULT NULL, 
  PRIMARY KEY(c1,c2)
);

Column c2 is part of a primary key, and thus it cannot be NULL.

Before , MariaDB (as well as versions of MySQL before MySQL 5.7) would silently convert it into a NOT NULL column with a default value of 0.

Since , the column is converted to NOT NULL, but without a default value. If we then attempt to insert a record without explicitly setting c2, a warning (or, in strict mode, an error), will be thrown, for example:

MySQL, since 5.7, will abort such a CREATE TABLE with an error.

The behavior adheres to the SQL 2003 standard.

SQL-2003, Part II, “Foundation” says:

**11.7 **Syntax Rules

…

5) If the specifies PRIMARY KEY, then for each in the explicit or implicit for which NOT NULL is not specified, NOT NULL is implicit in the .

Essentially this means that all PRIMARY KEY columns are automatically converted to NOT NULL. Furthermore:

11.5 General Rules

…

3) When a site S is set to its default value,

…

b) If the data descriptor for the site includes a , then S is set to the value specified by that .

…

e) Otherwise, S is set to the null value.

There is no concept of “no default value” in the standard. Instead, a column always has an implicit default value of NULL. On insertion it might however fail the NOT NULL constraint. MariaDB and MySQL instead mark such a column as “not having a default value”. The end result is the same — a value must be specified explicitly or an INSERT will fail.

MariaDB since 10.1.7 behaves in a standard compatible manner — being part of a PRIMARY KEY, the nullable column gets an automatic NOT NULL constraint, on insertion one must specify a value for such a column. MariaDB before 10.1.7 was automatically assigning a default value of 0 — this behavior was non-standard. Issuing an error at CREATE TABLE time is also non-standard.

See Also

• describes an edge-case that may result in replication problems when replicating from a master server before this change to a slave server after this change.

_{This page is licensed: CC BY-SA / Gnu FDL}

HIGH PRIORITY

HIGH_PRIORITY gives the statement a higher priority. If the table is locked, high priority SELECTs will be executed as soon as the lock is released, even if other statements are queued. HIGH_PRIORITY applies only if the storage engine only supports table-level locking (MyISAM, MEMORY, MERGE). See for details.

SQL_CACHE / SQL_NO_CACHE

If the system variable is set to 2 or DEMAND, and the current statement is cacheable, SQL_CACHE causes the query to be cached and SQL_NO_CACHE causes the query not to be cached. For UNIONs, SQL_CACHE or SQL_NO_CACHE should be specified for the first query. See also for more detail and a list of the types of statements that aren't cacheable.

SQL_BUFFER_RESULT

SQL_BUFFER_RESULT forces the optimizer to use a temporary table to process the result. This is useful to free locks as soon as possible.

SQL_SMALL_RESULT / SQL_BIG_RESULT

SQL_SMALL_RESULT and SQL_BIG_RESULT tell the optimizer whether the result is very big or not. Usually, GROUP BY and DISTINCT operations are performed using a temporary table. Only if the result is very big, using a temporary table is not convenient. The optimizer automatically knows if the result is too big, but you can force the optimizer to use a temporary table with SQL_SMALL_RESULT, or avoid the temporary table using SQL_BIG_RESULT.

STRAIGHT_JOIN

STRAIGHT_JOIN applies to the queries, and tells the optimizer that the tables must be read in the order they appear in the SELECT. For const and system table this options is sometimes ignored.

SQL_CALC_FOUND_ROWS

SQL_CALC_FOUND_ROWS is only applied when using the LIMIT clause. If this option is used, MariaDB will count how many rows would match the query, without the LIMIT clause. That number can be retrieved in the next query, using .

USE/FORCE/IGNORE INDEX

USE INDEX, FORCE INDEX and IGNORE INDEX constrain the query planning to a specific index. For further information about some of these options, see .

Description

Forcing an index to be used is mostly useful when the optimizer decides to do a table scan even if you know that using an index would be better. (The optimizer could decide to do a table scan even if there is an available index when it believes that most or all rows will match and it can avoid the overhead of using the index).

FORCE INDEX works by only considering the given indexes (like with USE_INDEX) but in addition it tells the optimizer to regard a table scan as something very expensive. However if none of the 'forced' indexes can be used, then a table scan will be used anyway.

FORCE INDEX cannot force an ignored index to be used - it will be treated as if it doesn't exist.

Example

This produces:

Index Prefixes

When using index hints (USE, FORCE or IGNORE INDEX), the index name value can also be an unambiguous prefix of an index name.

See Also

• for more details

_{This page is licensed: CC BY-SA / Gnu FDL}

You can limit which indexes are considered with the USE INDEX option.

Syntax

Description

The default is 'FOR JOIN', which means that the hint only affects how the WHERE clause is optimized.

USE INDEX is used after the table name in the FROM clause.

USE INDEX cannot use an - it will be treated as if it doesn't exist.

Index Prefixes

When using index hints (USE, FORCE or IGNORE INDEX), the index name value can also be an unambiguous prefix of an index name.

Example

This produces:

If we had not used USE INDEX, the Name index would have been in possible keys.

See Also

• for more details

_{This page is licensed: CC BY-SA / Gnu FDL}

Syntax

Description

You can tell the optimizer to not consider a particular index with the IGNORE INDEX option.

The benefit of using IGNORE_INDEX instead of USE_INDEX is that it will not disable a new index which you may add later.

Also see for an option to specify in the index definition that indexes should be ignored.

Index Prefixes

When using index hints (USE, FORCE or IGNORE INDEX), the index name value can also be an unambiguous prefix of an index name.

Example

This is used after the table name in the FROM clause:

This produces:

See Also

• See for more details

_{This page is licensed: CC BY-SA / Gnu FDL}

This article documents thread states that are related to the connection thread that processes statements.

These correspond to the STATE values listed by the statement or in the as well as the PROCESSLIST_STATE value listed in the .

index_merge is a method used by the optimizer to retrieve rows from a single table using several index scans. The results of the scans are then merged.

When using , if index_merge is the plan chosen by the optimizer, it will show up in the "type" column. For example:

The "rows" column gives us a way to compare efficiency between index_merge and other plans.

It is sometimes necessary to discard index_merge in favor of a different plan to avoid a combinatorial explosion of possible range and/or index_merge strategies. But, the old logic in MySQL for when index_merge was rejected caused some good index_merge plans to not even be considered. Specifically, additional AND predicates in WHERE clauses could cause an index_merge plan to be rejected in favor of a less efficient plan. The slowdown could be anywhere from 10x to over 100x. Here are two examples (based on the previous query) using MySQL:

Available tuning for ORDER BY with small LIMIT

• In 2024, fix for has added into MariaDB starting from 10.6. It allows one to enable extra optimization for ORDER BY with small LIMIT.

LooseScan is an execution strategy for .

The idea

We will demonstrate the LooseScan strategy by example. Suppose, we're looking for countries that have satellites. We can get them using the following query (for the sake of simplicity we ignore satellites that are owned by consortiums of multiple countries):

Suppose, there is an index on Satellite.country_code. If we use that index, we will get satellites in the order of their owner country:

Prior to , the index_merge access method supported union,sort-union, and intersection operations. Starting from , thesort-intersection operation is also supported. This allows the use ofindex_merge in a broader number of cases.

This feature is disabled by default. To enable it, turn on the optimizer switch index_merge_sort_intersection like so:

This refers to the index_type definition when creating an index, i.e. BTREE, HASH or RTREE.

For more information on general types of indexes, such as primary keys, unique indexes etc, go to .

MariaDB starting with

Compressions plugins were added in a preview release.

The various MariaDB storage engines, such as , , , can use different compression libraries.

Before , each separate library would have to be compiled in order to be available for use, resulting in numerous runtime/rpm/deb dependencies, most of which would never be used by users.

From , five additional MariaDB compression libraries (besides the default zlib) are available as plugins (note that these affect InnoDB and Mroonga only; RocksDB still uses the compression algorithms from its own library):

• bzip2

Basics

One can use DISTINCT keyword to de-duplicate the arguments of an aggregate function. For example:

In order to compute this, MariaDB has to collect the values of col1 and remove the duplicates. This may be computationally expensive.

After the fix for (available from , , , , , , ), the optimizer can detect certain cases when argument of aggregate function will not have duplicates and so de-duplication can be skipped.

MariaDB starting with

The hash_join_cardinality optimizer_switch flag was added in , , , , and .

In MySQL and MariaDB, the output cardinality of a part of query has historically been tied to the used access method(s). This is different from the approach used in database textbooks. There, the cardinality "x JOIN y" is the same regardless of which access methods are used to compute it.

Example

Consider a query joining customers with their orders:

Starting from , conditions in the form

are sargable, provided that

• CMP is any of =, <=>, <, <=, >, >= .

This is another name for Lateral Derived Optimization.

_{This page is licensed: CC BY-SA / Gnu FDL}

This article documents thread states that are related to replica I/O threads. These correspond to the Slave_IO_State shown by SHOW REPLICA STATUS and the STATE values listed by the SHOW PROCESSLIST statement or in the Information Schema PROCESSLIST Table as well as the PROCESSLIST_STATE value listed in the Performance Schema threads Table.

_{This page is licensed: CC BY-SA / Gnu FDL}

How Index Statistics Help the Query Optimizer

Understanding index statistics is crucial for the MariaDB query optimizer to efficiently execute queries. Accurate and current statistics guide the optimizer in choosing the best way to access data, similar to using a personal address book for quicker searches rather than a larger phone book. Up-to-date index statistics ensure optimized query performance.

Value Groups

The statistics primarily focus on groups of index elements with identical values. In a primary key, each index is unique, resulting in a group size of one. In a non-unique index, multiple keys may share the same value. The worst-case scenario involves large groups with identical values, such as an index on a boolean field.

MariaDB makes heavy use of the average group size statistic. For example, if there are 100 rows, and twenty groups with the same index values, the average group size would be five.

However, averages can be skewed by extremes, and the usual culprit is NULL values. The row of 100 may have 19 groups with an average size of 1, while the other 81 values are all NULL. MariaDB may think five is a good average size and choose to use that index, and then end up having to read through 81 rows with identical keys, taking longer than an alternative.

Dealing with NULLs

There are three main approaches to the problem of NULLs. NULL index values can be treated as a single group (nulls_equal). This is usually fine, but if you have large numbers of NULLs the average group size is slanted higher, and the optimizer may miss using the index for ref accesses when it would be useful. This is the default used by . The opposite approach is nulls_unequal, with each NULL forming its own group of one. Conversely, the average group size is slanted lower, and the optimizer may use the index for ref accesses when not suitable. This is the default used by the and storage engines. A third option, nulls_ignored, sees NULLs ignored altogether from index group calculations.

The default approaches can be changed by setting the , and server variables.

Null-Safe and Regular Comparisons

The comparison operator used plays an important role. If two values are compared with <=> (see the comparison operator), and both are null, 1 is returned. If the same values are compared with = (see the comparison operator) null is returned. For example:

Engine-Independent Statistics

introduced a way to gather statistics independently of the storage engine. See .

Histogram-Based Statistics

were introduced in , and are collected by default from .

See Also

• . This plugin provides user, client, table and index usage statistics.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

introduced the max_statement_time system variable. When set to a non-zero value, the server attempts to abort any queries taking longer than this time in seconds.

The default is zero, and no limits are then applied. The aborted query has no effect on any larger transaction or connection contexts. The variable is of type double, thus you can use subsecond timeout. For example you can use value 0.01 for 10 milliseconds timeout.

The value can be set globally or per session, as well as per user or per query (see below). Replicas are not affected by this variable, however from , there is which serves the same purpose on replicas only.

An associated status variable, , stores the number of queries that have exceeded the execution time specified by , and a MAX_STATEMENT_TIME_EXCEEDED column was added to the and Information Schema tables.

The feature was based upon a patch by Davi Arnaut.

User

can be stored per user with the syntax.

Per-query

By using in conjunction with , it is possible to limit the execution time of individual queries. For example:

max_statement_time per query Individual queries can also be limited by adding a MAX_STATEMENT_TIME clause to the query. For example:

Limitations

• does not work in embedded servers.

• does not work for statements in a Galera cluster (see for discussion).

• Check Intervals: The timeout is checked only at specific points during query execution. Queries stuck in operations where the check code path is not hit will not abort until they reach a checkpoint. This can result in query times exceeding the MAX_STATEMENT_TIME value.

Differences Between the MariaDB and MySQL Implementations

MySQL 5.7.4 introduced similar functionality, but the MariaDB implementation differs in a number of ways.

• The MySQL version of (max_execution_time) is defined in millseconds, not seconds

• MySQL's implementation can only kill SELECTs, while MariaDB's can kill any queries (excluding stored procedures).

• MariaDB only introduced the status variable, while MySQL also introduced a number of other variables which were not seen as necessary in MariaDB.

See Also

• variable

_{This page is licensed: CC BY-SA / Gnu FDL}

If a query uses a derived table (or a view), the first action that the query optimizer will attempt is to apply the derived-table-merge-optimization and merge the derived table into its parent select. However, that optimization is only applicable when the select inside the derived table has a join as the top-level operation. If it has a GROUP-BY, DISTINCT, or uses window functions, then derived-table-merge-optimization is not applicable.

In that case, the Condition Pushdown optimization is applicable.

Introduction to Condition Pushdown

Consider an example

The naive way to execute the above is to

1. Compute the OCT_TOTALS contents (for all customers).

2. The, select the line with customer_id=1

This is obviously inefficient, if there are 1000 customers, then one will be doing up to 1000 times more work than necessary.

However, the optimizer can take the condition customer_id=1 and push it down into the OCT_TOTALS view.

Inside the OCT_\TOTALS, the added condition is put into its HAVING clause, so we end up with:

Then, parts of HAVING clause that refer to GROUP BY columns are moved into the WHERE clause:

Once a restriction like customer_id=1 is in the WHERE, the query optimizer can use it to construct efficient table access paths.

Controlling the Optimization

The optimization is enabled by default. One can disable it by setting the flag condition_pushdown_for_derived to OFF.

The pushdown from HAVING to WHERE part is controlled by condition_pushdown_from_having flag in .

See Also

• Condition Pushdown through Window Functions (since )

• (since )

• The Jira task for the feature is .

_{This page is licensed: CC BY-SA / Gnu FDL}

Background

Users of "big" database systems are used to using FROM subqueries as a way to structure their queries. For example, if one's first thought was to select cities with population greater than 10,000 people, and then that from these cities to select those that are located in Germany, one could write this SQL:

For MySQL, using such syntax was taboo. If you run EXPLAIN for this query, you can see why:

It plans to do the following actions:

From left to right:

1. Execute the subquery: (SELECT * FROM City WHERE Population > 1*1000), exactly as it was written in the query.

2. Put result of the subquery into a temporary table.

3. Read back, and apply a WHERE condition from the upper select, big_city.Country='DEU'

Executing a subquery like this is very inefficient, because the highly-selective condition from the parent select, (Country='DEU') is not used when scanning the base table City. We read too many records from theCity table, and then we have to write them into a temporary table and read them back again, before finally filtering them out.

Derived table merge in action

If one runs this query in MariaDB/MySQL 5.6, they get this:

From the above, one can see that:

1. The output has only one line. This means that the subquery has been merged into the top-level SELECT.

2. Table City is accessed through an index on the Country column. Apparently, the Country='DEU' condition was used to construct ref access on the table.

Factsheet

• Derived tables (subqueries in the FROM clause) can be merged into their parent select when they have no grouping, aggregates, or ORDER BY ... LIMIT clauses. These requirements are the same as requirements for VIEWs to allow algorithm=merge.

• The optimization is enabled by default. It can be disabled with:

• Versions of MySQL and MariaDB which do not have support for this optimization will execute subqueries even when running EXPLAIN. This can result in a well-known problem (see e.g. ) of EXPLAIN statements taking a very long time. Starting from + and MySQL 5.6+ EXPLAIN commands execute instantly, regardless of the derived_merge setting.

See Also

• FAQ entry:

_{This page is licensed: CC BY-SA / Gnu FDL}

The idea

If a derived table cannot be merged into its parent SELECT, it will be materialized in a temporary table, and then parent select will treat it as a regular base table.

Before /MySQL 5.6, the temporary table would never have any indexes, and the only way to read records from it would be a full table scan. Starting from the mentioned versions of the server, the optimizer has an option to create an index and use it for joins with other tables.

Example

Consider a query: we want to find countries in Europe, that have more than one million people living in cities. This is accomplished with this query:

The EXPLAIN output for it will show:

One can see here that

• table <derived2> is accessed through key0.

• ref column shows world.Country.Code

• if we look that up in the original query, we find the equality that was used to construct ref access: Country.Code=cities_in_country.Country

Factsheet

• The idea of "derived table with key" optimization is to let the materialized derived table have one key which is used for joins with other tables.

• The optimization is applied then the derived table could not be merged into its parent SELECT

  • which happens when the derived table doesn't meet criteria for mergeable VIEW

• The optimization is ON by default; it can be switched off like so:

See Also

• in MySQL 5.6 manual

_{This page is licensed: CC BY-SA / Gnu FDL}

Optimization Description

When n is sufficiently small, the optimizer will use a priority queue for sorting. Before the optimization's porting to , the alternative was, roughly speaking, to sort the entire output and then pick only first n rows.

NOTE: The problem of choosing which index to use for query with ORDER BY ... LIMIT is a different problem, see optimizer_join_limit_pref_ratio-optimization.

Optimization Visibility in MariaDB

There are two ways to check whether filesort has used a priority queue.

Status Variable

The first way is to check the status variable. It shows the number of times that sorting was done through a priority queue. (The total number of times sorting was done is a sum and ).

Slow Query Log

The second way is to check the slow query log. When one uses and specifies , entries look like this

Note the "Priority_queue: Yes" on the last comment line. (pt-query-digest is able to parse slow query logs with the Priority_queue field)

As for EXPLAIN, it will give no indication whether filesort uses priority queue or the generic quicksort and merge algorithm. Using filesort will be shown in both cases, by both MariaDB and MySQL.

See Also

• page in the MySQL 5.6 manual (search for "priority queue").

• MySQL WorkLog entry,

• ,

_{This page is licensed: CC BY-SA / Gnu FDL}

Starting from , expressions in the form

are sargable if key_col uses either the utf8mb3_general_ci or utf8mb4_general_ci collation.

UCASE is a synonym for UPPER so is covered as well.

Sargable means that the optimizer is able to use such conditions to construct access methods, estimate their selectivity, or perform partition pruning.

Example

Note that ref access is used.

An example with join:

Here, the optimizer was able to construct ref access.

Controlling the Optimization

The variable has the flag sargable_casefold to turn the optimization on and off. The default is ON.

Optimizer Trace

The optimization is implemented as a rewrite for a query's WHERE/ON conditions. It uses the sargable_casefold_removal object name in the trace:

References

• : Make optimizer handle UCASE(varchar_col)=...

• An analog for is not possible. See : Make optimizer handle LCASE(varchar_col)=... for details.

_{This page is licensed: CC BY-SA / Gnu FDL}

A thread can have any of the following COMMAND values (displayed by the COMMAND field listed by the statement or in the , as well as the PROCESSLIST_COMMAND value listed in the ). These indicate the nature of the thread's activity.

Storage-engine independent column compression enables , , , , , , , , and columns to be compressed.

This is performed by means of a new COMPRESSED :COMPRESSED[=<compression_method>]

Currently the only supported compression method is zlib.

Field Length Compatibility

FirstMatch is an execution strategy for .

The idea

It is very similar to how IN/EXISTS subqueries were executed in MySQL 5.x.

Let's take the usual example of a search for countries with big cities:

Suppose, our execution plan is to find countries in Europe, and then, for each found country, check if it has any big cities. Regular inner join execution will look as follows:

The problem space

Let's say you have "news articles" (rows in a table) and want a web page showing the latest ten articles about a particular topic.

Variants on "topic":

• Category

Syntax

Description

OPTIMIZE TABLE has two main functions. It can either be used to defragment tables, or to update the InnoDB fulltext index.

Basic idea

Consider a query with a WHERE clause:

the WHERE clause will compute to true only if col1=col2. This means that in the rest of the WHERE clause occurrences of col1 can be substituted with col2 (with some limitations which are discussed in the next section). This allows the optimizer to infer additional restrictions.

For example:

allows to infer a new equality:

The Problem

The normal way to count "Unique Users" is to take large log files, sort by userid, dedup, and count. This requires a rather large amount of processing. Furthermore, the count derived cannot be rolled up. That is, daily counts cannot be added to get weekly counts -- some users will be counted multiple times.

So, the problem is to store the counts is such a way as to allow rolling up.

Ignored indexes are indexes that are visible and maintained, but which are not used by the optimizer. MySQL 8 has a similar feature which they call "invisible indexes".

Syntax

By default, an index is not ignored. One can mark existing index as ignored (or not ignored) with an statement:

It is also possible to specify IGNORED attribute when creating an index with a , or statement:

table's primary key cannot be ignored. This applies to both explicitly defined primary key, as well as implicit primary key - if there is no explicit primary key defined but the table has a unique key containing only NOT NULL columns, the first of such keys becomes the implicitly defined primary key.

Handling for ignored indexes

The optimizer will treats ignored indexes as if they didn't exist. They will not be used in the query plans, or as a source of statistical information. Also, an attempt to use an ignored index in a USE INDEX, FORCE INDEX, or IGNORE INDEX hint will result in an error - the same what would have if one used a name of a non-existent index.

Information about whether or not indexes are ignored can be viewed in the IGNORED column in the or the statement.

Intended Usage

The primary use case is as follows: a DBA sees an index that seems to have little or no usage and considers whether to remove it. Dropping the index is a risk as it may still be needed in a few cases. For example, the optimizer may rely on the estimates provided by the index without using the index in query plans. If dropping an index causes an issue, it will take a while to re-create the index. On the other hand, marking the index as ignored (or not ignored) is instant, so the suggested workflow is:

1. Mark the index as ignored

2. Check if everything continues to work

3. If not, mark the index as not ignored.

4. If everything continues to work, one can safely drop the index.

Examples

The optimizer does not make use of an index when it is ignored, while if the index is not ignored (the default), the optimizer will consider it in the optimizer plan, as shown in the output.

_{This page is licensed: CC BY-SA / Gnu FDL}

optimizer_adjust_secondary_key_costs

• Description: Gives the user the ability to affect how the costs for secondary keys using ref are calculated in the few cases when MariaDB 10.6 up to MariaDB 10.11 makes a sub-optimal choice when optimizing ref access, either for key lookups or GROUP BY. ref, as used by , means that the optimizer is using key-lookup on one value to find the matching rows from a table. Unused from . In the variable was changed from a number to a set of strings and disable_forced_index_in_group_by (value 4) was added.

• Scope: Global, Session

• Dynamic: Yes

• Data Type: set

• Default Value: fix_reuse_range_for_ref, fix_card_multiplier

• Range: 0 to 63 or any combination of adjust_secondary_key_cost, disable_max_seek or disable_forced_index_in_group_by, fix_innodb_cardinality,fix_reuse_range_for_ref, fix_card_multiplier

• Introduced: ,

MariaDB starting with

optimizer_adjust_secondary_key_costs will be obsolete starting from as the new optimizer in 11.0 does not have max_seek optimization and is already using cost based choices for index usage with GROUP BY.

The value for optimizer_adjust_secondary_key_costs is one of more of the following:

One can set all options with:

Explanations of the old behavior in MariaDB 10.x

The reason for the max_seek optimization was originally to ensure that MariaDB would use a key instead of a table scan. This works well for a lot of queries, but can cause problems when a table scan is a better choice, such as when one would have to scan more than 1/4 of the rows in the table (in which case a table scan is better).

See Also

• The system variable.

_{This page is licensed: CC BY-SA / Gnu FDL}

The target use case for rowid filtering is as follows:

• a table uses ref access on index IDX1

• but it also has a fairly restrictive range predicate on another index IDX2.

In this case, it is advantageous to:

• Do an index-only scan on index IDX2 and collect rowids of index records into a data structure that allows filtering (let's call it $FILTER).

• When doing ref access on IDX1, check $FILTER before reading the full record.

Example

Consider a query

Suppose the condition on l_shipdate is very restrictive, which means lineitem table should go first in the join order. Then, the optimizer can use o_orderkey=l_orderkey equality to do an index lookup to get the order the line item is from. On the other hand o_totalprice between ... can also be rather selective.

With filtering, the query plan would be:

Note that table orders has "Using rowid filter". The type column has "|filter", the key column shows the index that is used to construct the filter. rows column shows the expected filter selectivity, it is 5%.

ANALYZE FORMAT=JSON output for table orders will show

Note the rowid_filter element. It has a range element inside it. selectivity_pct is the expected selectivity, accompanied by the r_selectivity_pct showing the actual observed selectivity.

Details

• The optimizer makes a cost-based decision about when the filter should be used.

• The filter data structure is currently an ordered array of rowids. (a Bloom filter would be better here and will probably be introduced in the future versions).

• The optimization needs to be supported by the storage engine. At the moment, it is supported by and . It is not supported in .

Limitations

• Rowid Filtering can't be used with a backward-ordered index scan. When the optimizer needs to execute an ORDER BY ... DESC query and decides to handle it with a backward-ordered index scan, it will disable Rowid Filtering.

Control

Rowid filtering can be switched on/off using rowid_filter flag in the variable. By default, the optimization is enabled.

_{This page is licensed: CC BY-SA / Gnu FDL}

Basics

Off (0) by default, when enabling this optimization, MariaDB will consider a join order that may shorten query execution time based on the ORDER BY ... LIMIT n clause. For small values of n, this may improve performance.

Set the value of optimizer_join_limit_pref_ratio to a non-zero value to enable this option (higher values are more conservative, recommended value is 100), or set to 0 (the default value) to disable it.

Detailed description

Problem setting

By default, the MariaDB optimizer picks a join order without considering the ORDER BY ... LIMIT clause, when present.

For example, consider a query looking at latest 10 orders together with customers who made them:

The two possible plans are:customer->orders:

and orders->customer:

The customer->orders plan computes a join between all customers and orders, saves that result into a temporary table, and then uses filesort to get the 10 most recent orders. This query plan doesn't benefit from the fact that just 10 orders are needed.

However, in contrast, the orders->customers plan uses an index to read rows in the ORDER BY order. The query can stop execution once it finds 10 order-and-customer combinations. This is much faster than computing the entire join. Under this plan, and when this new optimization, we can leverage ORDER BY ... LIMIT to stop early, when we have the 10 combinations.

Plans with LIMIT shortcuts are difficult to estimate

It is fundamentally difficult to produce a reliable estimate for ORDER BY ... LIMIT shortcuts. Let's take an example from the previous section to see why. This query searches for last 10 orders that were shipped by air:

Suppose we know beforehand that 50% of orders are shipped by air. Assuming there's no correlation between date and shipping method, orders->customer plan will need to scan 20 orders before we find 10 that are shipped by air. But if there is correlation, then we may need to scan up to (total_orders*0.5 + 10) before we find first 10 orders that are shipped by air. Scanning about 50% of all orders can be expensive.

This situation worsens when the query has constructs whose selectivity is not known. For example, suppose the WHERE condition was

in this case, we can't reliably say whether we will be able to stop after scanning #LIMIT rows or we will need to enumerate all rows before we find #LIMIT matches.

Providing guidelines to the optimizer

Due to these challenges, the optimization is not enabled by default.

When running a mostly OLTP workload such that query WHERE conditions have suitable indexes or are not very selective, then any ORDER BY ... LIMIT queries will typically find matching rows quickly. In this case, it makes sense to give the following guidance to the optimizer:

The value of X is given to the optimizer via optimizer_join_limit_pref_ratio setting. Higher values carry less risk. The recommended value is 100: prefer the LIMIT join order if it promises at least 100x speedup.

References

• introduces optimizer_join_limit_pref_ratio optimization

• is about future development that would make the optimizer handle such cases without user guidance.

_{This page is licensed: CC BY-SA / Gnu FDL}

The optimizer can recognize use of indexed virtual column expressions in the WHERE clause and use them to construct range and ref(const) accesses.

Index Condition Pushdown is an optimization that is applied for access methods that access table data through indexes: range, ref, eq_ref, ref_or_null, and .

The idea is to check part of the WHERE condition that refers to index fields (we call it Pushed Index Condition) as soon as we've accessed the index. If the Pushed Index Condition is not satisfied, we won't need to read the whole table record.

Index Condition Pushdown is on by default. To disable it, set its optimizer_switch flag like so:

When Index Condition Pushdown is used, EXPLAIN will show "Using index condition":

DuplicateWeedout is an execution strategy for .

The idea

The idea is to run the semi-join (a query with uses WHERE X IN (SELECT Y FROM ...)) as if it were a regular inner join, and then eliminate the duplicate record combinations using a temporary table.

Suppose, you have a query where you're looking for countries which have more than 33% percent of their population in one big city:

First, we run a regular inner join between the City

The NOT NULL range scan optimization enables the optimizer to construct range scans from NOT NULL conditions that it was able to infer from the WHERE clause.

The optimization appeared in . It is not enabled by default; one needs to set an optimizer_switch flag to enable it.

Description

A basic (but slightly artificial) example:

The WHERE condition in this form cannot be used for range scans. However, one can infer that it will reject rows that NULL for weight. That is, infer an additional condition that

The situation

Your data includes a large set of non-overlapping 'ranges'. These could be IP addresses, datetimes (show times for a single station), zipcodes, etc.

You have pairs of start and end values; one 'item' belongs to each such 'range'. So, instinctively, you create a table with start and end of the range, plus info about the item. Your queries involve a WHERE clause that compares for being between the start and end values.

The problem

Once you get a large set of items, performance degrades. You play with the indexes, but find nothing that works well. The indexes fail to lead to optimal functioning because the database does not understand that the ranges are non-overlapping.

The solution

I will present a solution that enforces the fact that items cannot have overlapping ranges. The solution builds a table to take advantage of that, then uses Stored Routines to get around the clumsiness imposed by it.

Performance

The instinctive solution often leads to scanning half the table to do just about anything, such as finding the item containing an 'address'. In complexity terms, this is Order(N).

The solution here can usually get the desired information by fetching a single row, or a small number of rows. It is Order(1).

In a large table, "counting the disk hits" is the important part of performance. Since InnoDB is used, and the PRIMARY KEY (clustered) is used, most operations hit only 1 block.

Finding the 'block' where a given IP address lives:

• For start of block: One single-row fetch using the PRIMARY KEY

• For end of block: Ditto. The record containing this will be 'adjacent' to the other record.

For allocating or freeing a block:

• 2-7 SQL statements, hitting the clustered PRIMARY KEY for the rows containing and immediately adjacent to the block.

• One SQL statement is a DELETE; if hits as many rows as are needed for the block.

• The other statements hit one row each.

Design decisions

This is crucial to the design and its performance:

• Having just one address in the row. These were alternative designs; they seemed to be no better, and possibly worse:

• That one address could have been the 'end' address.

• The routine parameters for a 'block' could have be start of this block and start of next block.

• The IPv4 parameters could have been dotted quads; I chose to keep the reference implemetation simpler instead.

The interesting work is in the Ips, not the second table, so I focus on it. The inconvenience of JOINing to the second table is small compared to the performance gains.

Details

Two, not one, tables will be used. The first table (Ips in the reference implementations) is carefully designed to be optimal for all the basic operations needed. The second table contains other infomation about the 'owner' of each 'item'. In the reference implementations owner is an id used to JOIN the two tables. This discussion centers around Ips and how to efficiently map IP(s) to/from owner(s). The second table has "PRIMARY KEY(owner)".

In addition to the two-table schema, there are a set of Stored Routines to encapsulate the necessary code.

One row of Ips represents one 'item' by specifying the starting IP address and the 'owner'. The next row gives the starting IP address of the next "address block", thereby indirectly providing the ending address for the current block.

This lack of explicitly stating the "end address" leads to some clumsiness. The stored routines hide it from the user.

A special owner (indicated by '0') is reserved for "free" or "not-owned" blocks. Hence, sparse allocation of address blocks is no problem. Also, the 'free' owner is handled no differently than real owners, so there are no extra Stored Routines for such.

Links below give "reference" implementations for IPv4 and IPv6. You will need to make changes for non-IP situations, and may need to make changes even for IP situations.

These are the main stored routines provided:

• IpIncr, IpDecr -- for adding/subtracting 1

• IpStore -- for allocating/freeing a range

• IpOwner, IpRangeOwners, IpFindRanges, Owner2IpStarts, Owner2IpRanges -- for lookups

• IpNext, IpEnd -- IP of start of next block, or end of current block

None of the provided routines JOIN to the other table; you may wish to develop custom queries based on the given reference Stored Procedures.

The Ips table's size is proportional to the number of blocks. A million 'owned' blocks may be 20-50MB. This varies due to

• number of 'free' gaps (between zero and the number of owned blocks)

• datatypes used for ip and owner

• overhead Even 100M blocks is quite manageable in today's hardware. Once things are cached, most operations would take only a few milliseconds. A trillion blocks would work, but most operations would hit the disk a few times -- only a few times.

Reference implementation of IPv4

This specific to IPv4 (32 bit, a la '196.168.1.255'). It can handle anywhere from 'nothing assigned' (1 row) to 'everything assigned' (4B rows) 'equally' well. That is, to ask the question "who owns '11.22.33.44'" is equally efficient regardless of how many blocks of IP addresses exist in the table. (OK, caching, disk hits, etc may make a slight difference.) The one function that can vary is the one that reassigns a range to a new owner. Its speed is a function of how many existing ranges need to be consumed, since those rows will be DELETEd. (It helps that they are, by schema design, 'clustered'.)

Notes on the :

• Externally, the user may use the dotted quad notation (11.22.33.44), but needs to convert to INT UNSIGNED for calling the Stored Procs.

• The user is responsible for converting to/from the calling datatype (INT UNSIGNED) when accessing the stored routine; suggest /.

• The internal datatype for addresses is the same as the calling datatype (INT UNSIGNED).

• Adding and subtracting 1 (simple arithmetic).

(The reference implementation does not handle CDRs. Such should be easy to add on, by first turning it into an IP range.)

Reference implementation of IPv6

The code for handling IP address is more complex, but the overall structure is the same as for IPv4. Launch into it only if you need IPv6.

Notes on the :

• Externally, IPv6 has a complex string, VARCHAR(39) CHARACTER SET ASCII. The Stored Procedure IpStr2Hex() is provided.

• The user is responsible for converting to/from the calling datatype (BINARY(16)) when accessing the stored routine; suggest /.

• The internal datatype for addresses is the same as the calling datatype (BINARY(16)).

• Communication with the Stored routines is via 32-char hex strings.

The INET6* functions were first available in MySQL 5.6.3 and

Adapting to a different non-IP 'address range' data

• The external datatype for an 'address' should be whatever is convenient for the application.

• The datatype for the 'address' in the table must be ordered, and should be as compact as possible.

• You must write the Stored functions (IpIncr, IpDecr) for incrementing/decrementing an 'address'.

• An 'owner' is an id of your choosing, but smaller is better.

"Owner" needs a special value to represent "not owned". The reference implementations use "=" and "!=" to compare two 'owners'. Numeric values and strings work nicely with those operators; NULL does not. Hence, please do not use NULL for "not owned".

Since the datatypes are pervasive in the stored routines, adapting a reference implementation to a different concept of 'address' would require multiple minor changes.

The code enforces that consecutive blocks never have the same 'owner', so the table is of 'minimal' size. Your application can assume that such is always the case.

Postlog

Original writing -- Oct, 2012; Notes on INET6 functions -- May, 2015.

See also

Rick James graciously allowed us to use this article in the documentation.

has other useful tips, how-tos, optimizations, and debugging tips.

Original source:

_{This page is licensed: CC BY-SA / Gnu FDL}

Semi-join Materialization is a special kind of subquery materialization used for Semi-join subqueries. It actually includes two strategies:

• Materialization/lookup

• Materialization/scan

The idea

Consider a query that finds countries in Europe which have big cities:

The subquery is uncorrelated, that is, we can run it independently of the upper query. The idea of semi-join materialization is to do just that, and fill a temporary table with possible values of the City.country field of big cities, and then do a join with countries in Europe:

The join can be done in two directions:

1. From the materialized table to countries in Europe

2. From countries in Europe to the materialized table

The first way involves doing a full scan on the materialized table, so we call it "Materialization-scan".

If you run a join from Countries to the materialized table, the cheapest way to find a match in the materialized table is to make a lookup on its primary key (it has one: we used it to remove duplicates). Because of that, we call the strategy "Materialization-lookup".

Semi-join materialization in action

Materialization-Scan

If we chose to look for cities with a population greater than 7 million, the optimizer will use Materialization-Scan and EXPLAIN will show this:

Here, you can see:

• There are still two SELECTs (look for columns with id=1 and id=2)

• The second select (with id=2) has select_type=MATERIALIZED. This means it will be executed and its results will be stored in a temporary table with a unique key over all columns. The unique key is there to prevent the table from containing any duplicate records.