Optimizing a MySQL street address table for search

Question

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 address as VARCHAR(255), an event_id VARCHAR column which relates back to the main events table, and a number of other columns which contain information about the event. For example:

id	address	event_id	characteristic_1	characteristic_2	characteristic_3
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.

Before:

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?

Answer 1

Using LIKE for a contains-type of search (one that leads and ends with a wildcard) is non-sargable rendering the index mostly useless.

The goal is to create a national autosuggest search roughly similar to one that you'd find on a large real estate portal website.

FWIW, typically the way these get implemented is with an auto-complete search box.

When the page is loaded, the complete distinct list of addresses are loaded locally (so a non-sargable search doesn't have to happen in the database layer). The auto-complete search box is bound to the localized copy of the addresses with their correlating key values. Then the auto-complete handles filtering down the address list on the client side as the user types. Usually this is after a minimum number of characters are typed and many times only doing a starts-with type of search (though some do offer contains-type), for performance reasons.

Once a single address is selected by the user, the key of that address is then used to quickly load the rest of the data from the database, for just that address.