MySQL queries being very slow

[commun1]

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

-------------------------------------------------------------------------------- 

<?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?

 

[TonyGroves]

The LIKE string you are using contains a leading "%", which will prevent the use of any index on that field, so if the table is quite big, you could expect that query to take some time. If it was possible for you to eliminate the leading "%", then any index on the test_field column would be used, possibly speeding up the query an enormous amount.

 

[sleipnir214]

Is the column "test_field" indexed?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[azzazzello]

well, I guess a 100,000 rows can merit 2-3 seconds. Try creating an index on your test_field

CREATE INDEX my_cool_index ON test_table(test_field);

then try your query