Design Effective MariaDB Indexes Without Over-Indexing

Reliable MariaDB index design starts with query families, not individual columns. An index on every field used in a WHERE clause can leave the optimizer combining weak keys, scanning large ranges, sorting anyway, and making every write maintain a forest of overlapping B-trees. The right composite index can replace several narrow indexes, but an oversized covering index can consume more buffer pool and redo than the saved table lookups justify.

Indexes are persistent workload decisions. They affect SELECT plans, INSERT and UPDATE latency, lock behavior, storage, backups, replication, crash recovery, optimizer statistics, and schema-change windows. They also encode ordering: (customer_id, status, created_at) and (created_at, customer_id, status) are not interchangeable even when the same columns appear.

This guide develops indexes from real SQL, explains InnoDB primary and secondary structures, chooses composite column order for equality, range, sorting, grouping, and joins, evaluates covering and prefix keys, identifies redundancy, uses MariaDB 10.6+ ignored indexes safely, measures read and write effects, and rolls changes out with evidence. It aims for a small, intentional index set that supports the important workload rather than the maximum possible number of access paths.

Understand what an InnoDB index stores

InnoDB organizes the table around its clustered primary key. Leaf records of the primary index contain the row. A secondary index is a separate B-tree whose leaf entries contain the secondary key plus the row's primary-key value, which InnoDB uses to locate the full clustered row.

This has important consequences:

• A wide primary key makes every secondary index wider.
• A random primary key can increase page splits and reduce locality.
• Secondary lookup may require an additional clustered-index lookup.
• Primary-key columns can make a secondary index covering even when not declared explicitly.
• Each secondary index must be maintained by relevant writes.

If a table has no primary key and no suitable unique non-null key, InnoDB creates an internal six-byte clustered identifier. It is invisible to application SQL and makes replication, maintenance, and row identification less explicit. Production tables should normally have a deliberate stable primary key.

Inspect the real table:

SHOW CREATE TABLE sales.ordersG
SHOW INDEX FROM sales.orders;

Do not infer the clustered key from an ORM model. Migrations, implicit indexes, foreign keys, and prior hotfixes may have changed the schema.

Inventory the workload before designing keys

Collect normalized query families from:

• Slow query log and mariadb-dumpslow.
• Performance Schema statement digests.
• Application traces and endpoint metrics.
• Scheduled reports, exports, and maintenance jobs.
• Replica-only reads and failover workloads.
• Foreign-key checks and uniqueness requirements.

For every family, document:

Rank by total service cost, not the most dramatic single duration. A 25 ms query executed 100,000 times can deserve priority over a 20-second monthly report.

Capture the baseline plan and runtime evidence:

EXPLAIN FORMAT=JSON
SELECT id, created_at, total_amount
FROM sales.orders
WHERE customer_id = 481
  AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 100;

Use ANALYZE FORMAT=JSON only when executing the exact SELECT is safe. MariaDB's ANALYZE runs the target; with DML it actually changes rows.

Learn the leftmost-prefix rule

For a B-tree index:

INDEX ix_orders_customer_status_created
  (customer_id, status, created_at)

MariaDB can efficiently navigate leading prefixes such as:

(customer_id)
(customer_id, status)
(customer_id, status, created_at)

It generally cannot use this key as a direct selective lookup by status alone or created_at alone because the leading customer_id is unknown. It may still scan the whole index for covering or ordering reasons, but that is different from a narrow range lookup.

The order of predicates in SQL does not determine index order:

WHERE status = 'paid'
  AND customer_id = 481

is logically equivalent to reversing those AND terms. The optimizer determines access. The order of columns inside the index remains structural and important.

An index on (a,b) commonly makes a separate index on (a) redundant for lookup, but not universally. The narrower key can be smaller and cheaper for a scan, may have different uniqueness or prefix semantics, and workload evidence can justify both. Test rather than dropping by prefix comparison alone.

Put equality columns before a range carefully

A useful starting heuristic is:

1. Columns tested by equality.
2. A selective range or the columns needed for ordering.
3. Additional projection columns only if covering is worth the cost.

For:

SELECT id, created_at, total_amount
FROM sales.orders
WHERE customer_id = 481
  AND status = 'paid'
  AND created_at >= '2026-08-01 00:00:00'
  AND created_at <  '2026-09-01 00:00:00'
ORDER BY created_at DESC, id DESC
LIMIT 100;

a candidate is:

CREATE INDEX ix_orders_customer_status_created_id
ON sales.orders (customer_id, status, created_at, id);

The two equality values locate a prefix, and the date becomes a range within it. The key may also support ordered retrieval by created_at, id, subject to version, direction, and plan details.

This is not a universal formula. If status has only a few values but the query also runs without customer_id, another query family may need a different leading key. If the date range is extremely selective across all customers, (created_at, customer_id, status) may help that family but cannot usually preserve the requested per-customer order in the same way.

Once a range key part is used for navigation, later parts commonly cannot further narrow the contiguous range. They may still participate in index condition pushdown, filtering, ordering, or covering. Confirm used_key_parts, key_length, rows, and runtime pages rather than assuming every declared column reduces the search.

Order equality columns by workload, not folklore

“Put the most selective column first” is incomplete for equality predicates. When all leading columns are constrained by equality in the same query, either order may reach the same full composite key. The better order also depends on:

• Which leading prefixes support other important queries.
• Column width and cardinality.
• Data skew and correlation.
• Join order and parameter availability.
• Grouping or ordering requirements.
• Compression, locality, and write behavior.

Suppose the workload contains both:

WHERE customer_id = ? AND status = ?

and:

WHERE customer_id = ?

Then (customer_id, status) supports both leading-prefix shapes. (status, customer_id) supports the combined lookup but not a narrow customer-only lookup.

Do not calculate selectivity from total distinct counts alone. status='pending' may match 1% while status='completed' matches 90%. Tenant distributions can be even more skewed. Test real parameter classes.

Design indexes for joins

For a conventional parent-child join:

SELECT o.id, c.name
FROM sales.orders AS o
JOIN crm.customers AS c ON c.id = o.customer_id
WHERE o.status = 'pending';

the parent primary key customers(id) supports lookup from child to parent. On the child side, orders(customer_id) supports finding all orders for a customer and helps foreign-key checks.

Foreign-key columns must be indexed appropriately; MariaDB/InnoDB may create a supporting index automatically when one is absent. Automatic naming and shape may not match broader query needs. A later composite index beginning with the foreign-key columns can make the narrow generated index redundant, but dropping any key requires checking the constraint definition and engine requirements.

Inspect relationships and indexes:

SELECT CONSTRAINT_NAME,
       TABLE_SCHEMA,
       TABLE_NAME,
       COLUMN_NAME,
       REFERENCED_TABLE_SCHEMA,
       REFERENCED_TABLE_NAME,
       REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'sales'
  AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME, CONSTRAINT_NAME, ORDINAL_POSITION;

Join columns should use compatible data types, signedness, lengths, and collations. An implicit conversion can prevent an efficient ref lookup or change comparison semantics.

Indexing the foreign key does not guarantee a cheap join. If each parent has hundreds of thousands of child rows, r_rows per loop remains large. Filter earlier, add a composite child key, pre-aggregate, or redesign the query based on actual needs.

Support ORDER BY without sacrificing filtering

An index can produce rows in key order when earlier key parts are fixed appropriately and the requested ordering matches the remaining sequence.

SELECT id, created_at, total_amount
FROM sales.orders
WHERE customer_id = 481
ORDER BY created_at DESC, id DESC
LIMIT 100;

Candidate:

CREATE INDEX ix_orders_customer_created_id
ON sales.orders (customer_id, created_at, id);

MariaDB can seek to one customer and traverse the date/id sequence. This is particularly effective for keyset pagination:

SELECT id, created_at, total_amount
FROM sales.orders
WHERE customer_id = 481
  AND (created_at, id) < ('2026-08-25 12:00:00', 9000000)
ORDER BY created_at DESC, id DESC
LIMIT 100;

Large OFFSET pagination may still walk and discard many index entries. An index does not make offset proportional to page size.

Mixed ascending and descending directions, NULL ordering, collations, and descending-index behavior vary by MariaDB version. Use EXPLAIN on the exact release. Do not add DESC syntax based on another database's implementation and assume storage or optimizer behavior matches.

Sometimes filtering through a highly selective index and sorting 30 rows is better than scanning 100,000 rows in order through a less selective key. Using filesort is not itself a failure.

Support GROUP BY and DISTINCT selectively

An ordered index can sometimes stream grouping or avoid a broad sort:

SELECT customer_id, COUNT(*)
FROM sales.orders
WHERE created_at >= '2026-08-01 00:00:00'
  AND created_at <  '2026-09-01 00:00:00'
GROUP BY customer_id;

An index beginning with created_at narrows the month but does not naturally group rows by customer. An index beginning with customer_id groups but may scan all history. There may be no one index that optimizes both dimensions.

Options include:

• Accept a bounded temporary table and sort.
• Partition or summarize by time through an intentional architecture.
• Maintain a rollup table for frequent reporting.
• Run analytics on a resource-isolated replica or analytical system.
• Restrict report date ranges.

Do not build an enormous index for one rare aggregation if it increases every transactional write. Index design is allowed to conclude that a filesort is the best system-wide choice.

Understand covering indexes and their cost

A covering index contains all columns needed by the query, allowing MariaDB to return data from the secondary B-tree without fetching the clustered row. Extra: Using index often signals this.

For:

SELECT id, created_at, total_amount
FROM sales.orders
WHERE customer_id = 481
ORDER BY created_at DESC, id DESC
LIMIT 100;

a covering candidate could be:

CREATE INDEX ix_orders_customer_created_id_amount
ON sales.orders (customer_id, created_at, id, total_amount);

But id may already be present in secondary leaves because it is the primary key. Confirm actual key behavior and EXPLAIN; declaring it may be unnecessary depending on position/order needs.

Adding payload columns makes each entry wider. That means fewer entries per page, more B-tree pages, more buffer-pool churn, more redo, longer DDL, and more write amplification. Cover only a high-value, high-frequency query when measured clustered lookups are material.

Avoid placing large text, JSON, or rarely needed attributes into a covering key. Even when prefix syntax permits an index, it may not cover the full returned value.

Use prefix indexes with evidence

For long strings, MariaDB can index a leading prefix:

CREATE INDEX ix_customers_email_prefix
ON crm.customers (email(32));

This reduces index width but introduces collisions among values sharing the first 32 characters. MariaDB must examine candidates and compare the full value. A prefix index generally cannot cover a query requiring the full column.

Measure prefix selectivity before choosing length:

SELECT COUNT(*) AS rows_total,
       COUNT(DISTINCT email) AS distinct_full,
       COUNT(DISTINCT LEFT(email, 8)) AS distinct_8,
       COUNT(DISTINCT LEFT(email, 16)) AS distinct_16,
       COUNT(DISTINCT LEFT(email, 32)) AS distinct_32
FROM crm.customers;

This scan can be expensive and expose personal data through access or logs. Run it on an authorized replica or controlled window and interpret skew, not only totals.

Prefix indexes are unsuitable for arbitrary substring search such as LIKE '%example%'. Consider FULLTEXT, a search system, normalized derived data, or a different product feature according to language and matching requirements.

Handle expressions with generated columns

A normal index on created_at commonly cannot provide range access for:

WHERE DATE(created_at) = '2026-08-25'

The simplest fix is a sargable half-open range:

WHERE created_at >= '2026-08-25 00:00:00'
  AND created_at <  '2026-08-26 00:00:00'

When the application genuinely searches a deterministic expression, MariaDB can index an appropriate generated column on supported versions:

ALTER TABLE sales.orders
  ADD COLUMN created_date DATE
    AS (DATE(created_at)) PERSISTENT,
  ADD INDEX ix_orders_created_date (created_date);

Syntax, optimizer matching, virtual versus persistent support, replication, and online DDL behavior vary by release. Adding a persistent column rewrites or stores data and increases writes. Test the exact DDL.

Time-zone semantics matter: deriving a date from a UTC timestamp may not match a customer's local business date. Schema correctness comes before index use.

Choose UNIQUE indexes for integrity, not speed alone

A unique index declares a data invariant:

ALTER TABLE iam.users
  ADD CONSTRAINT uq_users_tenant_email
  UNIQUE (tenant_id, normalized_email);

It can also give the optimizer useful cardinality knowledge, but its primary purpose is correctness under concurrency. Application-side “check then insert” cannot prevent races without a database constraint.

Before adding it, identify duplicates safely:

SELECT tenant_id, normalized_email, COUNT(*) AS duplicate_count
FROM iam.users
GROUP BY tenant_id, normalized_email
HAVING COUNT(*) > 1
LIMIT 100;

Do not use ALTER ... IGNORE to discard duplicates automatically. Deduplication requires an explicit business rule, audit trail, foreign-key handling, and rollback.

MariaDB uniqueness with NULL follows SQL semantics where multiple NULL combinations may be allowed. If the business requires “only one missing value,” model that deliberately and test the exact release behavior.

Match index type to the operation

Most InnoDB workload indexes are BTREE. Other index families serve different semantics:

• FULLTEXT for supported natural-language or boolean text search.
• SPATIAL for supported geometry operations.
• HASH in engines and contexts with different capabilities, such as MEMORY exact lookup.
• Engine-specific structures in Aria, MyRocks, ColumnStore, or other products.

Do not expect a BTREE to optimize a leading-wildcard substring, and do not use FULLTEXT as an exact unique constraint. Storage-engine support, transaction behavior, syntax, limitations, and ranking rules must be checked on the installed version.

Estimate the cost of every additional index

Each secondary index changes:

• INSERT work and page allocation.
• UPDATE cost when any indexed column changes.
• DELETE work and purge behavior.
• Redo and potentially binary-log volume.
• Buffer-pool footprint.
• Disk capacity and snapshot size.
• Backup, restore, validation, and upgrade time.
• Replica apply and Galera certification workload.
• DDL duration and operational risk.

Measure before and after with a production-like concurrency test. Useful MariaDB counters include redo bytes, buffer pages, rows inserted/updated/deleted, and I/O. Application commit percentiles matter more than one microbenchmark.

Check table and index size through information_schema.TABLES as a rough view:

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       TABLE_ROWS,
       DATA_LENGTH,
       INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'sales'
  AND TABLE_NAME = 'orders';

TABLE_ROWS is estimated for InnoDB, and INDEX_LENGTH is aggregate. Use it for trend and capacity planning, not an exact per-index bill.

Find duplicate and overlapping indexes

Inventory ordered key parts from information_schema.STATISTICS:

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       INDEX_NAME,
       NON_UNIQUE,
       SEQ_IN_INDEX,
       COLUMN_NAME,
       SUB_PART,
       COLLATION,
       INDEX_TYPE,
       IGNORED
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA NOT IN
  ('mysql','information_schema','performance_schema','sys')
ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

The IGNORED column is available where ignored indexes are supported. Feature-detect it for older releases.

Exact duplicates share ordered columns, prefixes, uniqueness, type, and relevant attributes. Overlap needs judgment:

INDEX(a)
INDEX(a,b)
INDEX(a,b,c)

The longer indexes can support leading prefixes, but narrower ones may be cheaper for some scans. Unique and foreign-key roles can differ. Prefix length, collation, order direction, ignored state, and engine behavior matter.

Do not delete an index just because its name resembles another or because a tool labels it redundant. Confirm all application queries, constraints, statistics usage, maintenance jobs, and failover roles.

Measure index usage with strong caveats

Performance Schema can summarize table I/O by index where instrumentation is enabled:

SELECT OBJECT_SCHEMA,
       OBJECT_NAME,
       INDEX_NAME,
       COUNT_STAR,
       COUNT_READ,
       COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'sales'
  AND OBJECT_NAME = 'orders'
ORDER BY COUNT_STAR DESC;

Inspect columns first because schemas vary:

DESCRIBE performance_schema.table_io_waits_summary_by_index_usage;

Zero observed reads does not prove an index is unused. The measurement window may omit monthly reports, failover traffic, incident queries, or a rare critical lookup. Counters can reset on restart or truncate. An index can enforce uniqueness or support a foreign key even when select usage is absent. The optimizer may also use its statistics without choosing it for row access.

Record server uptime, instrumentation state, workload calendar, and all roles. Observe at least one complete business cycle plus rare operational workflows before removal.

Test removal with ignored indexes on MariaDB 10.6+

MariaDB 10.6 introduced ignored indexes, comparable to MySQL invisible indexes. An ignored index remains present and maintained but is excluded from optimizer plans and statistics consideration.

Inspect state:

SHOW INDEX FROM sales.orders;

Ignore a candidate:

ALTER TABLE sales.orders
  ALTER INDEX ix_orders_legacy_status IGNORED;

Restore it quickly:

ALTER TABLE sales.orders
  ALTER INDEX ix_orders_legacy_status NOT IGNORED;

Important constraints:

• A primary key cannot be ignored.
• The index is still maintained, so write-cost savings are not tested yet.
• The optimizer treats it as absent.
• USE INDEX, FORCE INDEX, or IGNORE INDEX naming an ignored key raises an error as if the key did not exist.
• Plan and statistics changes can affect queries that never selected the key directly.

Search application code, stored routines, reports, and jobs for explicit index hints before ignoring it. Roll out to one replica or canary where architecture permits, observe a complete workload cycle, and keep a one-command NOT IGNORED rollback.

Only after the ignored period proves safe should you plan the physical drop:

ALTER TABLE sales.orders
  DROP INDEX ix_orders_legacy_status;

Dropping is not as reversible as unignoring. Rebuilding a large index can take time, I/O, disk, redo, and replication capacity.

Use index hints as a diagnostic, not a permanent reflex

MariaDB supports old-style hints:

SELECT ...
FROM sales.orders FORCE INDEX (ix_orders_customer_created_id)
WHERE ...;

• USE INDEX limits considered keys.
• FORCE INDEX also makes a table scan look expensive, though MariaDB can still scan if no forced key is usable.
• IGNORE INDEX removes named keys from consideration.
• FOR JOIN, FOR ORDER BY, and FOR GROUP BY target scopes.

Hints are useful for comparing candidate plans:

EXPLAIN FORMAT=JSON
SELECT ... FROM sales.orders
USE INDEX (ix_orders_customer_created_id)
WHERE ...;

They can age badly as data, statistics, MariaDB versions, and indexes change. A hard hint can turn an upgrade improvement into a regression or cause errors after schema changes. Fix statistics, query shape, and index design first.

MariaDB 12.1+ introduces new comment-style index-level hints, with additional capabilities in 12.2. Do not deploy those syntaxes to older LTS servers. If old and new hint styles are combined, precedence behavior also matters; check release documentation.

Refresh statistics without treating it as magic

Index cardinality shown by SHOW INDEX is an estimate. Stale or sampled statistics can lead to wrong selectivity and join-order choices.

ANALYZE TABLE sales.orders;

This can update engine statistics and may change many plans. Schedule it, monitor resources, and preserve before/after digest performance.

MariaDB supports engine-independent statistics in mysql.table_stats, mysql.column_stats, and mysql.index_stats when collected through appropriate version-specific ANALYZE TABLE options. mysql.index_stats stores average frequency for index prefixes and supports InnoDB extended keys.

Do not manually edit statistics tables during ordinary tuning. Manual statistics are an advanced intervention requiring version-specific expertise, audit, testing, and rollback.

Statistics cannot fully model every correlated predicate or extreme tenant skew. A query path that must work across distributions needs representative testing, not faith in one cardinality number.

Plan index DDL for production

Creating an index on a large active table can consume CPU, read/write bandwidth, temporary space, redo, and replica capacity. Metadata-lock waits can delay start or finalization and block later statements behind the waiting DDL.

Before change:

SELECT VERSION();
SHOW CREATE TABLE sales.ordersG
SHOW INDEX FROM sales.orders;
SELECT @@GLOBAL.tmpdir;

Check disk and mount capacity:

df -hT /var/lib/mysql
df -i /var/lib/mysql
findmnt -T /var/lib/mysql

Test production-sized staging DDL. MariaDB supports ALGORITHM and LOCK clauses, but availability depends on operation, engine, and version. Requesting an unsupported algorithm can fail rather than silently delivering the desired online behavior, which is preferable to assuming it is nonblocking.

Example to validate on the exact release:

ALTER TABLE sales.orders
  ADD INDEX ix_orders_customer_status_created
    (customer_id, status, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

Do not label LOCK=NONE as “zero impact.” Online DDL still uses resources and metadata locks, and some phases or failure paths can affect traffic.

Set an operational window, metadata-lock timeout strategy, replication/cluster guardrails, disk abort threshold, and owner. Avoid starting DDL behind a long transaction where it waits while causing a queue of subsequent table users.

Verify a new index across the system

After creation:

SHOW INDEX FROM sales.orders;

EXPLAIN FORMAT=JSON
SELECT id, created_at, total_amount
FROM sales.orders
WHERE customer_id = 481
  AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 100;

For a safe SELECT in controlled conditions:

ANALYZE FORMAT=JSON
SELECT id, created_at, total_amount
FROM sales.orders
WHERE customer_id = 481
  AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 100;

Compare:

• P50/P95/P99 and total digest time.
• Rows and pages read.
• Loops and filtering.
• Temporary tables and sort work.
• CPU and storage latency.
• INSERT/UPDATE/DELETE and commit latency.
• Redo bytes and buffer-pool use.
• Replica lag or cluster health.
• Backup duration and size trend.

Test typical, missing, small, and very large tenant values. Confirm the optimizer chooses the key without a hint. If benefit appears only under a forced plan, investigate estimates before hard-coding the hint.

Troubleshoot common index mistakes

The index exists but MariaDB does not use it

Check leading key parts, predicate sargability, types, collations, selectivity, statistics, projection, order, and table size. The scan may genuinely be cheaper.

A composite index uses only the first column

A range on the first part, missing middle equality, expression, incompatible comparison, or ordering requirement can prevent deeper navigation. Inspect JSON used_key_parts, key_length, and runtime filtering.

Adding an index made writes slower

Measure page splits, redo, storage latency, buffer footprint, and updated indexed columns. Remove only after a safe ignored/canary test or rollback plan; query benefit may still outweigh write cost.

Cardinality is low or obviously stale

Confirm data distribution and update time. Run a controlled ANALYZE TABLE, then compare plans across workload families. Low cardinality can also be accurate for a status flag.

Index creation waits indefinitely

Inspect metadata locks and long transactions. Do not repeatedly submit more ALTER statements. Cancel safely if abort criteria are met, clear the blocker through incident procedure, and reschedule.

An ignored index caused application errors

Search for explicit hints referencing it. Immediately restore NOT IGNORED, verify plans and errors, then remove or update hints before another experiment.

Dropping an apparently unused index caused a regression

Recreate it only through a controlled DDL plan; this is why ignored-index observation is valuable. Determine which rare workload, constraint, or statistics effect was missed and expand the inventory window.

An index fixes SELECT but not ORDER BY

Earlier key parts may not be constant, directions may not match, a range may interrupt order use, or the optimizer may prefer filtering and sorting. Compare rows that would be scanned in index order versus sorted after filtering.

Production index-review checklist

• Inventory query digests across a full business cycle.
• Rank query families by total service cost.
• Record equality, range, join, order, group, projection, and limit.
• Test parameter skew and missing values.
• Inspect actual table DDL and ordered index parts.
• Use leftmost prefixes to consolidate deliberately.
• Stop expecting key parts after a range to behave like equalities.
• Treat covering columns as a measured space/write trade-off.
• Preserve uniqueness and foreign-key requirements.
• Detect exact duplicates and evaluate overlaps manually.
• Observe usage across every server role and seasonal job.
• Use MariaDB 10.6+ ignored indexes before a risky drop.
• Search code for hard index hints.
• Test DDL size, time, metadata locks, and disk headroom.
• Measure read benefit and write amplification together.
• Keep a reversible rollout and explicit abort thresholds.

FAQ

What is the best column order for a MariaDB composite index?

There is no universal order. Start from equality predicates, then range or ordering needs, while considering which leading prefixes support other important query families. Verify with runtime plans.

Is INDEX(a) redundant when INDEX(a,b) exists?

Often, but not always. The longer key supports the a prefix, while the narrower key may be smaller or have different uniqueness, prefix, or constraint behavior. Measure before removal.

Should low-cardinality columns be indexed?

Not alone by default. A status column can be useful inside a composite key when combined with tenant, date, or ordering columns, especially if one value is rare. Test actual skew.

What is a covering index?

It contains everything a query needs, so InnoDB can avoid fetching the clustered row. It can reduce reads but makes the index larger and writes more expensive.

What is an ignored index in MariaDB?

From MariaDB 10.6, an ignored index remains stored and maintained but the optimizer treats it as absent. It supports reversible testing before a physical drop.

Can I ignore a primary key?

No. MariaDB does not allow the explicit or implicit primary key to be ignored.

Why is an index hint risky?

It constrains the optimizer using today's schema, data, and cost model. After growth or upgrade, the forced plan can become worse or fail when an index changes.

Does LOCK=NONE make index creation impact-free?

No. It requests online concurrency where supported, but DDL still consumes resources and requires metadata-lock phases. Test the exact engine, version, and table size.

Conclusion

Strong MariaDB index design turns a measured workload into a small set of intentional B-trees. Choose column order from equality, range, joins, ordering, and reusable leading prefixes. Compare estimated and runtime rows across skewed values, and accept bounded sorting or scanning when another index would cost the system more than it saves.

Every index is also a write and operations decision. Measure storage, redo, DML latency, backup, replication, and DDL risk alongside SELECT improvement. Before removing a key, observe a complete workload and use MariaDB 10.6+ ignored indexes for a reversible optimizer test. The goal is not to make every query say Using index; it is to meet service objectives with the least complex, least costly index set that preserves data integrity.

Suggested Internal Links

• Analyze MariaDB Queries with EXPLAIN and ANALYZE Safely
• Use the MariaDB Slow Query Log for Better Diagnosis
• Tune MariaDB Temporary Tables and Sort Operations Safely
• Find and Fix MariaDB Lock Waits and Deadlocks
• Size the MariaDB InnoDB Buffer Pool for Production
• Optimize MariaDB Redo Logs and Transaction Durability

Suggested External Sources

• MariaDB compound and composite indexes
• MariaDB ignored indexes
• MariaDB index hints
• MariaDB indexes guide
• MariaDB index statistics table
• MariaDB ALTER TABLE documentation