CODEWITHSUNDEEP
mysql
Gajus
Gajus

Reputation: 73808

Column optimization for LIKE queries

I have a database table with 300 000 records. The most common query done is:

SELECT [..] WHERE `word` LIKE 'userInput%';

At the moment the column type is varchar(50) UNIQUE. I am wondering whether there is a way to optimize the column for this specific query?

Update 2011 11 19 GMT 00:00:

mysql> EXPLAIN SELECT `word_id`, `word` FROM `words` WHERE `word` LIKE 'bar%'
    -> ;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | words | range | word          | word | 152     | NULL |  435 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

Upvotes: 2

Views: 207

Answers (1)

Brian Hoover
Brian Hoover

Reputation: 7991

As long as you are using a "starts" with type wild card search, just a standard index should work fine on word.

It's only when you start getting into a "contains" wild card search that you start having hard issues with indexing.

Of course, attaching an explain plan would help...

Based on the explain plan, it looks like you're doing about as good as you can do. It's indicating that it's using the index properly, and not doing any full table scans or file sorts.

Upvotes: 3

Related Questions