Bug #76631 Partial index support
Submitted: 9 Apr 2015 7:29 Modified: 16 Apr 2015 8:32
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[9 Apr 2015 7:29] Daniël van Eeden 
Description:
This is a feature request for partial indexes:
CREATE INDEX foo ON t1(c1) WHERE c2 = 1

This is somewhat similar to Bug #60615

How to repeat:
Try to create a partial index in MySQL

Suggested fix:
Add support for partial indexes.
[16 Apr 2015 8:32] MySQL Verification Team 
Hello Daniël,

Thank you for the feature request.

Thanks,
Umesh
[18 Jun 2016 21:26] Omer Barnir 
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0
[26 Aug 2019 11:05] Michal Vorisek 
Feature request that needs to implemented with the high priority.

It can save a lot of resources (data/cache/CPU) and improve dev time/experience for common use cases.

Implemented all other major databases:
- Microsoft SQL - fully supported, https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes
- Postgres - fully supported, https://www.postgresql.org/docs/current/indexes-partial.html
- Oracle - supported by using function based index, as NULLs are not indexed in Oracle DB, https://dba-presents.com/index.php/databases/oracle/41-filtered-index-equivalent-in-oracle
[24 Feb 15:21] Nuno P 
Example:
90% of a table might sometimes have a "city_id = 1", but I want to index the other 10% of that table that has other city_id values.

–

I've just read a comment I wrote back in 2019, saying:
"But yes, VIRTUAL/PERSISTENT is perfect for the condition I was mentioning of "1 active entry per group", etc..."

The problem with VIRTUAL/PERSISTENT is that it totally breaks ONLINE ALTER TABLE for that table, which is a big problem, so I'm actually avoiding using this in the future, and migrating away from using it completely.