CODEWITHSUNDEEP
mysqlfull-text-searchinnodbdatabase-indexes
user2247281
user2247281

Reputation: 143

MySQL full text index and regular index searching

I have 3 columns of data I'm searching on: description (fulltext), lat (index), and lon (index).

When I do SELECT id FROM table MATCH(description) AGAINST ('query' IN BOOLEAN MODE) everything processes quickly and works fine.

When I do SELECT id FROM table WHERE lat BETWEEN a and b AND lon BETWEEN x and y everything processes quickly and works fine.

When I merge the two where clauses together 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 several seconds to process.

The first two queries will take 0.1 seconds and the last one will take 3+ seconds and I can't seem to figure out how to make it run quicker. The description is full text indexes and the lat/lon columns are normal indexes.

Any ideas on what is slowing things down and/or how to fix it? The table is InnoDB.

Upvotes: 3

Views: 1117

Answers (2)

Chris Stryczynski
Chris Stryczynski

Reputation: 34001

A solution is provided in the question here: MySQL index for normal column and full text column

Essentially you need to run the two queries with a UNION.

Upvotes: 0

Rick James
Rick James

Reputation: 142433

The reason for the slowdown... Note how each of the first two SELECTs return the id. That is cheap because the id is included in any secondary index.

But when you have two parts to the WHERE, one index (FULLTEXT) is used to get the id, then it looks up the row to get the values (lat, lng) needed for the other part of the WHERE. This involved another lookup on another BTree. If everything necessary is in RAM, that is not too bad. But if you need to hit the disk...

Let's check one possible fix... How much RAM do you have? What is the value of innodb_buffer_pool_size? That setting should normally be 70% of available RAM (assuming you have over 4GB). Raising it to this value may diminish the I/O needed to perform the complex query, thereby speeding it up.

If that does not work, I have a technique that works efficiently with lat/lng searches. I have not yet tried it together with FULLTEXT, so it may have some unexpected quirks. However it does work very efficiently for lat/lng searches (better than you can get with just an ordinary INDEX).

Upvotes: 1

Related Questions