mysql binary like,在MySQL中为BINARY LIKE操作建立索引

I know that varchar_pattern_ops exists in Postgresql for fast, index-based searches in a LIKE query, but is there any similar functionality for MySQL?

I currently have a Django-MySQL setup where I have this query which runs on a non-indexed field and with a BINARY LIKE operation, and it takes over a minute to complete.

My query is a partial search from the beginning of the text - text%.

This is the table structure. The table actually contains over 20 fields, but I've included just the primary key and the field I'm searching on

+---------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------------------------+---------------+------+-----+--

| id | varchar(255) | NO | PRI | NULL | |

| mid | varchar(255) | NO | MUL | NULL | |

And this is the query -

select count(*) from table where mid binary like 'text%';

These are the indexes -

PRIMARY KEY index has cardinality 102820460

mid index has cardinality 756032

解决方案

Do the fact that MySQL indexes the left side of a string.

Then a string column can use the index if the query use wildcard right side :

SELECT * FROM your_table WHERE field LIKE "text%" # can use an index

but remember that for index there is a limit of 767 bytes

From Mysql DOC

A B-tree index can be used for column comparisons in expressions that

use the =, >, >=,

used for LIKE comparisons if the argument to LIKE is a constant string

that does not start with a wildcard character.