MySQL: searching rows with TEXT field beginning with a query

Question

I have a table and one of the column is TEXT type. I need to search the table for the rows with the text similar to the given string.

As the string can be pretty long (let's say 10000 bytes) I decided that it will be enough to compare only first 20 bytes of the string. To do this search faster I created a key:

KEY `description` (`description`(20))

So what I want to do now is one of the following query:

SELECT * FROM `table` WHERE STRCMP(SUBSTRING(`description`,0,20),'string_to_compare') = 0

or

SELECT * FROM `table` WHERE `description` LIKE 'string_to_compare%')

Note that I put only one percentage sign at the end of string_to_compare for saying to DB that I want to compare only first bytes.

I hope that MySQL brains will do the best to use key and not to do any extra moves.

Questions:

1. Is there any difference which query is better? I'm personally prefer the second as it looks clearer and hopefully will be better understand by the DB engine (MyISAM).
2. Is that correct the MySQL MyISAM will make an efficient code for these queries?
3. How do I put '%' in the PDO's prepare statement? SELECT * FROM table WHERE description LIKE ":text%"?

Answer 1

1. Yes, there's a difference. When the WHERE condition calls a function on the column value, indexes can't be used. I don't think it will realize that your SUBSTRING() call happens to match the indexed part of the text and use that. On the other hand, LIKE is specifically coded to recognize the cases where it can use an index. Also, if you want to compare two strings for equality, you should use =, not STRCMP(), e.g.

   WHERE SUBSTRING(`description`,1,20) = 'string_to_compare'
   

2. I believe it will make an efficient query for the LIKE version.

3. The placeholder can't be in quotes for it to work. Use CONCAT() to combine it: WHERE description LIKE CONCAT(:text, '%'). Or you can put the % at the end of the PHP variable that you bind to the placeholder, and use WHERE description LIKE :text.