Does MySQL support partial indexes?

Question

Partial indexes only include a subset of the rows of a table.

I've been able to create partial indexes in Oracle, DB2, PostgreSQL, and SQL Server. For example, in SQL Server I can create the index as:

create index ix1_case on client_case (date) 
  where status = 'pending';

This index is cheap since it does not include all 5 million rows of the table, but only the pending cases, that should not exceed a thousand rows.

How do I do it in MySQL?

MySQL does not support partial indexes. – Gordon Linoff Mar 20 '18 at 23:46 — Gordon Linoff, Mar 20 '18 at 23:46

score 5 · Accepted Answer · answered Apr 18 '18 at 15:58

5

As @Gordon Linoff commented, MySQL does not (yet) support partial indexes.

answered Apr 18 '18 at 15:58

The Impaler

Rick James · Answer 2 · 2019-11-26T19:50:26.930

0

Queries that need the index you suggest may benefit from

INDEX(`status`, `date`)

edited Nov 26 '19 at 19:50

answered Mar 21 '18 at 02:03

Rick James

2 Answers2