MySql performance issues

[Rico Lipset]

Greetings php community,

 

    I am working on completing a large travel web application that relies upon a world cities database. The team I am working with is running into performance issues when accessing information from an autocomplete. For instance, if you begin to type in the name of a location the time it takes for any predictive cities to appear is not practicably useful. I am not a database programmer and the one that I am working with is not always completely thorough. Would anyone in the community be willing to look at the applications and offer an suggestions? Hope to hear from you.

 

Rico

[ManiacDan]

It would be best if you added timing code to the application, so that you could narrow down the problem to a few dozen lines of PHP or a single database query, then post just that small snippet to the board.  Not many people are going to be willing to eyeball your code to look for potential slow spots.

[gristoi]

Are you responsible for the world cities database? Or is it a remote resource ? If its yours then you need to ensure that the fields you are querying your autocomplete against are correctly indexed and that your search is specific

[kickstart]

Hi

 

In addition to the above points, make sure the city database you are reading is properly indexed.

 

Also code in a small delay to the Ajax calls to get the auto complete values. Ie, when you detect a key press instead of immediately calling the server set up a timer with a short delay to call to a function to call the server, and after the delay check if the value of the field has changed since the timer was set up. If it has then abandon that call (as the change will have triggered the next call anyway). A 1/4 second delay won't affect peoples perception of the response time but will massively reduce the number of hits on the database from someone typing a city name.

 

All the best

 

Keith