Bagisto MariaDB Vector Semantic Search

I’m always interested to see how MariaDB Vector is being used in real applications, beyond the usual demos and simple similarity-search examples.

The result is a useful real-world example of what you can achieve with MariaDB 11.8 LTS and native vector search, without introducing a separate vector database into the stack.

Thanks to the Bagisto team for sharing their work and results with the MariaDB community.

Bagisto is an open-source eCommerce platform developed by Webkul and built on the Laravel framework.

---

How Semantic Search Works

An embedding model turns each product’s text into a fixed-length list of numbers called a vector.
Products that mean similar things are positioned close together in that vector space.
When a shopper searches, their query goes through the same model and becomes a vector too.
The search then becomes a geometry problem: find the products whose vectors are closest to the query vector.
That closeness is measured with cosine similarity, and finding the closest results is called a k-nearest-neighbour (kNN) search.

---

What Gets Embedded

The extension flattens each product into a single text blob before sending it to the embedding model.
That blob includes the product name, short description, full description, SKU, category paths, and every EAV attribute that is not a file or image.
Configurable products fold in all their variant option labels.
Bundles add option labels and child product names.
Grouped, downloadable and booking products add their own associated text.

// Webkul\SemanticSearch\Services\SemanticSearchService

protected function prepareDocumentData(Product $product): array
{
    return [
        'content'  => $this->buildContentBlob($product),  // what the model sees
        'metadata' => [
            'price'      => $product->price,
            'brand'      => $product->brand,
            'color'      => $product->color,
            'size'       => $product->size,
            'categories' => $product->categoryPaths,
        ],
    ];
}

The content field is what the embedding model encodes into a vector.
The metadata field is what the intent parser later filters on.

---

The MariaDB Table

The driver creates its table lazily on the first upsert.
The VECTOR column needs the embedding dimension, which is only known once the first embedding exists.

CREATE TABLE `semantic_search_vectors` (
  `product_id`  int(10) unsigned NOT NULL,
  `name`        varchar(255),
  `sku`         varchar(255),
  `categories`  text,
  `description` longtext,
  `content`     longtext,
  `metadata`    longtext CHECK (json_valid(`metadata`)),
  `price`       decimal(12,4) NOT NULL DEFAULT 0.0000,
  `embedding`   vector(1536) NOT NULL,
  `created_at`  timestamp NULL,
  `updated_at`  timestamp NULL,
  PRIMARY KEY (`product_id`),
  FULLTEXT KEY `semantic_search_vectors_fulltext`
    (`name`,`sku`,`categories`,`description`,`content`),
  VECTOR KEY `embedding` (`embedding`) `distance`='cosine'
) ENGINE=InnoDB;

The table carries both a VECTOR KEY for semantic kNN and a FULLTEXT KEY for the text fallback path.

---

The kNN Query

When a shopper submits a query, the extension generates an embedding and runs this against the table:

SELECT product_id, name,
       1 - VEC_DISTANCE_COSINE(embedding, VEC_FromText(?)) / 2 AS score
  FROM semantic_search_vectors
 ORDER BY VEC_DISTANCE_COSINE(embedding, VEC_FromText(?))
 LIMIT ?

The formula 1 - distance / 2 rescales cosine distance into a 0 to 1 similarity score.
A score of 1 means identical. A score near 0.5 means unrelated. The extension filters results below min_score (default 0.3) before returning them.

---

Test Setup

All tests ran on MariaDB 11.8.9, the current long-term support release, using the official mariadb:11.8 Docker image. Vector search has been available since MariaDB 11.7, so 11.8 is the first LTS release to include it.

Item	Detail
Machine	Intel Core i5-8400 (6 cores, 6 threads, 2.8 GHz), 7.5 GB RAM, SATA SSD
Operating system	Ubuntu 22.04.5 LTS, Docker 29.8
Database	MariaDB 11.8.9, container limited to 4 CPUs and 2 GB RAM, binary log off
Application	Bagisto 2.4.10, Laravel 12.62.0, PHP 8.3.33, on the same machine as the database
Catalogue	25,000 generated products across 8 departments, 41 product types, 10 colours, 30 brands
Vectors	1536 dimensions, cosine distance

The tests were run twice, under two server configurations:

Setting	Default configuration	Tuned configuration
innodb_buffer_pool_size	128 MB (default)	1 GB
mhnsw_max_cache_size	16 MB (default)	512 MB
mhnsw_ef_search	20 (default)	20 (default)
mhnsw_default_m	6 (default)	6 (default)

---

Test 1: Indexing 25,000 Products

The 25,000 products were indexed through the extension’s own driver, one upsert per document, exactly as semantic-search:index calls it. Only the database upsert was timed.

Metric	MariaDB Vector
Time in upserts	146 s
Throughput	171 docs/s
Mean upsert	5.8 ms
p50 upsert	4.9 ms
p90 upsert	9.8 ms
p95 upsert	12.1 ms
p99 upsert	13.6 ms
Worst single upsert	177 ms
First call (schema DDL)	92 ms
On-disk size	339 MB

At 171 documents per second, a 25,000-product catalogue is indexed in about two and a half minutes.
The write tail is tight: 99% of upserts finish in under 14 milliseconds.
The one-time first call (92 ms) is the driver creating the table and adding the vector index before writing the first document.
On disk, 25,000 products at 1536 dimensions take 339 MB, of which the HNSW graph index accounts for about 95 MB.

---

Test 2: Plain Semantic Search (No Filters)

A shopper types “warm coat for winter hiking.” Bagisto generates an embedding and runs a plain kNN query that returns the 10 closest products, with no price range, colour or brand filter.
Timings are for the extension’s vectorSearch() call, which includes Laravel’s query builder and result mapping. 300 different queries were run one after another from a single client.

Metric	Default configuration	Tuned configuration
Mean	31.7 ms	14.5 ms
p50 (median)	26.8 ms	10.0 ms
p90	46.5 ms	13.3 ms
p95	67.1 ms	15.1 ms
p99	94.7 ms	208.6 ms
Slowest call	366 ms	234 ms
Cold first query	102 ms	22 ms

A 32 ms mean with default settings is already well inside what feels instant to a shopper.
With a vector cache large enough to hold the graph, the median drops to 10 ms. 

The tuned p99 reflects a handful of calls that hit a slow moment in the PHP process or on the machine, not the database’s steady-state latency. The raw SQL in Test 3 shows a p99 of 2.5 ms.
All 300 queries returned 10 results in both configurations.

---

Test 3: Throughput Under Concurrency

A single client sending one query after another measures latency, not throughput. To measure how much search traffic one MariaDB server can absorb, 1, 2, 4 and 8 clients ran the kNN query at the same time for 20 seconds each. Each client was a separate PHP process with its own connection, sending the same SQL the extension generates.

Concurrent clients	Default config: queries/s	Default config: p50 / p95	Tuned config: queries/s	Tuned config: p50 / p95
1	36	23.2 / 59.0 ms	443	2.2 / 2.4 ms
2	112	17.5 / 25.2 ms	877	2.3 / 2.4 ms
4	253	13.6 / 24.2 ms	1,747	2.3 / 2.4 ms
8	372	19.1 / 29.7 ms	1,904	3.1 / 8.4 ms

Throughput scales almost linearly up to 4 clients, matching the 4 CPUs given to the container.

At 8 clients the database and the 8 client processes compete for the machine’s 6 cores, so the gain flattens.
In the tuned configuration, a single MariaDB server handled about 1,900 unfiltered semantic searches per second over 25,000 products on a six-core desktop machine.
The client processes ran on the same machine, so these figures understate what a dedicated database server would achieve.

---

Test 4: Semantic Search With Filters

Real storefront queries often come with filters.
A shopper types “navy jacket between $50 and $300.” The LLM intent parser converts that into a vector plus a structured filter set.
We applied price_min: 50, price_max: 300, color: navy – matching about 5% of the catalogue. 100 different queries were run through the extension’s vectorSearch().

Metric	Default configuration	Tuned configuration
Mean	181 ms	48.7 ms
p50 (median)	179 ms	31.7 ms
p90	245 ms	94.3 ms
p95	285 ms	181 ms
p99	364 ms	276 ms
Slowest call	436 ms	311 ms

Adding a filter pushed the default-configuration mean from 32 ms to 181 ms. With the graph cached, filtered search averaged 49 ms.
In the default configuration, one of the 100 filtered queries returned 3 results instead of 10. In the tuned configuration, all 100 returned 10.

---

Why Filters Slow MariaDB Down

A vector search only knows one thing: distance. It has no concept of price, colour, or brand.
Unfiltered, it stops as soon as it has ten good neighbours.
With a filter, it can no longer stop there. The vector index returns candidates in distance order, not in filter-match order.
If only 5% of products match, MariaDB must examine about 200 candidates to collect 10 that survive the filter.

• Requested results: 10
• Filter selectivity: 5%
• Candidates needed: 10 / 0.05 = 200
• Default mhnsw_ef_search: 20

Two costs compound the deeper walk:

• Cache misses: With the defaults, the vector graph cache (mhnsw_max_cache_size, 16 MB) and the buffer pool (128 MB) are much smaller than the 339 MB table. A shallow walk mostly stays in memory, but a deep walk reaches uncached graph nodes and pages. This is why the tuned configuration cut filtered latency by more than two-thirds.
• CPU per candidate: The colour filter is evaluated per row with a JSON extraction, not with an index lookup.

EXPLAIN confirms the order of operations:

EXPLAIN
SELECT product_id, name,
       1 - VEC_DISTANCE_COSINE(embedding, VEC_FromText(?)) / 2 AS score
  FROM semantic_search_vectors
 WHERE price >= 50 AND price <= 300
   AND LOWER(JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.color'))) = 'navy'
 ORDER BY VEC_DISTANCE_COSINE(embedding, VEC_FromText(?))
 LIMIT 10;

-- key: embedding        ← vector index is used
-- Extra: Using where    ← filter is applied AFTER index retrieval

— key: embedding        ← vector index is used

— Extra: Using where    ← filter is applied AFTER index retrieval

---

Test 5: Text Fallback (No AI Provider)

When the AI provider is unreachable (no API key, a provider outage, or an exhausted quota), the extension falls back to full-text search.
This path then serves every storefront search, so its latency is the storefront’s latency. 200 different queries were run through the extension’s searchByText().

Metric	Default configuration	Tuned configuration
Mean	1,102 ms	23 ms
p50 (median)	1,027 ms	17.8 ms
p90	1,530 ms	29.8 ms
p95	1,894 ms	35.4 ms
p99	2,315 ms	67.6 ms
Slowest call	2,648 ms	249 ms

With the default 128 MB buffer pool, the text fallback is slow, averaging about one second per search. The full-text query reads the matching rows, and the rows of this table are large because each one carries a 6 KB vector, so they do not fit in a small buffer pool.

When the buffer pool can hold the table, the same queries average 23 ms.
Any store that relies on the text fallback should size the buffer pool to the table.

---

Test 6: Search Quality

HNSW is an approximate index. It trades a small amount of accuracy for speed, so a fast search is only useful if it still returns the right products.

The standard measure is recall@10: of the 10 products an exact search returns, how many does the vector index also return? The exact results came from the same query with the vector index disabled (IGNORE INDEX (embedding)), which forces MariaDB to compute the distance to every product.

Recall was measured over 100 queries for plain search and 50 queries for filtered search. A tie-aware check, which counts a result as correct when its distance is equal to or better than the exact 10th result, gave the same figures. 

The misses are therefore genuine and not ties between equally distant products.

Vector index	mhnsw_ef_search	Plain recall@10	Plain mean latency	Filtered recall@10	Filtered mean latency
M = 6 (default)	20 (default)	59.2%	2.4 ms	58.0%	5.1 ms
M = 6 (default)	100	64.7%	2.6 ms	not measured	not measured
M = 16	20 (default)	94.5%	2.4 ms	97.4%	7.4 ms
M = 16	100	97.5%	3.2 ms	not measured	not measured
No vector index (exact)	not applicable	100%	full scan	100%	183 ms*

Latencies are raw SQL in the tuned configuration, measured from a single client.

* The exact filtered search uses a B-tree index on a virtual colorcolumn to read only the navy products, then computes each one’s distance. See Where to Optimize.

This is the most important tuning result in this article. With MariaDB’s default graph degree (M = 6), the index returned only about 6 of the true 10 nearest products on this catalog, and raising mhnsw_ef_search helped only a little. 

Rebuilding the index with M = 16 raised recall to 94.5%, and to 97% for filtered search, with no measurable latency cost. Rebuilding the index for 25,000 vectors took 114 seconds.

The Bagisto extension currently creates its index with MariaDB’s defaults. A store can rebuild it once after indexing:

ALTER TABLE semantic_search_vectors DROP INDEX embedding;
ALTER TABLE semantic_search_vectors ADD VECTOR INDEX embedding (embedding) M=16 DISTANCE=cosine;

Alternatively, set mhnsw_default_m = 16 in the server configuration before the extension creates its table.

---

Where to Optimize

These are the changes that moved the numbers in these tests, in order of impact:

• Size the vector cache and the buffer pool to the data. The default mhnsw_max_cache_size of 16 MB cannot hold the graph of a 25,000-product catalog. Raising it, together with innodb_buffer_pool_size, cut the plain search median from 27 ms to 10 ms, filtered search from 181 ms to 49 ms, and the text fallback from 1.1 s to 23 ms. It also raised single-client throughput from 36 to 443 queries per second.

# my.cnf
innodb_buffer_pool_size = 1G
mhnsw_max_cache_size    = 512M

• Build the vector index with M = 16. This raised recall@10 from 59% to 94.5% at the same latency. See Test 6.

• Expose filter attributes as indexed virtual columns. Instead of copying color, brand and size out of the JSON into regular columns, add virtual columns computed from the metadata. Nothing is duplicated, and the values can never drift from the JSON:

ALTER TABLE semantic_search_vectors
  ADD COLUMN color varchar(100) AS (JSON_VALUE(metadata, '$.color')) VIRTUAL,
  ADD INDEX idx_color (color);

Results measured on MariaDB 11.8.9:

• Query plan: with the filter applied to color = ‘navy’ instead of the JSON expression, the optimizer continues to use the vector index and applies the filter afterwards (EXPLAIN shows key: embedding, Using where). Because the column is virtual and its index is not used in this plan, JSON_VALUE() is evaluated for each candidate row.
• Latency: the mean filtered query time was 310 ms with the virtual column, compared with 341 ms with the JSON expression, a reduction of about 9%. The graph walk accounts for most of the query time in both cases.
• Exact search option: the index makes an exact, pre-filtered search possible. With FORCE INDEX (idx_color), the query returned 100% of the true nearest navy products, with a mean time of 183 ms.
• Disk space: adding the virtual column completed instantly and did not change the table size. The index added 1.52 MB to a 325.6 MB table, about 0.5%. Adding the index took 47 s, during which the table, which also carries the FULLTEXT and vector indexes, was rebuilt. For comparison, a regular column populated from the JSON took 211 s to add, populate and index, and increased the row data by 0.02 MB.

The extension’s queries currently filter on the JSON expression, so using the column requires a change to its repository.

• Raise mhnsw_ef_search only after fixing M. With M = 6, raising it from 20 to 100 improved recall by only 5 points. With M = 16, the same change took recall from 94.5% to 97.5% for 0.8 ms more per query.

SET GLOBAL mhnsw_ef_search = 100;

• Re-index after significant changes: Run php artisan semantic-search:index --fresh after significant infrastructure or schema changes.

---

Key Takeaways

1. MariaDB 11.8 LTS stores and indexes vectors natively. Bagisto 2.4.10 uses the VECTOR column for semantic search without any extra infrastructure. These tests used MariaDB 11.8.9.

2. Indexing is fast. 25,000 products were indexed in about two and a half minutes, at 171 documents per second.

3. Plain semantic queries are quick and scale with CPU cores. Median latency was 27 ms with default settings and 10 ms with a properly sized cache. A single server handled about 1,900 searches per second with 8 concurrent clients on a six-core desktop machine.

4. The defaults are conservative, so tune two settings. Sizing mhnsw_max_cache_size and the buffer pool to the data gives the largest speed gain. Building the index with M = 16 gives the largest accuracy gain, from 59% to 94.5% recall@10 at no latency cost.

5. Filtered queries cost more, and virtual columns give you a choice. Post-filtering makes the graph walk deeper. With the cache sized correctly, a 5%-selective filter averaged 49 ms. An indexed virtual column lets you switch to an exact pre-filtered search when accuracy matters most.

6. At 25,000 products, MariaDB Vector provides fast unfiltered semantic search without requiring a separate vector database.

Saurav Pathak VP – Product & Sales (Bagisto)