CODEWITHSUNDEEP
mysqloptimizationindexingmariadbdatabase-indexes
Ommadawn
Ommadawn

Reputation: 2730

Creating good indexes for table

I'm working with a MariaDB (MySQL) table which contains information about some map points (latitude and longitude) and a quantity.

I'm making a lot of querys that retrieve some of this points and I want to optimize the query using indexes. I don't know how to do it well.

My queries are like this:

SELECT p.id, p.lat, p.lon, p.quantity 
FROM Points p 
WHERE ((p.lat BETWEEN -10.0 AND 50.5) AND 
       (p.lon BETWEEN -30.1 AND 20.2) AND 
       (100 <= p.quantity AND 2000 >= p.quantity)) 
ORDER BY p.name DESC;

So, the columns involved in the queries are: lat, lon and quantity.

Could anyone help me?

Upvotes: 0

Views: 50

Answers (3)

Rick James
Rick James

Reputation: 142298

Toss indexes you have, and add these:

INDEX(lat, lon),
INDEX(lon, lat),
INDEX(quantity)

Some discussion is provided here

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562348

When you have multiple range conditions, even if you have an standard B-tree index on all the columns, you can only get an index to optimize the first range condition.

WHERE ((p.lat BETWEEN -10.0 AND 50.5)              -- index on `lat` helps
   AND (p.lon BETWEEN -30.1 AND 20.2)              -- no help from index
   AND (100 <= p.quantity AND 2000 >= p.quantity)) -- no help from index

You can either index lat or you can index lon or you can index quantity but your query will only be able to use an B-tree index to optimize one of these conditions.

This is why the answer from @achraflakhdhar is wrong, and it's why the answer from @duskwuff suggested using a spatial index.

A spatial index is different from a B-tree index. A spatial index is designed to help exactly this sort of case, where you need range conditions in two dimensions.

Sorry this sounds like it will cause some rework for your project, but if you want it to be optimized, that's what you will have to do.

Upvotes: 1

user149341
user149341

Reputation:

What you want here is a spatial index. You will need to alter the schema of your table (by turning lat and lon into a single POINT or GEOMETRY value) to support this, and use specific functions to query that value. Once you've done this, you can create a spatial index using CREATE SPATIAL INDEX; this index will allow you to perform a variety of highly optimized queries against the value.

There's more information on using spatial types in MySQL in the "Spatial Data Types" section of the MySQL manual.

Upvotes: 2

Related Questions