Vector Storage and Search

Scenarios

In enterprise-grade applications, the rapid growth of business data has made efficient vector search increasingly important. Traditional architectures that separate relational databases from independent vector databases introduce complexity in data synchronization, high maintenance costs, and poor system consistency. To address these challenges, native support for vector indexes within databases has become an important development trend.

A vector is a floating-point sequence obtained by mapping unstructured data (such as text or images) into a high-dimensional numerical space using an embedding model. In this vector space, semantically similar data points are closer to each other, allowing similarity to be measured by calculating distances between vectors. This capability is widely used in semantic search, recommendation systems, and other AI-driven workloads. However, as data volume grows, performing exact nearest-neighbor search on massive high-dimensional vectors becomes computationally prohibitive due to the curse of dimensionality. To overcome this limitation, TaurusDB introduces Hierarchical Navigable Small World (HNSW) vector indexing, which organizes vectors using a multi-layer navigable small-world graph. This enables logarithmic-time approximate nearest neighbor (ANN) search and supports both Euclidean distance and cosine distance.

By supporting vector indexes natively, TaurusDB provides an efficient, consistent, and easy-to-maintain vector search solution. Unlike traditional separated architectures, TaurusDB enables integrated management and joint querying of structured and vector data within a unified storage and transaction system. This reduces cross-system synchronization and maintenance overhead and significantly improves consistency, query efficiency, and operational stability.

Supported Versions

Native vector support requires a TaurusDB kernel version of 2.0.78.260602 or later. For details about how to check the kernel version, see How Can I Check the Version of a TaurusDB Instance?

How It Works

A vector is a high-dimensional numerical representation generated from unstructured data, such as text or images, using an embedding model. Semantically similar data points are mapped to nearby coordinates in the vector space, allowing similarity to be measured by calculating distances between vectors. TaurusDB calculates similarity using either of two metrics: Euclidean distance (L2) and cosine distance.

Traditional B+Tree indexes are designed for exact match and range queries and cannot efficiently support similarity search on high-dimensional vectors. To address this, TaurusDB introduces HNSW vector indexing, which organizes vector data using a multi-layer neighbor graph. During query execution, the search begins at the top layer (entry node) and proceeds layer by layer to quickly locate candidates closest to the target vector. Ultimately, the top-K similar results are returned.

At the storage layer, each vector index corresponds to a hidden auxiliary table that persistently stores HNSW graph nodes and adjacency relationships. This design enables TaurusDB to reuse InnoDB's transaction, recovery, and locking mechanisms, eliminating the need for a specialized storage engine and ensuring consistent, efficient vector search.

Constraints

Vector Parameters

Vector Functions

• VEC_FROMTEXT(str)
  • Description

    Converts a text string in JSON array format into a binary VECTOR value. For example, '[1.0, 2.0, 3.0]' is converted into an internal floating-point binary representation. It supports up to 16,383 dimensions.

  • Aliases

    TO_VECTOR and STRING_TO_VECTOR, which are identical in functionality to VEC_FROMTEXT

  • Parameters

    str (VARCHAR ): a string in JSON array format, such as '[1.0, 2.0, 3.0]'. JSON or Geometry type parameters are not supported.

• VEC_TOTEXT(vector)
  • Description

    Converts a binary VECTOR value into a human-readable text string in JSON array format (such as '[1.0, 2.0, 3.0]'). The character set is utf8mb4_0900_bin.

  • Aliases

    FROM_VECTOR and VECTOR_TO_STRING, which are identical in functionality to VEC_TOTEXT

  • Parameters

    vector (VECTOR): the internal binary representation of the vector value.

• VEC_DISTANCE(v1, v2)
  • Description

    Calculates the distance between two vectors. The calculation method is automatically selected based on the distance metric defined on the column's vector index:

    If the index is EUCLIDEAN, L2 distance is calculated. If the index is COSINE, cosine distance is calculated.

    If no applicable vector index is found, an error is reported. If either input is NULL, dimensions do not match, or the calculation result is a non-finite value, NULL is returned.

  • Parameters
    • v1 (VECTOR): the first vector, which must be an indexed vector column.
    • v2 (VECTOR): the second vector, which must be a constant. The dimensions of v1 and v2 must match.
• VEC_DISTANCE_EUCLIDEAN(v1, v2)
  • Description

    Calculates the Euclidean distance (L2 distance) between two vectors using the formula sqrt(sum((v1[i] - v2[i])²)). If either input is NULL, dimensions do not match, or the calculation result is a non-finite value, NULL is returned.

  • Parameters

    v1 and v2 (VECTOR): any vector expressions (constants, indexed vector columns, or unindexed vector columns). The dimensions of both vectors must match.

• VEC_DISTANCE_COSINE(v1, v2)
  • Description

    Calculates the cosine distance between two vectors using the formula: 1 - dot(v1, v2) / (|v1| × |v2|). If either input is NULL, dimensions do not match, or the calculation result is a non-finite value, NULL is returned.

  • Parameters

    v1 and v2 (VECTOR): any vector expressions (constants, indexed vector columns, or unindexed vector columns). The dimensions of both vectors must match.

• VECTOR_DIM(vector)
  • Description

    Returns the number of dimensions of a vector (that is, the number of floating-point elements). The calculation method is to divide the binary length by sizeof(float) (4 bytes).

  • Parameters

    vector (VECTOR): the internal binary representation of the vector value. A BIGINT value is returned.

Creating Vector Columns

Use the VECTOR(N) type in a CREATE TABLE or ALTER TABLE statement to define an N-dimension vector column.

N specifies the dimension count and the value range is [1,16383].

• Create a table that contains a vector column.

  CREATE TABLE items (
      id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(255),
      embedding VECTOR(128)
  );

• Add a vector column to an existing table.

  ALTER TABLE products ADD COLUMN description_vec VECTOR(64);

Using Vector Indexes

• Create an HNSW vector index.

  CREATE VECTOR INDEX idx ON t(v)

  Examples

  Create a vector index named vidx on the embedding column of the items table using default parameters.

  CREATE VECTOR INDEX vidx ON items(embedding);

• Specify index parameters, including the number of neighbors (M) and distance type.

  CREATE VECTOR INDEX idx ON t(v) M=N DISTANCE=metric

  Examples

  Create an index named vidx on the embedding column of the items table, set the maximum number of connections per node to M=16, and set the distance type to COSINE.

  CREATE VECTOR INDEX vidx ON items(embedding) M=16 DISTANCE=COSINE;

• Create a vector index during table creation.

  CREATE TABLE t(..., VECTOR INDEX(v))

  Examples

  Create a table named items containing an auto-increment primary key id and a 128-dimension vector column embedding, along with a vector index named vidx configured with M=16 and Euclidean distance.

  CREATE TABLE items (
      id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      embedding VECTOR(128),
      VECTOR INDEX vidx(embedding) M=16 DISTANCE=EUCLIDEAN
  );

• Drop a vector index.

  ALTER TABLE t DROP VECTOR INDEX idx

  Examples

  Delete the vector index vidx from the items table.

  ALTER TABLE items DROP INDEX vidx;

Vector Search

• Vector similarity search: returns the top N most similar vectors.

  SELECT * FROM t ORDER BY VEC_DISTANCE(v, query_vec) LIMIT N

  Example

  Query the top 10 records in the items table that are most similar to the vector [1.0, 0.5, 0.8, ...]. embedding is a vector column and VEC_FROMTEXT('[1.0,0.5,0.8,0.2,0.3]') converts the text into a query vector.

  SELECT * FROM items
  ORDER BY VEC_DISTANCE(embedding, VEC_FROMTEXT('[1.0,0.5,0.8,0.2,0.3]'))
  LIMIT 10;

• Hybrid search: applies scalar filters first and then sorts vectors.

  SELECT * FROM t WHERE category='A' ORDER BY VEC_DISTANCE(v, query_vec) LIMIT N

  Example

  Query the top 5 most similar records in the items table where category is set to electronics. The records are first filtered by category and then sorted by vector distance.

  SELECT * FROM items
  WHERE category = 'electronics'
  ORDER BY VEC_DISTANCE(embedding, VEC_FROMTEXT('[1.0,0.5,0.8,0.2,0.3]'))
  LIMIT 5;

• Distance output: displays calculated distance values.

  SELECT id, VEC_DISTANCE(v, query_vec) AS dist FROM t ORDER BY dist LIMIT N

  Example

  Query the top 10 records in the items table that are most similar to the query vector, displaying the id and distance value dist for each record.

  SELECT id, VEC_DISTANCE(embedding, VEC_FROMTEXT('[1.0,0.5,0.8,0.2,0.3]')) AS dist
  FROM items
  ORDER BY dist
  LIMIT 10;

• Forced index search: forces the use of a vector index.

  SELECT * FROM t FORCE INDEX(vidx) ORDER BY VEC_DISTANCE(v, query_vec)

  Example

  Force the use of the vector index vidx to query the top 10 records in the items table that are most similar to the query vector.

  SELECT * FROM items
  FORCE INDEX(vidx)
  ORDER BY VEC_DISTANCE(embedding, VEC_FROMTEXT('[1.0,0.5,0.8,0.2,0.3]'))
  LIMIT 10;

Examples

1. To enable the vector feature, submit a service ticket.
2. Set the isolation level.

   SET transaction_isolation = 'READ-COMMITTED';

3. Create a table and a vector index.

   CREATE TABLE product_embeddings (
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   product_name VARCHAR(255),
   embedding VECTOR(5) NOT NULL,
   -- Create a vector index and specify M and the distance metric.
   VECTOR INDEX idx_embedding(embedding) M=16 DISTANCE=COSINE
   );

4. Insert data.

   INSERT INTO product_embeddings (product_name, embedding) VALUES
   ('product_A', VEC_FROMTEXT('[0.1, 0.2, 0.3, 0.4, 0.5]')),
   ('product_B', VEC_FROMTEXT('[0.6, 0.7, 0.8, 0.9, 1.0]')),
   ('product_C', VEC_FROMTEXT('[0.11, 0.22, 0.33, 0.44, 0.55]'));

5. Perform a vector similarity search.

   -- Find the two products most similar to the vector '[0.1, 0.2, 0.3, 0.4, 0.51]'.
   SELECT id, product_name, VEC_DISTANCE(embedding, VEC_FROMTEXT('[0.1, 0.2, 0.3, 0.4, 0.51]')) AS similarity_score
   FROM product_embeddings ORDER BY
   similarity_score ASC -- Smaller COSINE distance indicates higher similarity.
   LIMIT 2;