I'm developing an app that has a MySQL table,
addresses, which stores events that happen at a given US address. I receive this data from a third-party in large chunks at set intervals. The table has an
id primary key, an
VARCHAR column which relates back to the main
events table, and a number of other columns which contain information about the event. For example:
|1||123 Main St, Nowhere, KS 66002||9001||foo||bar||baz|
|2||1600 Pennsylvania Avenue NW, Washington, DC 20500||6B00||blib||blob||bloo|
|3||124 Conch Street, Bikini Bottom, Marshall Islands 96970||374A||me||hoy||minoy|
The table has 250 million rows with these types of addresses and grows each year. Data is never deleted. The table is read-only in production.
The goal is to create a national autosuggest search roughly similar to one that you'd find on a large real estate portal website.
I found that using a
LIKE query with a B-Tree index on the
address column is too slow in some cases, sometimes taking up to 20 seconds to run a basic search, so I'm now in the process of creating a
FULLTEXT index which is probably going to take a day or two to finish.
SELECT * FROM `addresses` WHERE `address` LIKE '%main st%' LIMIT 10;
What I imagine the query will look like after:
SELECT * FROM `addresses` WHERE MATCH(`address`) AGAINST('main st' IN NATURAL LANGUAGE MODE) LIMIT 10;
Due to the large table size (250 million rows and growing), is using a
FULLTEXT index the best way to go, should I be doing something to optimize it based on my use case, or should I jump ship entirely and use another system like Meilisearch for autosuggest?