Is it faster to use a complicated boolean to limit a ResultSet at the MySQL end or at the Java end?
boolean-operations, database, java
Solution
It is almost certainly faster to send the condition to the database.
- You avoid transferring lots of rows whose data you don't need.
- The database might use something faster than a table scan. It may be able to use an index which allows it to more quickly find the interesting rows without having to check the conditions on every row.
Problem
Lets say I have a really big table filled with lots of data (say, enough not to fit comfortably in memory), and I want to analyze a subset of the rows. Is it generally faster to do: ``` SELECT (column1, column2, ... , columnN) FROM table WHERE (some complicated boolean clause); ``` and then use the ResultSet, or is it faster to do: ``` SELECT (column1, column2, ... , columnN) FROM table; ``` and then iterate over the ResultSet, accepting different rows based on a java version of your boolean condition? I think it comes down to whether the Java iterator/boolean evaluator is faster than the MySQL boolean evaluator.