MySQL full text index and regular index lookup
I have 3 columns of data that I am looking for: description (full text), lat (index) and lon (index).
When I do SELECT id FROM table MATCH(description) AGAINST ('query' IN BOOLEAN MODE)
, everything works quickly and efficiently.
When I do SELECT id FROM table WHERE lat BETWEEN a and b AND lon BETWEEN x and y
, everything is fast and works great.
When I combine the two where clauses along with a simple AND and do SELECT id FROM table MATCH(description) AGAINST ('query' IN BOOLEAN MODE) AND (lat BETWEEN a and b AND lon BETWEEN x and y)
, everything works fine, but it takes a few seconds to process.
The first two requests will take 0.1 seconds and the last one will take 3+ seconds and I can't figure out how to make it run faster. Description is full-text indexes and lat / lon columns are normal indexes.
Any ideas on what is slowing things down and / or how to fix it? The table is InnoDB.
source to share
The reason for the slowdown ... Notice how each of the first two SELECTs
returns id
. It's cheap because the ID is included in any secondary index.
But when you have two parts for WHERE
, one index ( FULLTEXT
) is used to get the id , then it searches the string to get the values ( lat
, lng
) needed for the other part WHERE
. This is related to another search for another BTree. If everything you need is in RAM, it's not too bad. But if you need to get to the drive ...
Let me check one possible solution ... How much RAM do you have? What is the meaning innodb_buffer_pool_size
? Typically this setting is 70% of the available RAM (assuming you have more than 4GB). Casting it to this value can reduce the I / O required to execute a complex query, thus speeding it up.
If that doesn't work, I have a method that works efficiently with finding lat / lng. I haven't tried it with it yet FULLTEXT
, so it may have unexpected quirks. However, it works very efficiently for finding lat / lng (better than you can get with just normal INDEX
).
source to share
This question contains a solution: MySQL Index for Column with Regular Column and Full Text
Essentially, you need to run two queries with UNION
.
source to share