Each DBMS has its quirks.. I just migrated some databases from PostgreSQL to MariaDB and suddenly things got verrrry slow. Using a SHOW PROCESSLIST I tried to look for the queries that took a long time to run. These were quite simple queries like:

SELECT `hhlzeefi`.* FROM  `pfc` `hhlzeefi`  WHERE   `hhlzeefi`.`p` = 267 AND `hhlzeefi`.`d` is false;

I wrote my own Database Abstraction Layer which provides for the aliases that you see. Without aliases this query simplifies to:

SELECT *
FROM pfc
WHERE p = 267 AND d is false;

I just added an index to speed up this table, even though it doesn’t have that many rows (25K). The table structure is as follows:

CREATE TABLE pfc (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
p INT(11) UNSIGNED NOT NULL,
`key` TEXT NOT NULL,
`value` LONGTEXT NULL,
d TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
datecreated DATETIME(6) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX pk (d, p, `key`(100))
)

You also see the index I added. It’s the d-column. This is a type TINYINT(1). If we look at the MariaDB documentation: https://mariadb.com/kb/en/mariadb/boolean/

BOOL, BOOLEAN … are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.

Wonderful. Exactly what I wanted, a boolean column..

So, when we look at the query again:

SELECT *
FROM pfc
WHERE p = 267 AND d is false;

The d is false at the end is completely according to the documentation. Nevertheless the query takes a loooooot of time. It didn’t take that much time before I added the index. If I change the query into the, according to the documenation, equivalent version:

SELECT *
FROM pfc
WHERE p = 267 AND d = 0;

the results return with the speed of light (or well.. almost).

So, from now on, booleans in my queries are never the more readable and nicer “is false” or “is true”, but the ugly though indexable “= 0” or “= 1”…