This autosuggest is a single box that searches a database table of about 3.5 million cities (including towns and villages) - the database actually also details region, and country but this isn't important.
Asking AJAX requests to search such a database takes many seconds per request and this is obviously too slow.
My immediate thoughts on how to speed this up as as follows:
Create separate database tables corresponding to each letter of the alphabet, i.e. 26 tables.
My first question is:
If the first letter the use types is 'a', consult the table with places beginning with 'a' from that point on.
In theory this would make the request 26 times faster?
Is the a sensible approach or am I missing something?
Furthermore, if that is still not fast enough,
take the table, for example all places beginning with 'a', and split that into a further 26 tables; i.e.
All places beginning with aa, ab, ac, ad........ az
My second question is:
Is this a sensible and logical approach? Is there a limit to the number of tables on can have in mysql (there would be 26x26 = 676 tables in this case)?
I am aware of indexing tables to speed them up, so the approach above would be 'on top of' indexing the tables.
I look forward to hearing from you.