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.