[MDEV-28532] Keylookup with bitmask Created: 2022-05-10  Updated: 2022-05-11  Resolved: 2022-05-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3.34
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Michael Assignee: Daniel Black
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Debian 10


 Description   

Keylookup with Bitmask is slower than it could be

Create a big Table with normal key on tinyint column status

select * from table where status &7 = 2
takes much much longer than
select * from table where status IN (2, 18, 34, 50, 66, 82, ....etc )

 Comments   
Comment by Daniel Black [ 2022-05-11 ]

Generally (expression on column) = constant will need to evaluate the expression for each row in the table. No index optimizations on the column can be made.

While the "status in ..." is an alternate, as an unbound list is rather cumbersome. While the optimizer does re-write some simple expressions its obviously not the case with bitmasks.

The alternative following dominate sql pattern to put a single value into a column being
"status & 7" and have other columns for other values. Then appropriate indexing can be added for this bit 7 column (assuming has sufficient selectivity to be useful). generated columns provide an alternative to aggregate/de-aggregage bitfields and index/store them separately.

Comment by Michael [ 2022-05-11 ]

Never heard before of generated columns,
you made my day.

CREATE TABLE blah (
c_status int(11) unsigned NOT NULL DEFAULT 2,
c_state int(11) as (c_status &7),
c_callresult int(11) as (c_status &960),
.....
)

PLS close this task
and thank you...

Comment by Daniel Black [ 2022-05-11 ]

You're welcome. Happy SQLing.

Generated at Thu Feb 08 10:01:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.