On November 2, 2026, I refactored the logic for detecting duplicate blog post titles. Previously, all stored embeddings were read into Python to compute similarity; this time, I changed it to retrieve only nearby title candidates using the vector index in MariaDB 11.8+.
A key aspect to consider here was the SQL ORDER BY clause. ORDER BY 1 - VEC_DISTANCE_COSINE(...) DESC expresses the order by similarity, but wrapping the distance function in an expression may prevent the vector index from being used. The official documentation explains that you should sort by the distance function or its alias directly in ascending order and use LIMIT together with it. MariaDB Official Documentation
Thus, I set the search criteria based on distance, and converted the returned values in Python using similarity = 1 - distance. Below is a SQLAlchemy binding example with only the core parts included. :query_vector receives a JSON string of a vector generated by the same embedding model.
SELECT translation_id,
VEC_DISTANCE_COSINE(embedding, VEC_FromText(:query_vector)) AS distance
FROM title_vectors
ORDER BY VEC_DISTANCE_COSINE(embedding, VEC_FromText(:query_vector)) ASC
LIMIT 10;This example assumes that embedding has an DISTANCE=cosine vector index. If the distance metric used when creating the index differs from the search function, the index will not be used. VEC_DISTANCE_COSINE Documentation
I also added an integration test for this change to verify that the key of the EXPLAIN results is the expected vector index. Although separate measurement is needed to quantify the performance improvement, the code clearly shows that the search path has been reduced from reading all vectors in Python to retrieving only candidates.
After creating the index, make sure to verify whether the query actually uses it. Expressing the same order via formula does not necessarily result in the same execution plan.
