mysql LIKE where clause with parameter doesn't use index

Question

In my testing, mysql select statements with a where clause that contains a LIKE that compares to a parameter won't use an index. A full table scan is done and performance suffers. e.g.

set @gp1:= 'BOB%';
select * from quote where quoteNum like @gp1; -- this is slow

If the value is inlined the index is used. e.g.

select * from quote  where quoteNum like 'BOB%'; -- this is fast

Is there a way to force mysql to use an index in the first example?

Answer 1

Character set and collation of the variable must be the same as the column for the query to work.

SET character_set_connection = latin1;
SET collation_connection = latin1_swedish_ci;
set @gp1:= 'BOB%';
select * from quote where quoteNum like @gp1; -- this is fast now

This answer is dealing with a similar problem. https://stackoverflow.com/a/15032843/176868

Answer 2

Following forces index...

SELECT * FROM employee USE INDEX (emp_name_index)
  WHERE emp_name like 'white%';