Bug #76631 Partial index support

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.

Hello Daniël,

Thank you for the feature request.

Thanks,
Umesh

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0

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

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.