So I have a MYSQL db in which boolean values are stored as
binary(1). I was to investigate why certain queries were slow even though there was an index on the relevant columns. The issue was that when building the SELECT query, the system was using the
setBoolean method of
PreparedStatement which, as I understand it, converts the value to MYSQL
TINYINT. The query found the correct rows, but never used the index since the index was on a binary column. However, if I instead used the
setString method and converted the boolean to a string, namely
true, MYSQL was able to use the index and find the wanted rows fast.
Basically, the first query is what I got when using
setBoolean and the second when using
SELECT someColumn FROM table WHERE binaryColumn = 1 //Does not use index
SELECT someColumn FROM table WHERE binaryColumn = '1'//Uses index
In Java the change was this:
PreparedStatement ps1 = ...
PreparedStatement ps2 = ...
My question is simply if there is a better way to do this? Everything works fine but for some reason I think the code "smells" but I cant really motivate why.
I prefer always the setBoolean, because of abstraction.
The real interesting point is when your DB uses the index.
The optimizier of the DB use a index only, if it makes sense. If you have 1000 entries and a booleanvalue only split it into 50/50 it make no sense for that index, especial when its not the PK - but if you use a additional limitation, to get only 10 rows, as result, a good optimizer should use the index you specified - maybe a "composed index" on 2 columns (booleanColumn1, StringColumn1)
MySQL uses TINYINT(1) for the SQL BOOL/BOOLEAN. So I would change the data type to BOOLEAN, in accordance to standard SQL.
By your relay, the issue should then be resolved. By the way BIT(1) would be another option.