View Full Version : Index help for Super-Fast Selects

03-30-2007, 11:20 PM

I'm making an application that auto-suggests city names when someone starts typing them in. Therefore I need to create lightening-fast queries to get cities.

To do this I'll dedicate a table to city names. This will cover all cities in the US & Canada, drawn from postal code databases so while we're not talking about millions of records, its more than a couple of thousand.

My question is about indexes. I'd like to be able to query the DB when the user has entered the first 2 letters of the city name, then maybe again after 3 or 4 letters.

To get the fastest SELECT speed, should I have 2 indexes, one with length 2 and one with length 3 or 4, then do the select with the USE INDEX command?

Or, is there a faster way?

04-02-2007, 12:30 AM
Preload it into an array so you dont have to use AJAX to call the database, just setup a javascript function with arrays you pull from the database to do your compares. I think that will be ton's faster.

04-02-2007, 01:10 AM
Nvm I see you have thousands... There's just no way to make that fast enough to "show up" while a person is typing. Maybe if they paused... But by the time you query once, they typed another letter... query again... and again... Seems like a waste IMO.

Maybe after the first query you can have a static result set it would search through, but if thats not in javascript you would have to make the server side call again through AJAX to get the results and thats more time. I honestly just dont see it.

Maybe if you can filter with state,zip, country or w/e to get the "thousands" down to something more manageable. Have them fill in there zip first, Query city based on that zip, then query from there after the first two letters are typed, then put those in array...

Seems more work than its worth.

04-02-2007, 02:34 PM
Johnny -

Why not alter your thinking a little bit?

Instead of trying to pull up cities based on the first character, wait until the length of your search field gets to 2 or 3.

Then do a 1-time call to AJAX using the first 2-3 characters that they have typed in. That will significantly reduce your server load, increase the speed of your queries, AND not have you force your users to take 2 or 3 extra steps to get to your query.

Just my $0.02...


04-02-2007, 10:33 PM

Thanks for all the feedback. I had in mind something that combines both of your ideas, and actually that's what I've done.

I wait until the user has typed the first 2 letters then make my AJAX call. When the info is returned I see if the user has typed anything new and re-filter, (using Javascript), those results. I don't query the server again unless the user deletes a bunch and enters a new first 2 letters. The reason I went with 2 letters instead of 3 is because there are quite a few cities with short, (4-5 character), names. The highest number of cities I've returned off a 2-letter query so far is something like 1600, which is manageable in Javascript.

It seems fast enough in my first testing. I've added 3 indexes to the city name filed of the database as well: one that only covers the first 2 characters, one that covers the whole column, and a fulltext. I'm not sure the fulltext is really worth it, but I'll experiment.

FYI There are over 66,000 cities in the postal code databases of Canada & the U.S.

04-02-2007, 11:59 PM
a full text index on a column with a single word as an entry will offer you no benefit over the index on the column and in fact will not work with less than four characters in a word. it also matches the entire word only unless you use the query in binary mode.

also note that only a single index is used in any one query so your index on the field is either used or not used, depending on whether or not it uses the partial index.

again, I believe a partial index still needs to have 4 characters to function.

you can run your query separately in mysql and use EXPLAIN at the beginning of your query to see what index is used.

04-03-2007, 05:32 AM
I thought that's how a FULLTEXT worked but I wasn't sure. It may be handy, if I can run it in binary mode, and because some cities have multiple-word names. I'm hoping to be able to use it to come up with suggestions for mis-spellings, (for those people who are too stubborn to accept the AJAX suggestions, or have javascript off). More experimentation is in order.

For the normal indexes - I've played around with EXPLAIN a bit and it does seem that the index on the first 2 characters is used sometimes. I can't figure out the rationale behind when it is used and when an index on the whole column is used, but MySQL seems to choose the faster one, (I tried some USE INDEX queries on the one it didn't choose and got a slower result). The reason I made multiple indexes was to give MySQL options, and it seems to like them, and the FULLTEXT was, as I mentioned before, to try to figure out mis-spellings.