Roaring bitmaps as MySQL/MariaDB User Defined Functions

This development is completed, but only partially tested. Please use it at your own risk.

Storage

• You can use LONGBLOB data type to store bitmap in MySQL/MariaDB column.
• Please remember that if you're using database replication, you should probably exclude tables with bitmaps from it or switch to statement-based replication. Otherwise, the size of your binary log will significantly increase, and the performance of the database will be slightly slower.

UDF functions

prefixes

• roaring32 - for INT (maybe unsigned)
• roaring64 - for BIGINT (maybe unsigned)
• nullsafe - SQL functions return NULL if at least one argument is NULL. With the 'nullsafe' prefix, the function will return an empty result of the corresponding type, instead of NULL.
  • empty bitmap BLOB
  • 0 for integer
  • '[]' for json

functions

• roaring[32|64]_[nullsafe_]create(value0 INT,[value1 INT[, ...]]) -> BLOB - Create bitmap from multiple integer arguments.
• roaring[32|64]_[nullsafe_][remove|insert](map BLOB, value0 INT[,value1 INT[, ...]]) -> BLOB - Add or remove multiple integers from bitmap.
• roaring[32|64]_[nullsafe_]contains (map BLOB, value INT) -> BOOL - Check whether integer is in a bitmap.
• roaring[32|64]_[nullsafe_]count (map BLOB) -> INT - How many integers are in a bitmap.
• roaring[32|64]_[nullsafe_]json (map BLOB) -> TEXT - Get all integers from bitmap as JSON array.
• roaring[32|64]_[nullsafe_][and|or|xor] ([map0 BLOB[, map1 BLOB[, ...]]]) -> BLOB - Perform one of bitmap operations (and, or, xor) on multiple bitmap arguments and return resulting bitmap.
• roaring[32|64]_[nullsafe_][and|or|xor]_count ([map0 BLOB[, map1 BLOB[, ...]]]) -> BLOB - Perform one of bitmap operations (and, or, xor) on multiple bitmap arguments and return number of integers in a resulting bitmap.
• roaring[32|64]_[nullsafe_]group_create (value INT) -> BLOB - Aggregation. Create bitmap from integers is group.
• roaring[32|64]_[nullsafe_]group_[and|or|xor] (map BLOB) -> BLOB - Aggregation. Perform one of bitmap operations (and, or, xor) on bitmaps in group and return resulting bitmap.
• roaring[32|64]_[nullsafe_]group_[and|or|xor]_count (map BLOB) -> BLOB - Aggregation. Perform one of bitmap operations (and, or, xor) on bitmaps in group and return number of integers in a resulting bitmap.

full list of functions

libmysql_roaring.md

SQL queries for all functions loading

libmysql_roaring.sql

Build and installation

Standalone MySQL/MariaDB

• Get Rust programing language tools https://www.rust-lang.org/tools/install
• Get this git repository git clone --depth=1 https://github.com/tarasbogach/mysql_roaring.git
• Go to project folder cd mysql_roaring
• Build this project cargo build --release
• Now you should have compiled shared library ./target/release/libmysql_roaring.so
• Find your MySQL/MariaDB plugin folder echo "SHOW VARIABLES LIKE 'plugin_dir';" | mysql, and copy shared library to it sudo cp ./target/release/libmysql_roaring.so /usr/lib/mysql/plugin/
• Run queries from libmysql_roaring.sql cat libmysql_roaring.sql | mysql

Docker

You will need to add build stage to your Dockerfile. And copy .so and .sql files from it to your MySQL/MariaDB image.

```Dockerfile FROM rust:latest AS build

ENV CARGOREGISTRIESCRATESIOPROTOCOL=sparse

WORKDIR /build

COPY ./mysql_roaring /build

RUN --mount=type=cache,target=/usr/local/cargo/registry \ --mount=type=cache,target=/build/target \ cargo build --release \ && mkdir /output \ && cp target/release/libmysqlroaring.so /output \ && cp libmysqlroaring.sql /output

FROM mariadb

COPY --from=build /output/libmysqlroaring.so /usr/lib/mysql/plugin/ COPY --from=build /output/libmysqlroaring.sql /docker-entrypoint-initdb.d/ ```

Examples

32bit integer

```sql CREATE DATABASE IF NOT EXISTS example;

CREATE TABLE IF NOT EXISTS example.bitmaps (id INT8 UNSIGNED PRIMARY KEY, map LONGBLOB);

TRUNCATE TABLE example.bitmaps;

INSERT INTO example.bitmaps SELECT seq % 10 as id, roaring32groupcreate(CAST(RAND() * POW(2, 31) AS INTEGER)) as map FROM mysql.seq1to_1000000 GROUP BY seq % 10;

SELECT roaring32_count(map) FROM example.bitmaps;

SELECT roaring32groupor_count(map) FROM example.bitmaps; ```

64bit integer

Please note that the 64-bit version may be significantly (6.5x in some my cases) slower than the 32-bit version.

```sql CREATE DATABASE IF NOT EXISTS example;

CREATE TABLE IF NOT EXISTS example.bitmaps (id INT8 UNSIGNED PRIMARY KEY, map LONGBLOB);

TRUNCATE TABLE example.bitmaps;

INSERT INTO example.bitmaps SELECT seq % 10 as id, roaring64groupcreate(CAST(RAND() * POW(2, 63) AS INTEGER)) as map FROM mysql.seq1to_1000000 GROUP BY seq % 10;

SELECT roaring64_count(map) FROM example.bitmaps;

SELECT roaring64groupor_count(map) FROM example.bitmaps; ```