Mysql – Is MySQL smart enough to not do a full table scan for LIKE or NOT LIKE when there are other conditions

MySQL

I know that full wildcard searches (example: LIKE '%word%') cause a full table scan.

Is MySQL smart enough to only scan the results, not the entire table, when I use LIKE or NOT LIKE?

For example, say I have a query that selects 10 rows out of 1000000. The query is something like select * from table where id <= 10 and description NOT LIKE '%word%'. Is MySQL smart enough to only scan those 10 result rows?

(My query is very different but that's the gist of it: I want to use NOT LIKE on a result set from a large table. Assume everything is indexed. Client version "14.14 Distrib 5.7.29, for Linux (x86_64)", server version "5.7.29-0ubuntu0.16.04.1 for Linux on x86_64".)

Best Answer

Given NOT LIKE '%...' cannot use an index and id can, provided a index (include primary key) begins with id, then it will be used.

EXPLAIN [EXTENDED] {query} will show what indexes the query planner plans to use for the query.

Related Question