Multi-dimensional Range Queries in MySQL

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →

This guide requires VillageSQL 0.0.4 or later.

MySQL has no built-in way to represent a point or region in n-dimensional space. You can chain AND conditions across multiple numeric columns, but that only handles containment — it doesn’t extend to distance queries, and it doesn’t let you treat a row’s attributes as a unified geometric value. VillageSQL’s vsql_cube extension adds a cube custom type that makes both kinds of queries natural.

The Problem

Say you have a product catalog where each product has a price, an average rating, and a review count. A buyer wants products priced

50–

200, rated at least 3.5, with at least 50 reviews. That’s a three-way AND:

-- Works for containment, but doesn't generalize
SELECT name FROM products
WHERE price BETWEEN 50 AND 200
  AND rating BETWEEN 3.5 AND 5.0
  AND reviews BETWEEN 50 AND 1000;

The AND chain finds matches. But if you want the 5 products most similar to a given one — closest in price, rating, and review count simultaneously — there’s no natural SQL answer. You’d have to pull candidates into application code and compute distances there.

With VillageSQL: the cube type

INSTALL EXTENSION vsql_cube;

The cube type stores a point or box in n-dimensional space. A point is a location; a box is a region defined by two corner coordinates. Both live in a regular column.

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    attrs `cube`(3)
);

cube is a MySQL reserved word, so backtick it in DDL. The dimension parameter (3) is required — bare cube without a number isn’t supported.

Inserting data

cube_point_nd() builds a point from a comma-separated string of coordinates:

INSERT INTO products (name, attrs) VALUES
    ('Budget Widget',   cube_point_nd('29.99,4.1,320')),
    ('Standard Widget', cube_point_nd('79.99,4.4,1250')),
    ('Premium Widget',  cube_point_nd('149.99,4.7,890')),
    ('Discount Widget', cube_point_nd('19.99,3.2,45')),
    ('Pro Widget',      cube_point_nd('199.99,4.8,2100'));

The coordinates here are (price, rating, review_count) — one point per product.

Range queries with cube_contains

Define your search region as a box. cube_box_nd() takes two comma-separated strings: the lower corner and the upper corner.

-- Products: $50–$200, rating 3.5–5.0, 50–2000 reviews
SET @region = cube_box_nd('50.0,3.5,50', '200.0,5.0,2000');

SELECT name FROM products
WHERE cube_contains(@region, attrs);

cube_contains(@region, attrs) returns 1 when the product’s point falls inside the box — equivalent to all three AND conditions at once, but also composable with distance queries.

Nearest-neighbor with cube_distance

To find the most similar products to a given one, measure Euclidean distance in attribute space:

-- 3 products most similar to Standard Widget (79.99, 4.4, 1250)
SET @target = cube_point_nd('79.99,4.4,1250');

SELECT name,
       ROUND(cube_distance(attrs, @target), 2) AS distance
FROM products
WHERE name != 'Standard Widget'
ORDER BY cube_distance(attrs, @target)
LIMIT 3;

A note on scaling: price is in the hundreds, rating is 0–5, and review count can be in the thousands. Raw Euclidean distance will be dominated by review count. Normalize all three dimensions to a common scale (e.g., 0–1) before inserting if you want balanced similarity scoring.

Checking overlap

cube_overlaps() returns 1 if two boxes share any space — useful for checking whether a product qualifies for any of several promotion bands:

SET @promo_a = cube_box_nd('0.0,4.5,500', '100.0,5.0,9999');
SET @promo_b = cube_box_nd('100.0,4.0,100', '300.0,5.0,9999');

SELECT name FROM products
WHERE cube_overlaps(@promo_a, attrs) OR cube_overlaps(@promo_b, attrs);

Troubleshooting

Error	Fix
`ERROR: Function 'cube_point_nd' does not exist`	Run `INSTALL EXTENSION vsql_cube`
Column type `cube` not recognized	Confirm you are running VillageSQL 0.0.4 or later
Column declaration rejected	Backtick `cube` in DDL: `cube`(N)
Distance results look wrong	Normalize coordinates — raw Euclidean distance is skewed by the largest-scale dimension

Getting Started

Server Development

Extensions

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

The Problem

With VillageSQL: the cube type

Inserting data

Range queries with cube_contains

Nearest-neighbor with cube_distance

Checking overlap

Troubleshooting

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​The Problem

​With VillageSQL: the cube type

​Inserting data

​Range queries with cube_contains

​Nearest-neighbor with cube_distance

​Checking overlap

​Troubleshooting

​See also

The Problem

With VillageSQL: the cube type

Inserting data

Range queries with cube_contains

Nearest-neighbor with cube_distance

Checking overlap

Troubleshooting

See also