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
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 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
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.
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
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.