Vinay Garg Assistant Professor

What a million pages taught me about caching

A PDF search engine with a million pages will not be rescued by a faster server. It is rescued by asking the database fewer questions.

Four stacked translucent plates representing cache layers, with a query arrow routing around them

In short

  • A page that takes 900 ms is usually forty small waits, not 900 ms of work.
  • Reading every query for one page is the fastest optimisation available, and nobody does it.
  • Caching settings, categories and navigation removed one query from every request on this site.
  • A FULLTEXT index turned the search box from a table scan into a lookup.

41 → 3

SQL queries on a listing page after the audit

own audit, ebooknetworking.net

1M+

PDF pages indexed and searched

ebooknetworking.net

5 min

cache lifetime for settings, categories and navigation

this site

The portal I maintain indexes more than a million PDF pages. For years the answer to every slow page was a bigger machine, and for years that answer stopped working within a month. What finally changed things was an unglamorous audit of how many queries a single page view was asking for.

The number nobody measures

A page that takes 900 ms usually is not doing 900 ms of work. It is doing forty small waits. Each one looks harmless in isolation — a category name here, a count there — and together they are the page.

  • Forty-one queries per listing page, thirty-eight of them selecting one row each.
  • The same category list, rebuilt for every request, on every page.
  • A LIKE '%term%' search that scanned the full text of every document.

None of that is exotic. All of it is invisible until you count.

Three changes worth more than new hardware

Ask once. Join what belongs together, and fetch the rows you will actually render in a single pass. The listing page went from forty-one queries to three.

Cache the things that never change within an hour. Categories, navigation, site settings: a small file cache with a five-minute lifetime removed a query from every single request, and an edit in the admin still shows up immediately because the save clears the key.

Index for the query you write. A full-text index on the title, excerpt and a plain-text copy of the body turned the search box from a scan into a lookup. The difference on a million rows is not ten percent; it is the difference between a page and a timeout.

The database is rarely slow. The conversation with it is.

The habit

Before optimising anything, log the queries for one page and read them. Not the slow-query log — every query, in order, with the page they belong to. The embarrassing ones are usually visible in the first minute, and they are usually the ones you wrote yourself.

Sources

Frequently asked questions

How many database queries should a listing page run?

As few as the data allows — usually one or two. A page that fetches forty single rows can almost always be rewritten as three joins and one cached lookup. Count the queries before you tune anything else.

Is a bigger server ever the right fix?

Only after the query count is sane. Hardware buys headroom for work you actually need; it does not remove forty redundant round trips that a single join would replace.

Why cache settings and categories instead of querying them?

They change when an administrator edits them, not when a visitor loads a page. A short-lived file cache with a five-minute lifetime removes a query from every request, and saving in the admin clears the key immediately.

When is FULLTEXT better than LIKE?

Whenever the table is large. LIKE with a leading wildcard cannot use an index and reads every row; a FULLTEXT index on title, excerpt and a plain-text copy of the body turns the same search into a key lookup.

Vinay Garg

Vinay Garg

Assistant Professor of Computer Science at Multani Mal Modi College, Patiala. Twenty-five years of teaching web programming, and a habit of building the things I teach about.