Question 112974 in Mysql Question posted in
The official MySQL documentation can be found here.

Optimising a MYSQL query that uses LIKE %search% on joined tables

I have a MySQL query that searches through a zipcode database for city names. The query takes about 4 seconds, to search through 620,000 zipcodes. I would like ideas how I can make this faster.

First, the tables:

I have a table of zipcodes. Each zipcode entry has 6 ints, that refer to words. I use joins from the words, to make zipcodes: "92121, San Diego, California, United States, CA, US"

If I know the exact zipcode, the query is quite fast:

Full Question

I made this link to show the table and SELECT statement since StackOverflow appears to have some bugs when posting code.

However, if I do NOT know the zipcode and search by name, the query is really slow, as I must accommodate searching for all permutations of all words: "San Diego, CA", "San Diego, California", "San Diego, CA", "California San Diego", "92121 San Diego CA", etc

In order to accommodate all the permutations, I am doing a LIKE on every term, against every column. This is really slow (4 seconds) and I feel this statement can be made much more efficient.

The database contains all zipcode patterns, for all countries. Maybe I can search for "US", "CA", "UK", or "AU" beforehand, and limit the query to cities within the user"s home country?

Thoughts?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Taking the advice of Akina, where LIKE can't be optimized, I did a CONCAT of all the columns then did a single LIKE, rather than multiple LIKES against each column separately. I used a SELECT inside a SELECT to do this.

    I then removed the GROUP BY, and added LIMIT 1, so that the database could bail on finding the first zipcode rather than scanning the entire database.

    Finding a zipcode, on a 650k worldwide database, went from 3.5 seconds to 0.025 seconds.


  2. 0

    Try this: Put all 7 words into a string; index it with FULLTEXT, have innodb_ft_min_token_size set to 2.

    Now you can do

    MATCH(str) AGAINST('+diego' IN BOOLEAN MODE)
MATCH(str) AGAINST('+921*' IN BOOLEAN MODE)

    and lots of other things. All will be much faster than 3.5s.

    Read about Fulltext; there are some limitations.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search