Thank you.
I’ve attempted to be as descriptive as I possibly can in my objective to clearly communicate what I am attempting to accomplish. I hope that I can get some valuable feedback to help solve this problem; as it is more of a matter of efficiency than functionality. The main intent of this thread is to seek advice on OPTIMIZATION. Although this mostly has database calls, it belongs in the PHP section because I’m hoping I can optimize the PHP code; or if I have to, break apart the database.
[U][B]Objective
to create a AJAX Drop-Down menu like the one located at [url]http://finance.google.com
[U][B]The problem:
to create a [I][U]very fast
[B]Details
I have over 100,000 entries in my database that my parser will pick up. My simple query takes a very long time to execute; I’m running it on my local machine and it yields a 2-4 second delay.
I have a list of cities, counties, and zip-codes I retrieved from the USPS database (this is accessible for anyone who registered as a developer with them).
I run a simple query on each one of my three tables
SELECT * FROM table WHERE city LIKE (‘$q’) ORDER BY xxx GROUP BY xxx;
SELECT
SELECT * FROM table3 WHERE zip LIKE (‘$q’) ORDER BY xxx GROUP BY xxx;;
I echo the results back to my menu using an AJAX call after all items were iterated through.
[U][B]Solutions I’ve attempted
1. Limited the amount result set to 8 – IMPROVED MAY 0.2 SECONDS
2. Wrote a static return function to retreive a list of static cities when a certain sequence of letters/numbers are entered. Only after a person has entered in 3 letters I do the query. – IMPROVED 0.5 SECONDS
3. I ordered my database in alphabetical order. I created a separate table to index the start and stop values of all permuations of 2 letter sequences. For instance, if a user would type in “LO” that table will return a range of 2 integers, let’s say 3, and 10. Then I do a double query
SELECT * FROM (SELECT
This really didn’t do much, but now taxes my MySQL some more.
[I][B]i need your help!
I am looking for more solutions. Basically, how does GOOGLE Finance do it so fast? Can anybody help me come up with a fast way to do it to get it as fast as googles?
Using:
PHP 4.3x
MySQL 4.x