MySQL queries being very slow

[foy]

Heya,

I have a mysql-table (~ 100.000 rows) which I'm doing a mysql_query on. looks like this:

PHP:
--------------------------------------------------------------------------------
<?php 
$text_string = "test"; 
$data = mysql_query("SELECT test_field FROM test_table WHERE test_field LIKE '%$text_string%' ORDER BY test_field ASC"); 
while ($row = mysql_fetch_object($data)) { 
 echo $row->test_field."<br>"; 
} 
?> 
--------------------------------------------------------------------------------

It takes around 2-3 seconds before I get any return to the browser...

any ideas on how I could speed up the query?

[hakre]

try to add a FULLTEXT index on column test_field. this should speed up the LIKE operation a bit.

if you get many rows back, try to LIMIT them (ie returning only the first 20 rows: LIMIT 0,20)

[coopster]

Reference that may help as well ...

Speed of SELECT Queries [dev.mysql.com]