Search code examples
mysqlindexingfull-text-searchsql-likefull-text-indexing

mySQL LIKE Query on Full Text

I'm trying to search for city names in a table with a few million rows.

I'm not really trying to do a fulltext search, but something close. For instance searching for "Los Ang" should not just return "Los Angeles", but also "East Los Angeles" - but not "EastLos Angeles" (white space!).

Unfortunately, this would require a query somewhat like this:

SELECT 
    cities.name
FROM
    cities
WHERE
    (cities.name LIKE '% los angel%' OR cities.name LIKE 'los angel%') AND cities.country = 'US'
LIMIT 40

This would return my expected results - but it takes about 6 seconds, which is bad. Removing the LIKE '% los angel%' clause will improve the query to 0.05 seconds or so, but not giving me the full results I am looking for.

Index wise there is a B-TREE Index on column cities.name (varchar 200). Can't do fulltext Index, because it's a innodb table.

Any smart way I could optimize this without converting to myisam?

I do not care so much about writing performance, as it's mostly a read-only table.

Solution

  • No MySQL indexing method will get you "East Los Angeles" but not "EastLos Angeles", plus the many unstated cases: "Dayton" but not "Daytona Beach", "Forest Hill" but not "Forest Hills", "Portland, ME" but not "Portland, OR". Etc.

    You may, however, use a combination of SQL and application code. Searching for "Angeles" using a FULLTEXT index will be very fast, then the application code can deal with what comes around it. Note that "Hill" may match "Hills" in FULLTEXT, so that would be another thing to check. Also, there is a minimum "word length"; if you don't change that, the "Fe" in "Santa Fe Springs" will be ignored. And short names ("Roy", in Utah) could be an issue. Norway has a 1-letter city: "Å".