问题描述:

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 '0' for false and '1' for 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 setString:

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 = ...

ps1.setBoolean(1, true);

...

PreparedStatement ps2 = ...

ps2.setString(1, "1");

...

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.

相关阅读:
Top