Technical article
Choose database indexes from real Laravel queries
Evaluate a composite index against filters, ordering and execution plans. Check write costs and result correctness as well.
- SQL
- MySQL
- Laravel
In this article
Choose an index for a specific filter, ordering and join pattern. Indexing every column can increase write and storage cost without helping the important query. The execution plan and representative data determine the trade-off.
Example: an organisation lists its open cases ordered by creation date. Investigate an organisation/status/date composite index, but test column order against the actual database and status distribution. This example makes no measured speed claim.
- Record database version, row count, distribution, query and parameters.
- Compare plans and repeated timings under the same cold or warm cache conditions.
- Measure inserts, updates and index size; check usage and rollback before removing an index.
Start with one concrete list query
Consider an order list that shows open orders for one account within a date range, ordered by timestamp and ID. This is an illustrative domain model, not an existing table or client case from Codegenie. First record the actual SQL, parameter values, database version, existing indexes and value distribution. Orders per account and the proportion of open orders affect how much work an access path requires. A conveniently small development dataset cannot establish whether that choice will remain useful for a large account.
use App\Models\Order;
// Example only: the Order model and these columns belong to your application.
// Resolve accountId through your application's authorisation rules.
// Resolve from/to in the same time zone as the stored created_at values.
$orders = Order::query()
->select(['id', 'account_id', 'status', 'created_at'])
->where('account_id', $accountId)
->where('status', 'open')
->where('created_at', '>=', $from)
->where('created_at', '<', $to)
->orderByDesc('created_at')
->orderByDesc('id')
->limit(50)
->get();Choose column order from the access pattern
A candidate is (account_id, status, created_at, id): equality filters precede the date range and requested ordering. This is not a rule that the most selective column must always come first. A MySQL composite index can support its leftmost prefixes, while a query lacking an earlier filter may need a different access path. Review the whole workload before replacing an existing index. InnoDB also stores primary-key values in secondary index records. Check whether explicitly declaring id adds value and whether the optimiser can actually use the desired ordering in your engine and version.
Read the plan before and after one change
-- MySQL 8.4 example for a disposable test copy with an orders table.
-- Never execute index DDL blindly on production. Review existing indexes first.
-- Keep the SQL, parameter values and data identical between comparisons.
EXPLAIN FORMAT=TRADITIONAL
SELECT id, account_id, status, created_at
FROM orders
WHERE account_id = 42 AND status = 'open'
AND created_at >= '2026-09-01 00:00:00'
AND created_at < '2026-09-02 00:00:00'
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- Candidate only; obtain approval and assess write/storage costs separately.
CREATE INDEX orders_account_status_created_id
ON orders (account_id, status, created_at, id);
EXPLAIN FORMAT=TRADITIONAL
SELECT id, account_id, status, created_at
FROM orders
WHERE account_id = 42 AND status = 'open'
AND created_at >= '2026-09-01 00:00:00'
AND created_at < '2026-09-02 00:00:00'
ORDER BY created_at DESC, id DESC
LIMIT 50;Inspect key, type, rows, filtered and Extra in traditional EXPLAIN output. These describe the plan and estimates, not a measured response time. A table scan can be the correct decision for very few rows, and filesort does not automatically indicate a defect. Compare the work involved rather than merely looking for the new index name. EXPLAIN ANALYZE actually executes the SELECT, so use it only in a suitable controlled environment with an understood workload. Do not present an estimated row count as a measured latency improvement.
Check dates, pagination and correct results
The half-open interval includes the start and excludes the next boundary. Calculate these boundaries in the data’s time zone: a local calendar day around daylight-saving transitions is not always twenty-four hours. Compare the same IDs, ordering and values before and after the change. Include empty results, identical timestamps and large accounts. The ID tie-breaker makes ordering deterministic when dates match. Laravel paginate() may issue a separate count query, so a fast list query does not prove that counting is also fast. Evaluate pagination against the actual user interface and consistency requirements.
Do not accept only a faster SELECT
Repeat measurements with the same data, parameters and clearly recorded cache conditions. In addition to reads, measure inserts, updates, storage and other important query patterns. Decide in advance what regression would require rollback, and assess locking when creating or removing an index. The sample is not a production migration file. Keep a record of the previous schema and the approved deployment procedure, including who may stop the change. A valid outcome is that the proposed index is unnecessary or that the application must first issue fewer queries. No performance percentage follows from this example.
Continue the investigation
A query plan is one part of the diagnosis. The following references connect the technical investigation to a scoped SQL service. Demonstrations use fictional data, not the results of a client engagement. For an enquiry, start with a problem description and anonymised context. Do not send passwords, complete database dumps or customer personal data.
Last updated:
