vsql_trgm extension for MySQL

vsql_trgm is a port of PostgreSQL’s pg_trgm. It breaks a string into three-character pieces and scores two strings by how many pieces they share, so a misspelled search term still finds its row. Use it when LIKE is too strict and full-text search is too coarse.


Maintainer	VillageSQL
Source and full documentation	villagesql/vsql-trgm
License	GPL-2.0

Install

vsql_trgm.veb is already in the server’s lib/veb/ directory if you installed VillageSQL with the install script, the Docker image, or a release tarball. Install it into the server with one statement:

INSTALL EXTENSION vsql_trgm;

Confirm it is there:

SELECT EXTENSION_NAME
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'vsql_trgm';

+----------------+
| EXTENSION_NAME |
+----------------+
| vsql_trgm      |
+----------------+

To build it yourself, follow the build instructions in the repository.

What it adds

It adds three families of function. The plain family compares two whole strings. The word family finds the best-matching substring of the second string. The strict word family does the same but aligns on word boundaries. Each family has its own default threshold.

Function	Returns	What it does
`trgm_show(s)`	text	The trigrams of a string, as a JSON array
`trgm_similarity(a, b)`	real	The share of trigrams the two strings have in common, 0 to 1
`trgm_distance(a, b)`	real	1 minus the similarity
`trgm_similar(a, b)`	int	1 when the similarity reaches 0.3
`trgm_similar_threshold(a, b, t)`	int	1 when it reaches a threshold you choose, between 0 and 1
`trgm_word_similarity(a, b)`	real	The best similarity between `a` and any substring of `b`
`trgm_word_distance(a, b)`	real	1 minus that
`trgm_word_similar(a, b)`	int	1 when the word similarity reaches 0.6
`trgm_strict_word_similarity(a, b)`	real	The same as the word form, aligned on word boundaries
`trgm_strict_word_distance(a, b)`	real	1 minus that
`trgm_strict_word_similar(a, b)`	int	1 when the strict word similarity reaches 0.5

Example

Build a small name table:

CREATE TABLE people (id INT PRIMARY KEY, name VARCHAR(32));

INSERT INTO people VALUES
    (1, 'Jonathan Smith'),
    (2, 'John Smyth'),
    (3, 'Joan Smithe'),
    (4, 'Peter Brown');

Search it for a spelling nobody typed, and rank what comes back:

SELECT name, ROUND(trgm_similarity(name, 'Jon Smith'), 3) AS score
FROM people
WHERE trgm_similar(name, 'Jon Smith') = 1
ORDER BY score DESC;

+----------------+-------+
| name           | score |
+----------------+-------+
| Jonathan Smith |   0.6 |
| Joan Smithe    | 0.583 |
| John Smyth     | 0.455 |
+----------------+-------+

Peter Brown shares too few trigrams to reach 0.3, so it never appears. To see what is being compared, ask for the trigrams themselves:

SELECT trgm_show('Smith') AS trigrams;

+---------------------------------------+
| trigrams                              |
+---------------------------------------+
| ["  s"," sm","ith","mit","smi","th "] |
+---------------------------------------+

The padding spaces are deliberate. They let the start and end of a string count toward the score.

PostgreSQL’s set_limit() has no counterpart here, because a VEF function keeps no session state. Pass the threshold you want to trgm_similar_threshold() instead. A similarity filter also reads every row, so measure it before running one against a large table.

Getting Started

Server Development

Using Extensions

Creating Extensions

Install

What it adds

Example

See also

​Install

​What it adds

​Example

​See also

Install

What it adds

Example

See also