Search code examples
mysqlindexingb-tree

How do I implement B TREE algorithm on two columns in MYSQL

I am about to make a very large table of locations on a MYSQL database. The first column will be the x value or horizontal value of the location, the second column will be the y value or vertical value of the location. (Like longitude and latitude but with unsigned integers).

  CREATE TABLE IF NOT EXISTS `locations` (
    `horizontal_position` INT NOT NULL,
    `vertical_position`   INT NOT NULL,
    /*other columns*/
    )

Both the x value and the y value are equally important so for a moment I thought about needing a double primary key but I don't even know if that is possible. I think it would be best to use the B TREE algorithm for indexing but have no idea how I can make it use both the x and the y column. I could circumvent this by making only one colum which contains both the x and the y value in a bigint but that seems like the kind of solution databasepeople would frown upon.

So how do I create a B TREE index on both the columns and what is my primary key?

Thanks in advance

Solution

  • I think you want a spatial index. These are documented here.

    There are index types used for multi-dimensional indexes. The best known is the R-D tree, which is commonly associated with spatial indexes. These are generally views as extensions on base SQL, which is unfortunate, because multi-dimensional data is rather common.