McGruff

Can you post the script you are using to insert in to the database?

How much information are you going to be returning on each query? For instance, if someone clicks on the letter A, are they just going to see a list of company names beggining with A, or are they going to see the company name, a paragraph about the company, location of the company, # of employees... If you are just returning a name, or something short, I wouldn\'t bother with the text file. MySQL can return queries like that very fast, even if you have several thousand companies per letter. True, accessing static test files will be faster, but unless you have a ton of info per company, in my opinion, it isn\'t worth the extra effort on your part.

I\'ve tried changing around the order in both the FROM list and the WHERE list, but that doesn\'t seem to have any effect. If I do the LIKE version , here are the explained results: table type possible_keys key key_len ref rows Extra c All Primary NULL NULL NULL 95264 temp, filesort o ref Primary c_id c_id 4 c.c_id 1 where used p ref o_id o_id 4 o.oid 1 using index If I do the = version: table type possible_keys key key_len ref rows Extra o const Primary,c_id Primary 4 const 1 c const Primary Primary 4 const 1 p const o_id o_id 4 const 1 I hope that is somewhat legible. It is pretty easy to see why the second version is faster. MySQL knows that the second version will return only 1 row from each table, so it grabs the results fast. In the 1st version, MySQL doesn\'t know how many rows it is going to return, so it opts to do a full scan of the table it thinks will be the smallest, which is the customers © table. I believe MySQL is choosing the wrong table. If it does a scan on the orders table, filtering out orders that do not match the LIKE results, there will be (in most cases) a lot less rows to scan through. My problem is I don\'t know how to let MySQL know this.

It would depend on how much people were entering for their descriptions, and how many users you had. This is from the mysql website: They don\'t seem to specify how large is \"large\", but I don\'t think a few hundred characters would be large, unless you have a large number of members.

I\'m having trouble understanding how MySQL optimizes SELECT statements. Say I have 3 tables: orders, customers, and products. The primary key for the orders table is order_number, keep that in mind. I want to select all the information about a certain order number. To make things easier on whoever enters the order number, I want to use string expressions to get as many order #\'s that might enter what the user enters. Here is an exaple sql statement: SELECT o.order_number, c.customer_name, p.product_name FROM orders AS o, customers AS c, products AS p WHERE o.order_number LIKE \'%$whatever%\' AND c.customer_id=o.customer_id AND p.product_id=o.product_id the statement above takes ages to execute when customers gets large. Why? because MySQL grabs all of the rows from customers, then matches the orders and products to those rows. This behavior does not happen if I change \"WHERE o.order_number LIKE \'%$whatever%\'\" to \"WHERE o.order_number=\'$whatever\'\" if you make that change, MySQL sees o.order_number is primary, so will only return 1 possible row, and then grabs the results fast. So why does it not do the same when using LIKE? I think MySQL is assuming if it grabs the matching order numbers, it will take longer, but unless someone enters a single digit order number, this will not be the case. Any suggestions/opinions?