0

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?

3
  • Sanitizing the input is something to consider. But is "St" an abbreviation for "Street" or "Saint".
    – Rick James
    Commented Jun 30, 2023 at 15:00
  • @RickJames Sanitization/parsing/pre-processing is something I could try as long as the underlying search method is performant (what I'm trying to determine first). The address data is sometimes ambiguous. Whichever method I use should account for ambiguities in both the user input and the data in the database.
    – Tyler
    Commented Jun 30, 2023 at 20:07
  • Fulltext is far more performant than LIKE with an initial wildcard. But there are limitations.
    – Rick James
    Commented Jun 30, 2023 at 22:18

1 Answer 1

0

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.

2
  • @JD In practical terms, I understand how I would export all the distinct addresses from the table but I don't follow about creating a localized copy. Would this be like a single JSON file that the client downloads and searches locally on the front-end? With millions of unique addresses, it seems like this file would be too large to be practical.
    – Tyler
    Commented Jun 30, 2023 at 20:20
  • @Tyler So it depends on what kind of application you're creating to determine how to actually store the addresses. With an average address length of 50 characters for 1 million addresses, it would be about 50 MB of data, which is decent sized. With modern hardware, to load it off disk and across a network, you're probably looking at a few hundred milliseconds to load that data each time. In a low user, desktop application, that's probably negligible to do every time the page is loaded. But for a webpage where it may be heavier on the browser, a caching technology like Redis might make sense.
    – J.D.
    Commented Jun 30, 2023 at 20:55

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.