+48 501 250 220

A shop database without indexes: every filter and join became a full scan, until the right indexes brought microseconds

A shop database without indexes worked as long as the tables were small. At 500 000 orders and 1.75 million order items every filter and every join came down to a full scan. We measured exactly what the missing indexes cost, added the right ones, and documented the traps that make an index useless even though it exists.

Book an intro call The MySQL and MariaDB service
  • A join over three tables: 406 ms, 0.075 ms with the index
  • Filtering orders: 500 000 rows scanned, 5 with the index
  • An index present and still skipped: we show when and why
A table of twelve rows; on its left a long arrow runs down all rows under the label ALL with 500 000 below, on its right an index card sends one orange arrow to a single row under the label ref with 5 below

The database of an online shop on MariaDB: customers, products, orders, order items, reports and customer history

Challenge

The shop database had been designed in a hurry and had nothing but primary keys. With small tables everything was instant, so the missing indexes did not show. As the data grew, queries that used to take milliseconds took hundreds of milliseconds, and lists and reports under load kept the database busy for long moments.

The task: show by measurement what the missing indexes cost on the real data, add the right indexes for the typical queries of a shop, a filter by customer, the order history with a join, a sales report and pagination, and point out the traps that make an index useless even though it was created.

Solution

The database as we found it: four tables, customers, products, orders and order items, with primary keys only. The foreign keys between them existed only in the application code, not as constraints in the database, so nothing had ever created an index on them. For every typical query of the shop we recorded the plan, the number of rows read by a scan and the median time, then added the right indexes one by one and measured the same query again.

The traps got their own measurements: cases in which an index exists and the database does not use it, or uses it badly. And because an index is not a free gain, we measured how much five indexes slow down writes and how much space they take. The shop's data at the time: 100 000 customers, 50 000 products, 500 000 orders and 1.75 million order items on MariaDB 10.11 with InnoDB, as of 2026-10.

Results

Customer history, a join over three tables
From 406.8 ms to 0.075 ms
A customer's orders
From 500 000 rows scanned to 5
Look-up by email address
From 5.9 ms to 0.024 ms
The ten latest orders
From 39.3 ms to 0.009 ms
Sales report by status and date range
From 34 268 rows to 3 336 with a composite index
The price of five indexes on the orders table
Inserts 3.1 times slower, indexes at 237 percent of the table size

How we worked

  1. The starting point as we found it

    Four tables with primary keys only; the foreign keys between them lived in the application code and had no index in the database.

  2. The baseline for every query

    The plan from EXPLAIN, the counter of rows read by a scan, which does not depend on the cache, and the median time of many runs.

  3. The right indexes, the same measurement

    An index for every column in a filter, a join or a sort, added one by one to the shop's database, then the identical query again.

  4. The traps measured separately

    An index that exists and is skipped, a function on the column, a type mismatch, the order of columns in a composite index, a covering index.

  5. The cost counted

    Space on disk and the slowdown of writes with five indexes on the orders table.

  6. A checklist for the team

    Which queries to check, which columns to index, which indexes to drop because no plan ever picks them.

Challenges we overcame

That an index makes a query faster is not the interesting part. Interesting is when an index exists and does not help, and how to read the plan to see it.

"Index used" is not the same as fast

The plan shows a key name even when the database reads the whole index. The column to read is the access type: ref, eq_ref and range mean a look-up through the index, index means a full scan of the index, ALL a full scan of the table. A plan can name a key and announce Using index and still read a million entries underneath. The key name is not the evidence; the access type and the number of rows are.

The index exists and the database ignores it

On the status column, five distinct values, we created an index, and a sum of order totals for paid orders still ran as a full scan of the table: 500 000 rows, 35.7 ms. That is not a mistake of the optimiser but a decision: paid orders are 20 percent of the table, and the total is not in the index, so using the index would mean 100 000 random jumps to rows, which costs more than one sequential scan. An index on a column with few distinct values often changes nothing.

A function on the column switches the index off

With an index on the order date, two queries for the same day behave completely differently. The condition with the date wrapped in a function reads the whole index, 500 000 entries, 32.7 ms. The condition as a range from midnight to midnight reads 556 rows in 0.073 ms. The difference of about 447 times comes only from the function around the column:

WHERE DATE(created_at) = '2026-06-15'                              full index scan
WHERE created_at >= '2026-06-15' AND created_at < '2026-06-16'      range look-up

Wrapping an indexed column in anything, a function, a concatenation, a cast, takes the range look-up away. The rule is written on the side of the value, not of the column.

An implicit type conversion: a wrong result, and slower

The most dangerous trap, because it hits correctness, not only speed. The status column is text. Compared with the text value, the query returns 100 000 rows in 8.5 ms through the index. Compared with the number 0 instead of a text, it returns all 500 000 rows in 84.6 ms with a full scan of the index. MariaDB casts every text to a number for the comparison, every status becomes 0, and the condition matches every row. The query is slower, returns the wrong result, and raises only a warning that nobody reads. The types in a condition have to match.

A composite index works from the left only

A composite index on product and quantity in the order items serves a condition on the product in 0.020 ms. A condition on the quantity alone cannot use it for a look-up and scans the whole index, 1.75 million entries, 96.1 ms. A multi-column index serves conditions from its left edge only, so the order of the columns is a design decision, chosen for the real queries.

A covering index removes the jumps to rows

A list of the thousand latest orders with id and total: with an index on the date alone the database reads the index in order but has to jump to every row for the total, 0.75 ms. With an index on date and total everything the query needs is in the index, no jumps, 0.24 ms. With larger results and a cold cache the difference is far bigger than these three times.

Two lanes: the upper condition DATE(created_at) = … ends at a cross before an index card; the lower condition created_at >= … AND < … reaches its index card on an orange lane

Measured without and with the index

Query Without index With index Speed-up
Look-up by email address Full scan, 100 000 rows, 5.9 ms Look-up, 1 row, 0.024 ms About 245 times
A customer's orders Full scan, 500 000 rows, 24.1 ms Look-up, 5 rows, 0.020 ms About 1 200 times
Customer history, join over 3 tables Scan of the order items, 1.75 million rows, 406.8 ms Look-ups, 0.075 ms About 5 400 times
The ten latest orders Full scan and a file sort, 39.3 ms Read from the index, 10 rows, 0.009 ms About 4 360 times
Sales report, status and date range Index on the date, 34 268 rows, 9.7 ms Composite index, 3 336 rows, 2.2 ms About 4.4 times
Cost: inserting 200 000 orders 0.53 s without indexes 1.64 s with five indexes, 60.6 MB of indexes for 25.6 MB of data 3.1 times slower

What we take from it

  • Index the columns of conditions and joins. Columns in a filter, in a join condition and in a sort are the first candidates. A logical foreign key without an index is the most common cause of slow joins.
  • Read the plan, especially the access type. ALL and index are scans; the goal is ref, eq_ref or a narrow range. The key name alone proves nothing.
  • Do not wrap indexed columns in functions. Move the operation to the side of the value, or consider a computed column with its own index.
  • Watch the types in conditions. Comparing text with a number can return a wrong result and switch the index off.
  • Choose the column order of a composite index for the queries. Only the left prefix works.
  • Consider covering indexes for hot queries, and weigh them against the cost of writes and space.
  • Index with restraint. Every index slows writes and takes space: five indexes on the orders table took 237 percent of the table's size and made inserts 3.1 times slower. Add the ones that are used, drop the ones no plan ever picks, and measure again on the real data. The same discipline applies on the application side, where a large result set pulled into memory can take a server down.

Sources

On one base line a black block labelled 26 MB for the data of the orders table and, beside it, a taller orange stack of five blocks labelled 61 MB for its indexes

Related situations

  • Take over a legacy system without documentation

    Your system runs, but nobody knows it in detail. We record application, database, server and email, assess the risks, recommend the next step in writing and, if you wish, take over operations.

  • Developer left?

    Your developer has resigned or already left, and the system has to keep running. We review the system and give you a written report 48 hours after access: its state, the immediate risks and a plan for the first 30 days.

More projects

  • A score scale with a dashed threshold: the phishing envelope moves in an orange arc across the threshold

    A spam filter that ran but did not protect

    Services up, configuration valid, no errors in the system status. Yet phishing landed in the inboxes while partners' newsletters went to spam. Our engineers measured which detection mechanisms actually fired, repaired the causes, and three months later found and fixed a regression that only showed up hour by hour.

  • Four envelopes pass the gates SPF, DKIM and DMARC, each ticked in orange, on the way into the inbox

    Email deliverability for four company domains

    A mail server for four company domains signed nothing, published no DMARC policy and was rejected by Microsoft. Our engineers audited it, piloted the change on one domain and rolled it out in batches. The result: all three checks pass at Gmail, the Microsoft block was lifted, and the users noticed nothing.

In 20 minutes we work out which first step fits your system

You tell us the goal and the current state, and we say openly whether and how we can help. Reply within one working day, no obligation.

Book an intro call How we work
Phone (German, Polish, English) +48 501 250 220
Email Email

Reachable: Monday to Friday, 9:00 to 17:00 Central European Time; support and emergencies around the clock (24/7)