# Vector type and index reference

Beta featureLearn how to use vectors in MySQL on PlanetScale

**Note**

PlanetScale MySQL vectors is still in beta and is not intended for use in production workloads.

## Vector type

PlanetScale MySQL provides a `VECTOR(X)`

type that can be used to store vectors. To add a vector column to a table, set it to type `VECTOR(X)`

where `X`

is the dimension of the vectors to be stored in this column.

### Example

```
CREATE TABLE t1 (
id INT PRIMARY KEY auto_increment,
embedding VECTOR(4)
);
```

## Vector index

PlanetScale MySQL provides a new `VECTOR INDEX`

to facilitate fast and scalable approximate nearest neighbor (ANN) search on vector data.

Statements that create a vector index may take optional parameters, which can be specified as JSON key-value pairs, via the `SECONDARY_ENGINE_ATTRIBUTE`

variable. There are two options that can be specified in the JSON:

`type`

: specifies the algorithm used to build and query the vector index.- Supported values:
`spann`

(more info on the SPANN algorithm)

- Supported values:
`distance`

specifies the distance metric that queries will use.- Supported values:
`dot`

for the dot product`cosine`

for the cosine of the angle between the two vectors, which is the same as the dot product divided by the magnitude of the two vectors`l2`

or`euclidean`

for the length of a line between the ends of the vectors`l2_squared`

or`euclidean_squared`

for the square of the Euclidean distance. This is the default.

- Supported values:

The distance metric specified at index creation time must match the distance metric used at query time, or the index cannot be used, and MySQL will perform a full-table scan instead.

### Examples

```
CREATE /*vt+ QUERY_TIMEOUT_MS=0 */
VECTOR INDEX embedding_index ON t1(embedding);
```

```
CREATE /*vt+ QUERY_TIMEOUT_MS=0 */
VECTOR INDEX embedding_index ON t1(embedding)
SECONDARY_ENGINE_ATTRIBUTE='{"type":"spann", "distance":"cosine"}';
```

## Vector functions

PlanetScale MySQL includes several new functions for working with vectors.

`TO_VECTOR(string)`

or `STRING_TO_VECTOR(string)`

Converts a text string to a binary vector value. The text string is an array of floating point numbers in JSON format.

### Example

```
SELECT TO_VECTOR('[1, 2.78, 3.14]');
-> 0x0000803F85EB3140C3F54840
```

`FROM_VECTOR(string)`

or `VECTOR_TO_STRING(vector)`

Converts a binary vector to a human-readable string.

### Example

```
SELECT FROM_VECTOR(0x0000803F85EB3140C3F54840);
-> [1.00000e+00,2.78000e+00,3.14000e+00]
```

`VECTOR_DIM(string)`

Calculates the dimension of a vector.

### Example

```
SELECT VECTOR_DIM(TO_VECTOR('[1,2,3]'));
-> 3
```

`DISTANCE(vector1, vector2, [metric])`

Calculates the distance between `vector1`

and `vector2`

. The optional third parameter specifies which distance metric is to be used: `DOT`

, `COSINE`

, `L2`

(`EUCLIDEAN)`

, or `L2_SQUARED`

(`EUCLIDEAN_SQUARED)`

.

`DOT`

means the dot product.`COSINE`

means the cosine of the angle between the two vectors, which is the same as the dot product divided by the magnitude of the two vectors. Example:`L2`

(or`EUCLIDEAN`

) means the length of a line between the ends of the vectors. Example:`L2_SQUARED`

(or`EUCLIDEAN_SQUARED`

) is the square of the Euclidean distance

If the distance metric is omitted, it defaults to `DOT`

.

### Examples

```
SELECT DISTANCE(TO_VECTOR('[1,2]'), TO_VECTOR('[5,4]'), 'DOT');
-> 13
```

```
SELECT DISTANCE(TO_VECTOR('[1,2]'), TO_VECTOR('[5,4]'), 'COSINE');
-> 0.9079593845004517
```

```
SELECT DISTANCE(TO_VECTOR('[1,2]'), TO_VECTOR('[5,4]'), 'L2');
-> 4.47213595499958
```

```
SELECT DISTANCE(TO_VECTOR('[1,2]'), TO_VECTOR('[5,4]'), 'L2_SQUARED');
-> 20
```

```
SELECT id, price, seller_id
FROM products
WHERE price < 20.0
ORDER BY DISTANCE(TO_VECTOR('[1.2, 3.4, 5.6]'), embedding, 'L2_SQUARED')
LIMIT 10;
```

`DISTANCE_DOT(vector1, vector2)`

Is the same as `DISTANCE(vector1, vector2, 'DOT')`

`DISTANCE_COSINE(vector1, vector2)`

Is the same as `DISTANCE(vector1, vector2, 'COSINE')`

`DISTANCE_L2(vector1, vector2)`

Is the same as `DISTANCE(vector1, vector2, 'L2')`

`DISTANCE_EUCLIDEAN(vector1, vector2)`

Is the same as `DISTANCE(vector1, vector2, 'L2')`

`DISTANCE_L2_SQUARED(vector1, vector2)`

Is the same as `DISTANCE(vector1, vector2, 'L2_SQUARED')`

`DISTANCE_EUCLIDEAN_SQUARED(vector1, vector2)`

Is the same as `DISTANCE(vector1, vector2, 'L2_SQUARED')`

## Need help?

Get help from the PlanetScale support team, or join our GitHub discussion board to see how others are using PlanetScale.

**Was this page useful?**