Optimization w/ MySQL and PHP. Need fast menus

oomagnum00

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.

Objective
to create a AJAX Drop-Down menu like the one located at http://finance.google.com

The problem:
to create a very fast AJAX drop-down menu querying a database with large amounts of data. The drop-down menu is very slow the way it is, but it works.

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 FROM table2 WHERE county LIKE ('$q%')ORDER BY xxx GROUP BY xxx; ;
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.

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 FROM search_items LIMIT 3, 10) as X ORDER BY x.yyy GROUP BY x.yyy;

This really didn't do much, but now taxes my MySQL some more.

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
ALSO NOTE: That the all the queired upon fields are INDEXED.

sneakyimp

Not sure exactly how google does it but they are loading some pretty complicated javascript that makes reference to the input field. i would wager that they are loading the data into javascript variables and doing everything client-side.

I would imagine that using more queries will only slow things down. Also, I would imagine that the http protocol is going to be pretty slow for this kind of thing. You might want to think about splitting your data into multiple tables and using a hashing scheme to find the right table. Another possibility might be opening a socket from javascript to your server (i don't even know if that is possible).

ClarkF1

Do you really need all the information???

If not instead of * use the column names relating to the information you need nothing more. There's no need to get all the information if for example you're only getting a company name

oomagnum00

sneakyimp wrote:
Not sure exactly how google does it but they are loading some pretty complicated javascript that makes reference to the input field. i would wager that they are loading the data into javascript variables and doing everything client-side.

I would imagine that using more queries will only slow things down. Also, I would imagine that the http protocol is going to be pretty slow for this kind of thing. You might want to think about splitting your data into multiple tables and using a hashing scheme to find the right table. Another possibility might be opening a socket from javascript to your server (i don't even know if that is possible).

Thanks for the suggestions. I'm going to load mostly client-side. Secondly, I like your suggestion about breaking apart the tables. I'll simply break them into 26 tables, each representing it's own start letter.

If not instead of * use the column names relating to the information you need nothing more. There's no need to get all the information if for example you're only getting a company name

I'm fairly confident that the use of * or a specific field will not hinder proformance at any level other than memory. Database optimizers (time) are only really concerned with operators such as LIKE ('%something%') ORDER BY... WHERE, stuff like that. This is where the time sink is happening.

QUESTION:

So it is possible to thread using JavaScript? Anybody know of a good tutorial to do this? I'm thinking I can return a static list until I receive 2 letters, then by the time I get the 2nd letter I can thread the results for the 3 through N letters.

sneakyimp

are you sure you'll need to thread? I'd wager that just getting all of the info on the user's computer and defined in javascript arrays would be sufficient. you can put an onChange event in the input that rips through one or more arrays using a pattern matching filter.

something like

<HTML>
<body>
<script language="Javascript">
 var states = new Array();
 // get the following from an SQL query
 states[1] = 'Alabama';
 states[2] = 'Alaska';
 states[3] = 'Arizona';
 states[4] = 'Arkansas';
 states[5] = 'California';
 states[6] = 'Colorado';
 states[7] = 'Connecticut';
 states[8] = 'Delaware';

 states[9] = 'Florida';
 states[10] = 'Georgia';
 states[11] = 'Hawaii';
 states[12] = 'Idaho';
 states[13] = 'Illinois';
 states[14] = 'Indiana';
 states[15] = 'Iowa';
 states[16] = 'Kansas';
 states[17] = 'Kentucky';

 states[18] = 'Louisiana';
 states[19] = 'Maine';
 states[20] = 'Maryland';
 states[21] = 'Massachusetts';
 states[22] = 'Michigan';
 states[23] = 'Minnesota';
 states[24] = 'Mississippi';
 states[25] = 'Missouri';
 states[26] = 'Montana';

 states[27] = 'Nebraska';
 states[28] = 'Nevada';
 states[29] = 'New Hampshire';
 states[30] = 'New Jersey';
 states[31] = 'New Mexico';
 states[32] = 'New York';
 states[33] = 'North Carolina';
 states[34] = 'North Dakota';
 states[35] = 'Ohio';

 states[36] = 'Oklahoma';
 states[37] = 'Oregon';
 states[38] = 'Pennsylvania';
 states[39] = 'Rhode Island';
 states[40] = 'South Carolina';
 states[41] = 'South Dakota';
 states[42] = 'Tennessee';
 states[43] = 'Texas';
 states[44] = 'Utah';

 states[45] = 'Vermont';
 states[46] = 'Virginia';
 states[47] = 'Washington';
 states[48] = 'West Virginia';
 states[49] = 'Wisconsin';
 states[50] = 'Wyoming';

 function getAutofill(inputText) {
   var regEx = new RegExp('^' + inputText + '.*');
   var result = '';
   for(i=0; i<states.length; i++) {
      if (regEx.test(states[i])) {
        result = result + states[i] + '<br>';
      }
   }
   var e = document.getElementById('autofill');
   autofill.innerHTML = result;
 } // getAutofill
</script>
<form>
 <input type="text" name="input" onKeyUp="return getAutofill(this.value);">
 <div id="autofill" style="background-color:#ddddff">&nbsp;</div>
</form>
</body>
</HTML>