In Mysql, Find Strings With A Given Prefix

In MySql, I want to locate records where the string value in one of the columns begins with (or is the same as) a query string. The column is indexed with the appropriate collation

Solution 1:

Surprisingly, a LIKE query will use an index just fine if you're doing a prefix search.

SELECT*from TableName Where ColumnName LIKE'o hai%'

will indeed use an index since it does not begin with a wildcard character.

This (and other behavior) is documented in the "How MySQL uses Indexes" doc: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

You will need to escape the '%' character and follow normal quoting rules, but other than that any utf-8 input prefix ought to work and do the job. Run an EXPLAIN query to make sure, sometimes other reasons can preclude indexes from working such as needing to do an OPTIMIZE TABLE to update index cardinalities (though this can take ages and locks your table)

Solution 2:

Try this:

SELECT*FROM tablename WHERE columname LIKE CONCAT(query, '%');