Indexes That Actually Work: A Field Guide to tabGL Entry and tabStock Ledger Entry
Karani Geoffrey9 min readMost advice about database indexes is written for a schema someone invented for a blog post. You do not run that schema. You run Frappe, where every table is called tabSomething, every primary key is a varchar(140) called name, and the framework has already created a pile of indexes for you, several of which are useless for the queries you actually run.
So let us do this with the real tables: tabGL Entry, tabStock Ledger Entry and tabSales Invoice. My thesis is simple and slightly heretical: ERPNext's shipped indexes are mostly single-column, and single-column indexes are the wrong shape for the reports that are actually killing your server. The fix is a small number of well-chosen composite indexes, and knowing which ones requires understanding exactly one rule.
The one rule: leftmost prefix
An InnoDB secondary index is a B-tree sorted by the concatenation of its columns, in order. An index on (company, account, posting_date) can efficiently answer:
WHERE company = ?WHERE company = ? AND account = ?WHERE company = ? AND account = ? AND posting_date BETWEEN ? AND ?
It can do nothing useful for WHERE account = ? alone, or WHERE posting_date > ? alone. The tree is sorted by company first, so without a company predicate the accounts are scattered across the whole index.
A second consequence: the moment you use a range (>, <, BETWEEN, LIKE 'x%') on a column, every column after it in the index can no longer be used for lookups. It can still be used to avoid a sort, which is why column order is a design decision, not an afterthought.
Rule of thumb for ERP reports: equality columns first, then the range column, then the sort column. Company and is_cancelled are equality. posting_date is the range.
What ERPNext actually ships on tabGL Entry
Look, do not guess:
SHOW INDEX FROM `tabGL Entry`;
You will see the primary key on name, a set of single-column indexes generated from the DocType's search_index flags (posting_date, account, party_type, party, cost_center, against_voucher, voucher_type, voucher_no, voucher_detail_no, company), plus the composites ERPNext creates in the GL Entry controller's on_doctype_update hook:
def on_doctype_update():
frappe.db.add_index("GL Entry", ["voucher_type", "voucher_no"])
frappe.db.add_index("GL Entry", ["posting_date", "company"])
frappe.db.add_index("GL Entry", ["party_type", "party"])
Notice what is missing. There is no index leading with company and continuing into account and posting_date. The composite that exists is (posting_date, company) - date first. For a General Ledger report scoped to one company and one account over a fiscal year, that index is close to worthless, because a one-year range on posting_date may already be 80 percent of the table.
The EXPLAIN that proves it
EXPLAIN SELECT `account`, SUM(`debit`) AS dr, SUM(`credit`) AS cr
FROM `tabGL Entry`
WHERE `company` = 'Kilimani Traders Ltd'
AND `is_cancelled` = 0
AND `posting_date` BETWEEN '2025-07-01' AND '2026-06-30'
GROUP BY `account`;
+----+-------------+-------+------+-----------------------------------+------+---------+------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------------------------+------+---------+------+---------+---------------------------------+
| 1 | SIMPLE | tabGL | ALL | posting_date,company,posting_date_| NULL | NULL | NULL | 4128744 | Using where; Using temporary; |
| | | Entry | | company | | | | | Using filesort |
+----+-------------+-------+------+-----------------------------------+------+---------+------+---------+---------------------------------+
Three red flags in one row. type: ALL means full table scan. key: NULL means the optimizer looked at the available indexes and decided a scan was cheaper - it was right, which is the depressing part. Using temporary; Using filesort means the GROUP BY built a temp table and sorted it.
The type column is your quality scale, best to worst: system, const, eq_ref, ref, ref_or_null, range, index, ALL. On an ERP report over a big table, you are aiming for range or ref. index is not a win: it means a full scan of the index rather than the table, which is smaller but still linear.
Use ANALYZE, not just EXPLAIN
MariaDB's ANALYZE runs the statement and shows real numbers next to the estimates:
ANALYZE SELECT `account`, SUM(`debit`) AS dr
FROM `tabGL Entry`
WHERE `company` = 'Kilimani Traders Ltd' AND `is_cancelled` = 0
AND `posting_date` BETWEEN '2025-07-01' AND '2026-06-30'
GROUP BY `account`\G
type: ALL
rows: 4128744
r_rows: 4128744.00
filtered: 11.11
r_filtered: 47.30
Extra: Using where; Using temporary; Using filesort
The gap between filtered (11.11, the optimizer's guess) and r_filtered (47.30, reality) tells you the optimizer's statistics are stale. That alone can cause a bad plan. Fix it with ANALYZE TABLE `tabGL Entry`; before you conclude you need a new index.
The index that fixes it
ALTER TABLE `tabGL Entry`
ADD INDEX `gle_company_account_date` (`company`, `account`, `posting_date`),
ALGORITHM=INPLACE, LOCK=NONE;
ALGORITHM=INPLACE, LOCK=NONE is the part people skip. Without it MariaDB may choose a copying algorithm that locks the table for the duration, and on a 4 million row tabGL Entry that is a several-minute outage in the middle of the working day. Adding a plain secondary index to InnoDB supports LOCK=NONE, so state it explicitly and let the server error out rather than silently locking.
Now the plan:
+----+-------------+-------------+-------+--------------------------+--------------------------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+--------------------------+--------------------------+---------+------+-------+-------------+
| 1 | SIMPLE | tabGL Entry | range | gle_company_account_date | gle_company_account_date | 1130 | NULL | 18422 | Using where |
+----+-------------+-------------+-------+--------------------------+--------------------------+---------+------+-------+-------------+
4.1 million rows down to 18,422, and the temp table and filesort are gone because the index already delivers rows grouped by account. That query went from 18 seconds to about 90 milliseconds on the same hardware.
About key_len, and a limit that will bite you
key_len = 1130 decomposes as: company varchar(140) in utf8mb4 is 140 x 4 + 2 length bytes + 1 null byte = 563, account the same 563, posting_date is a DATE at 3 bytes + 1 null byte = 4. Total 1130. This is how you verify the optimizer is really using all three columns and not just the first one.
It also explains a hard limit. InnoDB with DYNAMIC row format caps an index key at 3072 bytes. Five varchar(140) columns is 2815 bytes and squeaks through; six is 3378 and fails with "Specified key was too long". On Frappe schemas, four Link fields is a practical ceiling for a composite index. If you need more selectivity, use a prefix such as account(40).
tabStock Ledger Entry: the same lesson, worse consequences
ERPNext creates these in the Stock Ledger Entry controller:
def on_doctype_update():
frappe.db.add_index("Stock Ledger Entry", ["voucher_no", "voucher_type"])
frappe.db.add_index("Stock Ledger Entry", ["item_code", "warehouse", "posting_datetime", "creation"])
That second one is genuinely good, and it is the index that makes valuation work: for a given item in a given warehouse, walk entries in posting order. Note the trailing creation, which breaks ties when two entries share a posting_datetime. This is why stock reconciliation is fast when you filter by item.
But look at what it cannot do. A Stock Balance report across all items for a date range has no item_code predicate, so the leftmost prefix rule disqualifies the index entirely and you get a full scan of a table that on a distribution business is often larger than tabGL Entry. ERPNext solves this on PostgreSQL with a partial index on (company, posting_datetime, creation) WHERE is_cancelled = 0, and explicitly skips it on MariaDB because MariaDB has no partial indexes.
The MariaDB workaround is a plain composite:
ALTER TABLE `tabStock Ledger Entry`
ADD INDEX `sle_company_posting` (`company`, `is_cancelled`, `posting_datetime`, `creation`),
ALGORITHM=INPLACE, LOCK=NONE;
Putting the low-cardinality is_cancelled second looks wrong by textbook selectivity rules, but it is right here: it is an equality predicate in every ERPNext stock query, and placing it before the range column keeps posting_datetime usable as a range and as the sort order.
Covering indexes: the free win
If every column a query touches lives in the index, InnoDB never visits the clustered index at all. EXPLAIN shows Using index. For an aging report that only needs sums:
ALTER TABLE `tabGL Entry`
ADD INDEX `gle_party_cover` (`party_type`, `party`, `posting_date`, `debit`, `credit`),
ALGORITHM=INPLACE, LOCK=NONE;
MariaDB has no INCLUDE clause, so the payload columns become real key columns. That costs disk and it costs write throughput, so only do this for a report that is genuinely hot and genuinely narrow.
Making the index survive bench migrate and a restore
A raw ALTER TABLE is not dropped by bench migrate, so it persists on that server. But it does not exist on a fresh restore, a staging clone, or a second site. Make it reproducible with a tiny custom app and the after_migrate hook:
# myapp/hooks.py
after_migrate = ["myapp.setup.indexes.ensure_indexes"]
# myapp/setup/indexes.py
import frappe
def ensure_indexes():
frappe.db.add_index("GL Entry", ["company", "account", "posting_date"],
index_name="gle_company_account_date")
frappe.db.add_index("Stock Ledger Entry",
["company", "is_cancelled", "posting_datetime", "creation"],
index_name="sle_company_posting")
frappe.db.add_index is idempotent, so running it on every migrate is safe.
When indexes are the problem
Every index is a write tax. A Sales Invoice submit writes to tabGL Entry, tabStock Ledger Entry, tabPayment Ledger Entry and their children, and each row must be inserted into every index on those tables. Sites that have ticked "Search Index" on twenty fields in Customize Form have made submission slower for no measurable read benefit.
Find the dead weight. MariaDB's performance schema tracks index usage:
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND index_name != 'PRIMARY'
AND count_star = 0
AND object_schema NOT IN ('mysql','performance_schema','sys','information_schema')
ORDER BY object_schema, object_name;
Counters reset on restart, so let the server run at least a full business cycle including month-end before you trust a zero.
And find redundant ones. If you have (company) and also (company, account, posting_date), the single-column index is dead weight, because the composite serves every query the short one could:
SELECT table_name, index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) AS cols
FROM information_schema.STATISTICS
WHERE table_schema = DATABASE()
AND table_name IN ('tabGL Entry','tabStock Ledger Entry')
GROUP BY table_name, index_name;
On more than one site I have seen index_length exceed data_length on tabGL Entry. That is not a healthy ERP, that is a table paying rent on indexes nobody queries.
The short version
- Read the DocType controller's
on_doctype_updateto know what ERPNext gave you. - Order composite columns as: equality, equality, range, sort.
- Always
ANALYZEbefore and after. Ifr_rowsdid not drop by an order of magnitude, the index did not help. - Always
ALGORITHM=INPLACE, LOCK=NONEon production. - Pin the index in an
after_migratehook so it survives the next restore. - Every quarter, delete the indexes with
count_star = 0.