Mnesia Indexing & Query Optimization

By default Mnesia can find a record fast only by its primary key. Searching by any other field forces a full table scan — slow on large tables. Indexes and queries fix this.

A secondary index lets Mnesia look up records by a non-key field in roughly constant time, at the cost of extra storage and slightly slower writes.

Add an index on a field position with mnesia:add_table_index/2.

Use index_read/3 to fetch all records whose indexed field matches a value.

match_object/1 finds records matching a pattern record where _ means any value.

QLC (Query List Comprehensions) gives a SQL-like syntax over Mnesia tables, with the compiler optimizing index use.

Write list-comprehension style queries against a table handle from mnesia:table/1.

Mnesia tables can be ram_copies (fast, volatile), disc_copies (RAM + disk), or disc_only_copies (disk only, low memory). Pick by speed vs durability needs.

The table type matters too: set (one record per key), ordered_set (sorted keys, enables range queries), and bag (multiple records per key).

Use mnesia:table_info(user, size) and timing around queries to confirm an index actually helps before adding it everywhere.

Every secondary index must be updated on each write, so adding many indexes slows inserts and updates. Index only the fields you actually query by, and drop unused indexes with del_table_index/2.

Test your Mnesia optimization knowledge.

You learned to optimize Mnesia reads:

• Non-key searches scan the whole table unless indexed
• Add indexes with add_table_index/2, query with index_read/3
• QLC gives SQL-like queries that exploit indexes
• Choose table copy type (ram/disc) and structure (set/ordered_set/bag) by need
• Always measure before optimizing