hi guys, could anyone help me developing this MDEV (mariadb, maybe usefull for twitter too, since it's a nice feature that optimize many queries using ENUM fields)
it's a enum optimization without index (but can help index too)
example:
a field with => enum ('a','b') NOT NULL
WHERE enum = 'c' => rewrite to WHERE 0 (impossible where)
WHERE enum != 'c' => rewrite to enum IN ('','a','b') OR WHERE 1 (always true)
WHERE enum!='b' => rewrite to enum IN ('','a') (COND_OK)
check that we can use any operator (LIKE for example) the key is:
read all enum possible values, execute operator for each enum value, if we have all false return it's a impossible where, if we have all true we have a always true, if we don't have all true and all false we can rewrite to IN () operator and use very optimized index queries (using HASH or BTREE for example)
hi guys, could anyone help me developing this MDEV (mariadb, maybe usefull for twitter too, since it's a nice feature that optimize many queries using ENUM fields)
https://mariadb.atlassian.net/browse/MDEV-4419
it's a enum optimization without index (but can help index too) example: a field with => enum ('a','b') NOT NULL
WHERE enum = 'c' => rewrite to WHERE 0 (impossible where) WHERE enum != 'c' => rewrite to enum IN ('','a','b') OR WHERE 1 (always true) WHERE enum!='b' => rewrite to enum IN ('','a') (COND_OK)
check that we can use any operator (LIKE for example) the key is: read all enum possible values, execute operator for each enum value, if we have all false return it's a impossible where, if we have all true we have a always true, if we don't have all true and all false we can rewrite to IN () operator and use very optimized index queries (using HASH or BTREE for example)