> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations.md).

# Optimizing Queries

{% columns %}
{% column %}
{% content-ref url="/pages/xn2R567v0OJWXuj4ubur" %}
[Aborting Statements that Exceed a Certain Time to Execute](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/aborting-statements.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
How to abort statements that exceed a maximum execution time.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/hOGQXHsBQrlKytZZPcFP" %}
[Big DELETEs](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/big-deletes.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Techniques for deleting large numbers of rows from big tables efficiently.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/XOprIEs7CPRsxkrQAjee" %}
[Charset Narrowing Optimization](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/charset-narrowing-optimization.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
The Charset Narrowing optimization, which speeds up equality comparisons between columns of different character sets.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/ZVG1OP22meYmHURQTRL3" %}
[Data Sampling: Techniques for Efficiently Finding a Random Row](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/data-sampling-techniques-for-efficiently-finding-a-random-row.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Efficient techniques for selecting random rows, avoiding a slow ORDER BY RAND().
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/tityUdQAMK2eZNoBXuy0" %}
[Data Warehousing High Speed Ingestion](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/data-warehousing-high-speed-ingestion.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Techniques for high-speed data ingestion when INSERT performance is the bottleneck.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/GkwHt6Qo49YfS8Qtd64e" %}
[Data Warehousing Summary Tables](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/data-warehousing-summary-tables.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Creating and maintaining summary tables to speed up data-warehouse queries.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/zZBssltGKVc0WXdzP3Fx" %}
[Data Warehousing Techniques](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/data-warehousing-techniques.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Techniques for improving performance of data-warehouse-style tables.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/edP58L6jYaE5ia6gd6dV" %}
[DISTINCT removal in aggregate functions](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/query-optimizations-distinct-removal-in-aggregate-functions.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
How the optimizer removes redundant DISTINCT from aggregate-function arguments.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/LKRAAxTMYQIwPVLZOvGe" %}
[Equality propagation optimization](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/equality-propagation-optimization.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
The equality propagation optimization, which propagates equality conditions through a query's WHERE clause.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/GL7PHlPHG65AuBZ51t00" %}
[Filesort with Small LIMIT Optimization](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/filesort-with-small-limit-optimization.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
The filesort optimization that uses a priority queue when sorting with a small LIMIT.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/cfySndh64IyPWcFB4dcr" %}
[FORCE INDEX](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/force-index.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Using FORCE INDEX to make the optimizer use an index instead of a table scan.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/P0YJePdLyGw6eUYGRjTR" %}
[Groupwise Max in MariaDB](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/groupwise-max-in-mariadb.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Techniques for finding the largest, or top, row within each group.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/X1vxLYRipYjBa6uzeqIa" %}
[GUID/UUID Performance](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/guiduuid-performance.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Why random GUID/UUID keys hurt index performance, and how to mitigate it.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/hKwkuYAaPBveyAsH0nje" %}
[hash\_join\_cardinality optimizer\_switch Flag](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/hash_join_cardinality-optimizer_switch-flag.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
The hash\_join\_cardinality optimizer\_switch flag, which affects hash-join cardinality estimates.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/FiGFbjJ2wxxfX7z5lER9" %}
[How to Quickly Insert Data Into MariaDB](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/how-to-quickly-insert-data-into-mariadb.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Techniques for inserting data into MariaDB as quickly as possible.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/MSwdRSgtGpMqsyRpA4Ev" %}
[IGNORE INDEX](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/ignore-index.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Using IGNORE INDEX to tell the optimizer not to consider specific indexes.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/n8kb1e9elzTJZ6VDhmUz" %}
[Index Condition Pushdown](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/index-condition-pushdown.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Index Condition Pushdown, which pushes WHERE conditions into the index scan to reduce row reads.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/B5W7S8nddeoEXS4waSpJ" %}
[Index Hints: How to Force Query Plans](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/index-hints-how-to-force-query-plans.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Using index hints to influence the query plan when the optimizer's choice is not ideal.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/1tGLEVQKB7pHbVocJS9H" %}
[index\_merge sort\_intersection](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/index_merge-sort_intersection.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
The index\_merge sort\_intersection access method, which merges results from several index scans.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/dCwbMsFm4Lq3ZxS3ZjOW" %}
[LIMIT ROWS EXAMINED](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/limit-rows-examined.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Using LIMIT ROWS EXAMINED to cap how many rows a query examines.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/AmsiRQm9ULRTDaUlsIh6" %}
[MariaDB 5.3 Optimizer Debugging](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/mariadb-53-optimizer-debugging.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
An obsolete optimizer-debugging facility from an early MariaDB release (historical).
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/Hl7D6tR9cCgPhldtqmcm" %}
[not\_null\_range\_scan Optimization](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/not_null_range_scan-optimization.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
The not\_null\_range\_scan optimization, which builds range scans from inferred NOT NULL conditions.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/F0DXnccg4Dj1CcHNzBC8" %}
[optimizer\_switch](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimizer-switch.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
The optimizer\_switch system variable, used to enable or disable individual optimizations.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/1jEoXytKVGTuFLAvxS1D" %}
[optimizer\_adjust\_secondary\_key\_costs](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimizer_adjust_secondary_key_costs.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
The optimizer\_adjust\_secondary\_key\_costs setting, which tunes cost estimates for secondary keys.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/FxB9G0MZp8RLWe7MzWBA" %}
[optimizer\_join\_limit\_pref\_ratio Optimization](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimizer_join_limit_pref_ratio-optimization.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
An optimization that can pick a join order which shortens ORDER BY ... LIMIT queries.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/e5uQHEUdFDG9qrRpxzVI" %}
[Optimizing for "Latest News"-style Queries](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimizing-for-latest-news-style-queries.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Optimizing queries that fetch the latest items on a topic, such as news feeds.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/0LkJDd5baeKRg82UM1su" %}
[Pagination Optimization](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/pagination-optimization.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Efficient pagination techniques for splitting long lists across pages.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/aqOY8AZwp9vC3rJtfLng" %}
[Pivoting in MariaDB](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/pivoting-in-mariadb.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Techniques for pivoting row data into a spreadsheet-like columnar layout.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/TemNGlr3351av5vVONsY" %}
[Query Limits and Timeouts](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/query-limits-and-timeouts.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
The methods MariaDB provides to limit or time out queries.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/WMtDJGPpKXydZO8mi85Q" %}
[reorder\_outer\_joins](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/reorder_outer_joins.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
The reorder\_outer\_joins optimizer\_switch flag, which controls reordering of independent outer joins.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/HSYMsn4w5AwqhpAQ1S0P" %}
[Rollup Unique User Counts](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/rollup-unique-user-counts.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Efficiently rolling up unique-user counts without repeatedly reprocessing large logs.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/rBH2cpzOcqP3TjysxTGs" %}
[Rowid Filtering Optimization](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/rowid-filtering-optimization.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
The rowid filtering optimization, which pre-filters rowids to reduce expensive row lookups.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/6xW4zMOiziu1NjQkMMGj" %}
[Sargable DATE and YEAR](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/sargable-date-and-year.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
The sargable DATE and YEAR optimization, letting the optimizer use indexes for certain date and year conditions.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/ouSdZ6Bh3C7xNQTyuZOo" %}
[Sargable UPPER](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/sargable-upper.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
The sargable UPPER optimization, letting the optimizer use indexes for UPPER() expressions.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/FP2smqL9gbRvodpQgHtT" %}
[USE INDEX](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/use-index.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Using USE INDEX to limit which indexes the optimizer considers.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/sXo0YKGuXmMm3h9lhzHH" %}
[Virtual Column Support in the Optimizer](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/virtual-column-support-in-the-optimizer.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Optimizer support for virtual (generated) columns.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/KRPliZ9fu3N0EjnZ3DxR" %}
[Optimization Strategies](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimization-strategies.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Discover effective optimization strategies for MariaDB Server queries. This section provides a variety of techniques and approaches to enhance query performance and overall database efficiency.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/HtOTK1OHGFdna8usbZjc" %}
[Optimizations for Derived Tables](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimizations-for-derived-tables.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Optimize derived tables in MariaDB Server queries. This section provides techniques and strategies to improve the performance of subqueries and complex joins, enhancing overall query efficiency.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/RCVafXc1mNPCk5CaLdII" %}
[Statistics for Optimizing Queries](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/statistics-for-optimizing-queries.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Utilize statistics to optimize queries in MariaDB Server. This section explains how the database uses statistical information to generate efficient query execution plans and improve performance.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/plHV2PnqhFK2CF6AthHs" %}
[Subquery Optimizations](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Optimize subqueries in MariaDB Server for improved performance. This section provides techniques and best practices to ensure your nested queries execute efficiently and enhance overall query speed.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/WWNDm65tckFBfTPPrHfK" %}
[Table Elimination](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/table-elimination.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Learn about table elimination for query optimization in MariaDB Server. This section explains how the optimizer removes unnecessary tables from query plans, improving performance.
{% endcolumn %}
{% endcolumns %}