3

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.

Improve this question
7
  • That's curious. Please provide EXPLAIN SELECT ... for the slow query. Commented Apr 22, 2015 at 21:37
  • 1
    well its only going to use one of the two indexes for the query, surely. Commented Apr 22, 2015 at 23:06
  • select_type=SIMPLE, type=fulltext, possible_keys=<several>, key=search_fulltext, extra=Using where; Using filesort Commented Apr 22, 2015 at 23:57
  • @pala_ shouldn't it effectively do the same thing as an inner join? Check fulltext index for a list of matching fulltext rows, check lat/lon index for a list of matching index rows, return the rows that are in both lists. Commented Apr 23, 2015 at 1:08
  • index merge doesnt apply to fulltext, see : dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html Commented Apr 23, 2015 at 1:18

2 Answers 2

Reset to default
1

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

It's an Amazon RDS instance with 7.5 gb of RAM and the entire database is small enough to be held in memory--there are only 1 or 2 reads from disk per hour. innodb_buffer_pool_size is set to 5.7 gb. The lat lon lookup is to narrow the results for calculating a haversine equation for distance, so instead of calculating the haversine equation for 500k rows, it will just do the 100 or so that are within our lat/lon window. The lat/lon window is square whereas our final results are a circle (search radius).
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.