The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →To speed up a slow MySQL or MariaDB query, first capture the real query and run EXPLAIN. Then add the narrowest single-column or composite index that matches its recurring filters, joins, ordering, or grouping; run EXPLAIN again; and keep the index only if it reduces work on representative data. An index can turn a full-table scan into a selective lookup, but it also consumes storage and makes writes more expensive, so indexing every column is not a solution.
Antihost offers Linux VPS plans with full root access, unlimited traffic on a 1 Gbps port and a 14-day free trial.
What an index does—and what it cannot do
An index is a separately maintained data structure that provides an alternate access path to table rows. Instead of checking every row to find matches, the optimizer can traverse the index entries for a predicate and then retrieve only the qualifying rows. The Oracle MySQL Reference Manual describes the principle directly: “The best way to improve the performance of SELECT operations is to create indexes on one or more of the columns that are tested in the query.” It also explains that “The index entries act like pointers to the table rows.”
As an Amazon Associate I earn from qualifying purchases.
That shortcut matters when a query is selective: the condition matches a relatively small portion of the table. For a query that must return most rows, walking an index and then fetching those rows can cost more than reading the table sequentially. The optimizer may therefore choose a table scan even when a suitable index exists.
Indexes are not free. Every index occupies disk and memory, and each INSERT, UPDATE, or DELETE may have to update several index structures. Redundant or rarely used indexes slow write-heavy workloads and increase backup and maintenance time. Design indexes for actual query patterns rather than for every column that appears in a table.
#1 Best Overall
Start with the query, not the table definition
Index design should begin with a slow statement and its workload context. Save the exact SQL, representative parameter values, expected result size, and how often it runs. A query that is fast for one value can be slow for another if the data distribution is uneven.
- Predicates: record columns used in
WHERE, including equality, range,IN, and nullable comparisons. - Joins: record both sides of each join condition and note which table is expected to provide the selective lookup.
- Ordering and grouping: capture every column and direction in
ORDER BYandGROUP BY. - Projection: list columns in
SELECT; they determine whether a covering index is possible. - Limits and frequency: a frequently executed query returning 20 rows deserves different trade-offs from a once-a-day report returning millions.
Do not create an index merely because a column appears in a table or because it is mentioned once in a query. First establish that the statement is a recurring bottleneck and identify the rows it actually needs to examine.
Measure the baseline with EXPLAIN
Run an execution plan before changing the schema. In both MySQL and MariaDB, the basic form is:
EXPLAIN SELECT order_id, total
FROM orders
WHERE customer_id = 42;Use the version-appropriate analyzed-plan feature when it is available and safe for your environment if you need observed execution details rather than estimates alone. Run the statement with representative data and parameters; a plan generated for an empty development table is not a reliable guide to production behavior.
| Plan detail | What to inspect | Why it matters |
|---|---|---|
| Access type or method | Whether the engine performs a full scan, index lookup, range access, or another method | Shows whether the candidate path avoids scanning unnecessary rows |
| Chosen key | The index name selected by the optimizer | Confirms whether the intended index is even considered and chosen |
| Estimated rows | Rows expected to be examined at each step | Fewer examined rows generally means less I/O and filtering work, although estimates can be wrong |
| Remaining filtering | Conditions applied after the index lookup | Large post-index filtering can indicate that the index does not match enough of the predicate |
| Sort or temporary operations | Whether an avoidable temporary structure or filesort is used | A suitable key may help satisfy ordering or grouping without an extra sort |
| Join order and join keys | Which table is read first and which indexes are used for subsequent lookups | A well-indexed table can still be slow if the join starts with an unselective input |
MariaDB documentation explicitly recommends using EXPLAIN to determine whether indexes are being used effectively. Treat the plan as a hypothesis: after adding an index, compare the new plan and measure elapsed time, examined rows, sorting, and resource use on a representative workload.
Choose columns that match real predicates and joins
Selective equality and join columns
A single-column index is a good candidate when a predicate is both common and selective. For example:
CREATE INDEX idx_orders_customer_id
ON orders (customer_id);
EXPLAIN
SELECT order_id, total
FROM orders
WHERE customer_id = 42;The index is useful only if customer_id = 42 does not match most of the table, and the optimizer may still prefer a scan on a small table or for a low-selectivity value. Validate the choice with EXPLAIN rather than assuming that an index must be faster.
Join columns deserve the same treatment. If a query repeatedly joins orders.customer_id to customers.customer_id, ensure the lookup side has an appropriate key and inspect the join plan. A primary key or unique key may already supply the needed index; adding another identical key is redundant.
Range predicates
Conditions such as created_at >= ..., price BETWEEN ..., or a prefix range can use an ordered index, but the benefit depends on how much of the table falls inside the range. Very broad ranges can make a scan cheaper. In a composite index, a range condition can also limit how effectively later columns are used for searching, so test the complete query instead of relying on column-count rules.
Functions, expressions, and implicit conversions
Applying a function to an indexed column, converting its data type, or wrapping it in an expression can prevent a normal index lookup from matching the stored key order. For example, a predicate that transforms every timestamp before comparison may require row-by-row evaluation. Where appropriate, compare a column to a correctly typed boundary value, store a searchable derived value, or use a database-supported functional or generated-column strategy. Confirm the result in the plan; the exact options differ by server version and schema design.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsDesign composite indexes with the leftmost-prefix rule
A composite index stores columns in a defined order. A multi-column key can generally serve its leftmost prefix: an index on (tenant_id, status, created_at) reliably supports lookups beginning with tenant_id, and can usually use the first two columns when both are constrained. A query filtering only on status cannot normally use that key as effectively because it skips the leading column.
| Index | Query shape | Expected usefulness |
|---|---|---|
(tenant_id, status, created_at) |
WHERE tenant_id = ? AND status = ? |
Uses the leading equality columns |
(tenant_id, status, created_at) |
WHERE tenant_id = ? ORDER BY created_at |
Can use the prefix beginning with tenant_id; verify whether the skipped status affects ordering for the actual predicate |
(tenant_id, status, created_at) |
WHERE status = ? |
Usually not an efficient lookup because the leading tenant_id is absent |
(status, tenant_id, created_at) |
WHERE status = ? AND tenant_id = ? |
Can serve this shape, but may be a poor choice for queries that usually begin with tenant_id |
There is no universal “most selective first” rule that overrides the workload. A practical starting heuristic is to put equality and join columns first when that matches recurring statements, followed by range or ordering columns. Then verify the plan and measured behavior. The best order depends on which predicates appear together, their distributions, and whether the key must support sorting or grouping.
Filter-and-sort example
CREATE INDEX idx_orders_customer_status_date
ON orders (customer_id, status, created_at);
EXPLAIN
SELECT order_id, created_at
FROM orders
WHERE customer_id = 42
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;Here, the leading columns match equality predicates and the final column matches the requested ordering pattern. The optimizer still decides whether this path is cheaper than alternatives, and the direction and exact plan should be checked on the server version you run.
Do not create one giant key for every query
Adding columns to a composite index increases its width. Wider keys consume more storage, take longer to update, and may reduce the number of entries that fit in memory. Before extending an existing index, check whether its current leftmost prefixes already serve other statements and whether the additional column removes meaningful work.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Use covering indexes selectively
A covering index contains every column needed to filter, join, sort, and return a particular query. When the engine can answer the statement from the index alone, it may avoid fetching the base-table row for each match.
CREATE INDEX idx_orders_customer_status_date_total
ON orders (customer_id, status, created_at, total);For the filter-and-sort query above, an index containing order_id, created_at, and any other selected columns may cover the projection, depending on the storage engine’s handling of the table’s primary-key columns. Covering is a workload-specific optimization, not a reason to append every table column. Compare the reduction in row lookups with the added write and storage cost.
MariaDB guidance notes that prefix indexes cannot provide covering behavior. If a text column is indexed only by a prefix, the index does not contain the complete value needed to satisfy a projection that returns that column, so base-table access may still be required.
A repeatable index-tuning procedure
- Capture the slow statement. Record its SQL text, parameter values, frequency, result size, and the table sizes involved. Include the complete joins, ordering, grouping, and selected columns.
- Run a baseline plan. Use
EXPLAIN SELECT ...and record the access method, chosen key, estimated rows, join order, residual filtering, and sort or temporary operations. - Check existing keys first. An existing primary, unique, or composite index may already provide the needed leftmost prefix. Avoid creating a duplicate definition.
- Form one narrow candidate. Match the recurring query shape: equality and join columns first when appropriate, then range or ordering columns. Include projection columns only when a covering benefit is plausible.
- Create the index during a suitable change window. Account for build time, locking or online-DDL behavior supported by your server and storage engine, and the additional disk space required.
- Re-run the plan. Confirm whether the candidate key is chosen, whether estimated rows fall, and whether unnecessary filtering, sorting, or temporary work disappears. A newly created index that is never selected is not automatically a success or a failure; investigate why.
- Measure representative executions. Compare latency and resource use for common and worst-case parameter values, not just a single warm-cache run. Check concurrent write activity as well as read speed.
- Review write cost and overlap. If the index provides no durable benefit, remove it only after checking other queries that may rely on it. Consolidate overlapping keys cautiously.
- Refresh statistics when needed. Run
ANALYZE TABLEwhen data distribution changes substantially or plans become stale, following the operational practices for your MySQL or MariaDB version.
Why MySQL or MariaDB may ignore an index
The predicate is not selective
If a condition matches most rows, an index traversal followed by many row fetches can cost more than a sequential scan. This is normal optimizer behavior, not proof that the index is broken. Test a selective value and a common value separately; plans may legitimately differ.
The query skips the leading composite column
An index on (tenant_id, status, created_at) is not a general-purpose index on status. If the workload frequently searches by status alone, consider a separate key only after measuring that workload and accounting for its write cost.
Statistics are stale
The optimizer estimates row counts from statistics. After major data changes, those estimates can become inaccurate and lead to a poor plan. Refresh statistics with ANALYZE TABLE, then run EXPLAIN again. Statistics refresh does not guarantee a particular key choice; it gives the optimizer better information.
A function or type mismatch blocks the lookup
Expressions on the indexed column, implicit conversions, and comparisons between incompatible types can prevent efficient key use. Rewrite the predicate with matching data types where possible and inspect the plan to verify the access method.
Another index is cheaper
The optimizer may select a different key because it estimates fewer rows, supports the join order better, or avoids a sort. Compare the alternatives rather than forcing an index immediately. Hints can hide a statistics or data-distribution problem and may become harmful as the workload changes.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
The query needs nearly the whole table
Reporting statements, broad date ranges, and low-cardinality predicates may be faster with a scan. If the business requirement is to return most rows, changing the query’s result scope, pagination strategy, or reporting design may help more than adding another index.
Indexing joins, ordering, and grouping
Indexes can assist more than WHERE filtering. MySQL documentation identifies filtering, joins, ordering, grouping, and covering access as roles an index can play. For joins, index the columns used to find matching rows on the side repeatedly probed by the join plan. For ordering, align the key’s usable prefix with the query’s equality predicates and requested order. For grouping, test whether the key reduces or removes temporary aggregation work.
These benefits interact. A key that is excellent for a selective lookup may not provide the desired order after a range condition. A key that eliminates a sort may read too many rows. Use the complete plan and measured workload to decide which cost matters most.
MySQL and MariaDB: a practical comparison
| Decision axis | What to evaluate in either system |
|---|---|
| Predicate and join selectivity | How many rows each condition and join key is expected to match |
| Leftmost prefix | Whether the query supplies the leading columns of a composite key |
| ORDER BY and GROUP BY | Whether the key can reduce sorting or temporary work after predicates are applied |
| Covering potential | Whether all required filter and output columns fit in a reasonably sized key; remember that MariaDB prefix indexes do not provide covering behavior |
| EXPLAIN evidence | Chosen key, access type, estimated rows, residual filtering, and sort operations |
| Storage and write maintenance | Index width, number of indexes, build time, and impact on inserts, updates, and deletes |
Syntax and optimizer features vary by release, so use the documentation for the exact MySQL or MariaDB version deployed in your environment when choosing analyzed-plan options, online index creation settings, or advanced index types. The core method remains the same: match a real workload, inspect EXPLAIN, measure, and reassess as data changes.
Common indexing mistakes
- Indexing every column: creates write overhead and storage use without guaranteeing a useful access path.
- Ignoring column order: treats a composite key as if each column had an independent index.
- Measuring only one parameter: misses skewed data and cases where a scan is cheaper.
- Appending all selected columns: creates an oversized covering candidate when a small row-lookup reduction would have been enough.
- Leaving redundant keys indefinitely: duplicate or fully subsumed indexes continue to cost writes until removed.
- Skipping statistics maintenance: leaves the optimizer with stale row-distribution estimates.
- Forcing a key without evidence: can lock the query to a plan that becomes poor after growth or data changes.
Production checklist
- Is the statement a demonstrated workload bottleneck?
- Did you capture its filters, joins, ordering, grouping, projection, and parameter distribution?
- Did baseline
EXPLAINshow a scan, excessive rows, residual filtering, or avoidable sorting? - Does the proposed key follow the leftmost-prefix rule for the actual query shape?
- Are equality, join, range, and ordering columns ordered deliberately rather than by habit?
- Could a covering key help without becoming unacceptably wide?
- Did the post-change plan and representative measurements improve?
- Have you accounted for index build impact, storage, and write-heavy paths?
- Are statistics current, and have you checked other queries before removing an overlapping index?
Bottom line
Use indexes as targeted access paths, not as decorations on a schema. Start with a real slow query, inspect its baseline plan, design the smallest key that matches its recurring predicates and ordering, and validate the result with a second EXPLAIN and representative measurements. Composite-column order and selectivity determine whether the key is usable; covering designs can remove row lookups but increase maintenance cost; and a scan is sometimes the correct plan. Revisit statistics and redundant keys as the data and workload evolve.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
