Sample Header Ad - 728x90

Optimizing a MySQL street address table for search

0 votes
1 answer
438 views
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?
Asked by Tyler (111 rep)
Jun 30, 2023, 01:25 PM
Last activity: Jun 15, 2025, 10:03 AM