Why is MySQL ignoring my indexes?

I have a query

select distinct id from patient where company_id in (1) and
(name_last like 'peter%' or name_first like 'peter%' or remote_id like 'peter%')

running on a table that looks like this

CREATE TABLE `patient` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `status` enum('tmp','active') DEFAULT 'tmp',
  `remote_id` varchar(64) DEFAULT NULL,
  `name_last` varchar(64) DEFAULT NULL,
  `name_first` varchar(64) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `mtime` datetime NOT NULL,
  `ctime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_ID_SCHEME_MRN` (`company_id`,`remote_id`),
  KEY `ix_ID_SCHEME_LAST` (`name_last`),
  KEY `ix_ID_SCHEME_FIRST` (`name_first`),
  KEY `ix_ID_COMPANY_ID` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When I run an explain on it, I see that MySQL isn't using any of my indexes.

id: 1
  select_type: SIMPLE
        table: patient
         type: ALL
possible_keys: ix_ID_SCHEME_MRN,ix_ID_SCHEME_LAST,ix_ID_SCHEME_FIRST,ix_ID_COMPANY_ID
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 494
        Extra: Using where

I am surprised that it isn't using ix_ID_COMPANY_ID at least. But, honestly, I would think that it would make use of most of the indexes.

Any advice?

Thanks.

If a significant portion of the rows match company_id in (1), then MySQL will choose to do a table-scan instead of using the index. In my experience, "significant portion" is about 20%.

Think of it this way: in the index at the back of a book, why don't they index words like "the"? Because the index entry would just show a list of every single page number. Looking up each page the word occurs on would be more trouble (flipping back and forth in the book) than it would be to simply read the book cover-to-cover.

That's an extreme example, but there's a break-point where it's more efficient to do a table-scan. For better or worse, MySQL tends to choose this option if the value you're searching for occurs somewhere between 16-20% of the rows.

The other indexes cannot be used because you're using OR.

StackExchange Database Administrators Q#74111, answer score: 3