Optimizing Queries
Optimize queries for peak performance. This section provides techniques for writing efficient SQL, understanding query execution plans, and leveraging indexes effectively to speed up your queries.
Last updated
Was this helpful?
Optimize queries for peak performance. This section provides techniques for writing efficient SQL, understanding query execution plans, and leveraging indexes effectively to speed up your queries.
How to abort statements that exceed a maximum execution time.
Techniques for deleting large numbers of rows from big tables efficiently.
The Charset Narrowing optimization, which speeds up equality comparisons between columns of different character sets.
Efficient techniques for selecting random rows, avoiding a slow ORDER BY RAND().
Techniques for high-speed data ingestion when INSERT performance is the bottleneck.
Creating and maintaining summary tables to speed up data-warehouse queries.
Techniques for improving performance of data-warehouse-style tables.
How the optimizer removes redundant DISTINCT from aggregate-function arguments.
The equality propagation optimization, which propagates equality conditions through a query's WHERE clause.
The filesort optimization that uses a priority queue when sorting with a small LIMIT.
Using FORCE INDEX to make the optimizer use an index instead of a table scan.
Techniques for finding the largest, or top, row within each group.
Why random GUID/UUID keys hurt index performance, and how to mitigate it.
The hash_join_cardinality optimizer_switch flag, which affects hash-join cardinality estimates.
Techniques for inserting data into MariaDB as quickly as possible.
Using IGNORE INDEX to tell the optimizer not to consider specific indexes.
Index Condition Pushdown, which pushes WHERE conditions into the index scan to reduce row reads.
Using index hints to influence the query plan when the optimizer's choice is not ideal.
The index_merge sort_intersection access method, which merges results from several index scans.
Using LIMIT ROWS EXAMINED to cap how many rows a query examines.
An obsolete optimizer-debugging facility from an early MariaDB release (historical).
The not_null_range_scan optimization, which builds range scans from inferred NOT NULL conditions.
The optimizer_switch system variable, used to enable or disable individual optimizations.
The optimizer_adjust_secondary_key_costs setting, which tunes cost estimates for secondary keys.
An optimization that can pick a join order which shortens ORDER BY ... LIMIT queries.
Optimizing queries that fetch the latest items on a topic, such as news feeds.
Efficient pagination techniques for splitting long lists across pages.
Techniques for pivoting row data into a spreadsheet-like columnar layout.
The methods MariaDB provides to limit or time out queries.
The reorder_outer_joins optimizer_switch flag, which controls reordering of independent outer joins.
Efficiently rolling up unique-user counts without repeatedly reprocessing large logs.
The rowid filtering optimization, which pre-filters rowids to reduce expensive row lookups.
The sargable DATE and YEAR optimization, letting the optimizer use indexes for certain date and year conditions.
The sargable UPPER optimization, letting the optimizer use indexes for UPPER() expressions.
Using USE INDEX to limit which indexes the optimizer considers.
Optimizer support for virtual (generated) columns.
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.
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.
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.
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.
Learn about table elimination for query optimization in MariaDB Server. This section explains how the optimizer removes unnecessary tables from query plans, improving performance.
This page is licensed: CC BY-SA / Gnu FDL
Last updated
Was this helpful?
Was this helpful?