The catalog index

The catalog index is a flattened copy of your catalog that listings, facet counts, sorts and search can read without joining the live product tables. This page explains when to build the index, how to keep it current, and how to rebuild it after direct changes to the product tables.

What the index holds

kahunacart_catalog_index holds one row per variant, carrying everything a listing needs: status, visibility, type, price, sale price, stock state, the sort keys, and the text a search matches on.

The default variant's row is flagged as primary, and a listing reads only primary rows, so it gets one row per product. Every row also carries the product's rollups: the lowest and highest effective variant prices, whether any variant is on sale, and whether any variant can be purchased. So "in this price range" means the product has a variant in it, and "in stock" means it has a buyable variant.

Category terms carry the whole ancestor path, not only the leaves a product is filed on. A department page is one equality test on the department's own id, however deep the tree goes.

Table	Holds
`kahunacart_catalog_index`	One row per variant: status, visibility, prices, sale, stock, sort keys, the search text and the store id
`kahunacart_catalog_index_terms`	The list of categories (with ancestors), tags and attribute values, hanging off the primary row
`kahunacart_catalog_index_queue`	One row per product that needs rebuilding, however many times it changed
`kahunacart_search_synonyms`	The merchant's own search vocabulary
`kahunacart_catalog_search`	SQLite only: the full-text shadow table
`kahunacart_catalog_index_prices`	One row per variant per extra currency where the variant has a price in it: the price, the list price, the sale price and the product's price range, for a price sort, a price range and the on-sale facet in that currency

A store that sells in more than one currency has a fourth table, kahunacart_catalog_index_prices, with one row per variant per extra currency. It holds that variant's price in the currency, its struck-through price, its sale price and the product's cheapest and dearest effective price there, and it is what a price sort, a price range and the on-sale facet read when the shopper is not in the base currency. See More than one currency.

When the index is joined

catalog.index.mode decides this, and it defaults to auto. On auto the index is joined only when a sale, a price list, or a search term is in play. A store running no promotions runs the same queries it ran before the index existed.

Condition	Why the live path cannot answer it well
A sale exists	On the live path `?sale=1` is answered by listing covered product ids in PHP and binding them into the query. Past about 5,000 ids the resolver gives up and the facet stops narrowing. On the index it is one column.
A price list exists	The price a shopper would see is not the one on the variant row, so a price sort and a price facet cannot be answered from the variants table.
A search term	Ranking needs the index's search text column and the engine's own text index.

An index that has never been built is never joined. A completed build stamps catalog.index_built in the settings table; until then every listing takes the live path. A store that upgrades without rebuilding continues to work, but does not receive the index's performance benefits.

Everything else, such as a category page, three ticked facets or a deep page, is faster through the index but is not wrong without it.

A second currency turns it on for good. A store that lists any extra currency in currencies.enabled reads every listing through the index whatever catalog.index.mode says, because the live tables hold one price per variant and it is in the base currency, so a price sort or a price range in euros has nothing to read there. The index has to be built (see below); until it is, a shopper in another currency gets the listing without its price sort and price range, and bin/plugin kahunacart status says so. A store with no extra currency is unchanged: the three conditions above still decide.

Join the index on every listing

Set catalog.index.mode to always and every filtered listing joins the index, whether or not a sale, a price list or a search term is in play. That suits a large catalog that runs no promotions and still wants cheap deep pages and cheap facet counts.

On always, page times stop moving when a sale starts and ends, and every listing depends on the index being current. A store on always needs the background worker running or a cron drain of its own. There is no automatic switch by catalog size.

catalog.index.enabled: false turns the whole thing off and puts every listing back on the live-query path.

Keep the index current

Writes do not rebuild the index inline. They queue the products that need rebuilding, and the queue is drained in the background. A queue entry is one row per product, so editing a product with thirty variants queues one row.

What changed	What is queued
A product is created, edited, deleted or reordered	That product
A variant is written — price, SKU, stock, default flag	Its product, once, however many variants moved
A product's categories or tags are rewritten	That product
A category is moved, reparented or deleted	Every product beneath it, at any depth
A tag is renamed or deleted	Every product carrying it
A sale is created, edited or deleted	Every product
A price list is created, edited or deleted	Every product
A variant's quantity ladder is replaced	Its product, and no other
A variant's price in another currency, an add-on's, a sale's or a deposit's, is written or cleared	That product, once, and the catalogue version moves

A sale or a price list queues every product because no query can say which products its rules might start matching. Invalidation is wired into the data layer, so the admin, the API, the CLI, the seeder, an importer and an add-on all queue the same way.

The background drain

bin/plugin kahunacart work, the once-a-minute worker Grav's scheduler runs as kahunacart-jobs, drains up to catalog.index.per_tick products every tick, 500 by default. Whatever a tick does not reach is still there next minute, so a store catches up over a few ticks.

Set catalog.index.per_tick to 0 to stop the worker touching the index and drive it from cron instead. See CLI.

Rebuild the index

From the admin

In the admin sidebar, click KahunaCart, then Search under Catalogue.
Read the status cards: Products indexed, Waiting and Text matching.
Click Index what changed to drain the queue, or Rebuild everything to throw the index away and build it again.

The screen also says whether the index is being read right now and why. "Built and idle" is normal on a store with no sale and no price list running. The admin action is bounded at 25,000 products; a larger catalog comes back reporting that it is not complete, with the command to finish it.

On a store that sells in more than one currency the report also has a line for the extra currencies, and a rebuild writes their prices too. See More than one currency.

From the command line

Check what is there. bin/plugin kahunacart index reports whether the index is enabled, whether it has ever been built, whether any listing is currently reading it, how many rows and products it holds, how many products are queued, and which text index the engine gave the store.
Build it. bin/plugin kahunacart index --rebuild empties the index, queues every product and drains the queue in chunks of catalog.index.chunk, 1,000 by default.
Or drain only what is queued. bin/plugin kahunacart index --dirty does what the worker does every minute, for when you have just saved a sale and do not want to wait.

Flag	Effect
`--rebuild`	Throw the index away and build it again from the catalog, in chunks.
`--dirty`	Rebuild only the queued products, then stop.
`--chunk`	Products rebuilt per transaction. Defaults to `catalog.index.chunk`.
`--json`	Print a machine-readable summary instead of a report.
neither flag	Report what is there and change nothing.

Add --chunk=250 on a server with a small transaction budget.

Run a rebuild after upgrading, when an importer or a SQL script wrote products directly into the tables, or after restoring a database. A restore onto a different engine always needs one, because the recorded text index no longer belongs to the engine it is now sitting on.

Note

A rebuild is safe on a live store. Each product is replaced in its own transaction, the drain holds a lock so two workers cannot fight over the queue, and a listing whose index is mid-rebuild falls back to the live query path. A run that is killed leaves the finished products correct and the rest queued, so running it again finishes the job.

bin/plugin kahunacart status reports what the index holds without doing any work.

Search on the index

The index carries a search text column, and a migration puts whatever full-text index your own database engine offers over it. This is the sql rung of the storefront's search ladder.

Engine	Text index	Ranking
SQLite	FTS5 shadow table with triggers	The engine's own relevance score
MySQL / MariaDB	`FULLTEXT` over the search text	Natural-language relevance
PostgreSQL	GIN over a text-search expression	The engine's own relevance score
Any other	Portable prefix matching over the search text	A boost column holding the title and SKU words alone

The haystack is weighted by repetition: title 3, SKU 3, summary 2, attributes 1, description 1. A second column holds the weight-3 fields alone, which keeps the title match as the top hit even on an engine with no relevance score.

Results on this rung are ordered by how many of the term's words are in the product's name, then by the engine's own relevance score, then by catalog.sort as a tie-break. Set catalog.search.rank to false to order search results by catalog.sort alone.

A database restored onto a different engine falls back to the portable path: correct, but slower. bin/plugin kahunacart index --rebuild turns the fast path back on, and bin/plugin kahunacart status tells you when you need to.

search.products.driver decides which rung a store searches on. Storefront covers the whole ladder, including how a plain search and a refined one can land on different rungs. A store set to search.products.driver: like keeps its search pages off this index even while the index is joined for a sale on every other listing.

Search synonyms

kahunacart_search_synonyms holds a merchant-editable list of a word and the words it should also find, so a shopper who types jumper finds the sweaters. A word you add works on the very next search with no rebuild. Expansion goes one way only: a row saying jumper finds sweater does not make a search for sweater find jumpers.

In the admin sidebar, click KahunaCart, then Search.
In the Search synonyms panel, fill in a Term and the words it should also find.
Click Save synonyms.

Set catalog.search.synonyms to false to ignore the table without deleting it. Synonyms belong to the sql rung; on the portable substring scan they do nothing. See Storefront.

Extend the index

An add-on can declare its own column on the index and fill it, through the onKahunaCartExtendCatalogIndex event. See Events.

More than one currency

A store with currencies.enabled set keeps the prices of every extra currency in kahunacart_catalog_index_prices. One row per variant per currency, only where the variant has a price in that currency, typed or converted from the base price: a priced variant with no euro amount, in a currency that doesn't convert, has no euro row. A variant with a zero base price gets a zero row in every enabled currency unless a typed amount says otherwise, following the free variant rule. The base currency has no rows here. It keeps reading the columns it always read, so a shopper in the base currency, and a store with only one currency, run the statements they ran before this table existed.

Column	What it holds
`variant_id`, `currency`	Which variant, and which currency. Unique together.
`product_id`	The product, so a product's rows are read and replaced together.
`price_minor`	The price in the currency after price lists, in that currency's minor units. This is what a price range measures.
`list_price_minor`	The struck-through price, worked out the way the base row's is.
`sale_price_minor`	The price under a sale that covers the variant in this currency, or null. A fixed-amount sale with no amount in the currency takes nothing off there, so this is null.
`min_price_minor`, `max_price_minor`	The cheapest and dearest effective price (the sale price when there is one) among the product's priced variants, repeated on each of its rows the way the base index repeats them.

The indexes: a unique one on (store_id, variant_id, currency), which is also the join a price sort makes; (store_id, currency, price_minor, product_id) for the price range; (store_id, currency, variant_id, min_price_minor), which lets a price sort read its number from the index and never the row; and (store_id, product_id) for replacing a product's rows.

What a listing does in another currency. The price sort joins the table by the product's primary variant: LEFT JOIN kahunacart_catalog_index_prices cp ON cp.store_id = ci.store_id AND cp.variant_id = ci.variant_id AND cp.currency = ?, ordered by cp.min_price_minor. A product with no price in the currency is still listed, and goes last in both directions so a shopper sorting by price meets what costs something first. The price range keeps the products that have a variant in it, measured on that currency's own prices (ci.product_id IN (SELECT product_id FROM kahunacart_catalog_index_prices WHERE store_id = ? AND currency = ? AND price_minor >= ? AND price_minor <= ?)), so a product with no price there is in no range. The on-sale facet is on for a product when a variant has a sale price in the currency.

What keeps the rows current. The builder writes them in the same pass as the base rows, for every enabled currency, using the same price lists and the same sale resolver. Saving a price in another currency, clearing one, starting a sale or saving a price list queues the product like any other price change, and the next drain brings its rows into line, writing only what changed. A product that is deleted has its price rows swept by the next drain that has anything to do. Taking a currency out of currencies.enabled leaves its rows where they are, and a drain stops keeping them up.

After you add a currency, rebuild. Adding a code to currencies.enabled does not write its rows for the products you already have. Run bin/plugin kahunacart index --rebuild, or press Rebuild everything on the Search screen. Until then status says "JPY was added since the catalog index was built, so shoppers in it cannot sort or filter by price yet", the Search screen says the same, and a shopper in that currency gets the listing with the price sort replaced by newest first and the price range left off, rather than a sort over rows that are not there. A full rebuild records which currencies it wrote (the catalog.index_currencies row in kahunacart_settings); a drain never does, and a currency a drain stops keeping up is forgotten from that record, so putting it back asks for a rebuild too. If you take every extra currency off, edit products, and put one back, rebuild: the single-currency drain has no record to correct.

Configuration reference

Config key	Default	Effect
`catalog.index.enabled`	`true`	`false` puts every listing back on the live-query path
`catalog.index.mode`	`auto`	`auto` joins the index only when a sale, a price list or a search term needs it; `always` joins it on every filtered listing
`catalog.index.chunk`	`1000`	Products rebuilt per transaction
`catalog.index.per_tick`	`500`	Products the background worker drains per minute; `0` never
`catalog.search.rank`	`true`	`false` orders search results by `catalog.sort` alone, with no relevance
`catalog.search.synonyms`	`true`	`false` ignores the synonym table without deleting it

The full key reference, including search.products.driver and the rest of the search settings, is on Configuration.