Bug #99522 | Support column prefix index of size zero (NULL index) | ||
---|---|---|---|
Submitted: | 12 May 2020 7:26 | Modified: | 13 May 2020 7:21 |
Reporter: | Vladimir Lasky | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S4 (Feature request) |
Version: | 8.0.20 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | INDEX |
[12 May 2020 7:26]
Vladimir Lasky
[12 May 2020 9:28]
MySQL Verification Team
One can index generated columns to do this. Check if it helps? -- ------------ -- drop table if exists t; create table t(a varchar(60))engine=innodb; insert into t select uuid(); insert into t select uuid() from t; insert into t select uuid() from t a,t; insert into t select uuid() from t a,t; insert into t select uuid() from t a,t; insert into t select uuid() from t a,t limit 1000000; update t set a=null where a like '%33%'; analyze table t; select count(*), sum(isnull(a)),sum(not isnull(a)) from t \G explain select count(*) from t where isnull(a); flush status; select count(*) from t where isnull(a); show status like 'handler%'; -- vs an indexed generated column alter table t add a_n tinyint generated always as(isnull(a)); alter table t add key n(a_n); analyze table t; explain select count(*) from t where a_n=1; flush status; select count(*) from t where a_n=1; show status like 'handler%'; select version(); -- ------------
[13 May 2020 6:44]
Vladimir Lasky
Hello Shane, Thanks for taking the time to comment. Yes, your approach with the indexed generated column is a partial workaround, but it is not always an option and doesn't provide the same benefits. 1. Generated columns are only supported when using the InnoDB storage engine. Many MySQL users use other storage engines like MyISAM, TokuDB and MyRocks to meet specific needs. My businesses are relying on TokuDB. 2. Queries with WHERE clauses that check for IS NULL or NOT NULL would have to be rewritten to use the ISNULL() function. It is considered good practice to avoid creating WHERE clauses with column names inside MySQL functions as this usually prevents MySQL's optimizer from functioning. Obviously, when you have indexed generated columns populated using function(s) applied to a column, that is an 'exception to the rule', but I would prefer to avoid relying on such exceptions except unless there is no other alternative. The use case I describe is very common. The syntax I propose for the NULL index (a column prefix of size zero) is more intuitive and simpler for most people to deploy. 3. I expect the development effort required to support this to be small.