2

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
Kaii
  • 20,122
  • 3
  • 38
  • 60
Sidharth Samant
  • 714
  • 8
  • 28
  • https://stackoverflow.com/questions/2042269/how-to-speed-up-select-like-queries-in-mysql-on-multiple-columns – ScaisEdge Apr 15 '19 at 08:49
  • Thanks, although my question is a little bit more constrained than that. I've updated the question. My query is just a partial search from the beginning of the test. – Sidharth Samant Apr 15 '19 at 09:03
  • In this case the index should work .. because the initil part can match witn the index .. try add a simple index on the column but remember that for index there is a limit of 767 bytes – ScaisEdge Apr 15 '19 at 09:05
  • The reason might be the cardinality of the values in this index, result in a bad selectivity. You can try `FORCE INDEX`. Also, find an explanation here: https://logicalread.com/mysql-index-cardinality-mc12/ – Kaii Apr 15 '19 at 09:15
  • Also, is the mentioned LIKE search the only condition? please post table structure (including indexes) and your full query. – Kaii Apr 15 '19 at 09:16
  • If the search prefix is always _n_ characters or less (e.g. 4) then create a fixed width index on `mid` like so `CREATE INDEX ix_mid_4 ON mid(4)` – Salman A Apr 15 '19 at 09:28
  • @Kaii - Added the required details – Sidharth Samant Apr 15 '19 at 09:29
  • Next time please use `SHOW CREATE TABLE` so we can see which collations are used and how the indexes are defined. The reason seems to be a mismatch between attribute collation and the type of operation (binary compare). See https://stackoverflow.com/a/4020848/43959 – Kaii Apr 15 '19 at 16:25
  • `SHOW CREATE TABLE` gives this - `CREATE TABLE table (id varchar(255) NOT NULL, mid varchar(255) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8` – Sidharth Samant Apr 15 '19 at 17:17
  • Also, when I do `SHOW TABLE STATUS`, I get `utf8_general_ci` as the collation for that table. – Sidharth Samant Apr 15 '19 at 17:19

1 Answers1

1

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 =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

https://dev.mysql.com/doc/refman/8.0/en/index-btree-hash.html

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • The column IS currently indexed, but it's not used for that query. I did an EXPLAIN for the query and while the `possible_keys` contained that index, the `key` column was `NULL`. – Sidharth Samant Apr 15 '19 at 09:12
  • 1
    I found out that if i remove `binary` from the query and just execute `select count(*) from table where mid like 'text%';`, it uses the index and becomes insanely fast. – Sidharth Samant Apr 15 '19 at 10:35
  • @Kaii - I understand. As far as I can tell, using `binary` returns a case-sensitive search. But the field contains only upper case values. So that's not a problem. – Sidharth Samant Apr 15 '19 at 12:20