5 ms·
I've seen many devs extrapolate this thinking too far into sending only the most simple queries and doing all of the record filtering on the application end. Th
by BeefWellington 2y ago
I've seen many devs extrapolate this thinking too far into sending only the most simple queries and doing all of the record filtering on the application end. This isn't what I think you're saying -- just piggybacking to try and explain further.
The key thing here is to understand that you want the minimal correct query for what you need, not to avoid "making the database work".
The given example is silly because there's additional parameters that must be either NULL or have a value before the query is sent to the DB.
You shouldn't send queries like:
SELECT \* FROM users
WHERE id = 1234
AND (NULL IS NULL OR username = NULL)
AND (NULL IS NULL OR age > NULL)
AND (NULL IS NULL OR age < NULL)
But you should absolutely send:
SELECT \* FROM users
WHERE id = 1234
AND age > 18
AND age < 35
- deredede 2y agoWhile sub-optimal, your first example is probably fine to send and I'd expect to be simplified early during query planning at a negligible cost to the database server. What you shouldn't send is queries like: SELECT \* FROM users WHERE ($1 IS NULL OR id = $1) AND ($2 IS NULL OR username = $2) AND ($3 IS NULL OR age > $3) AND ($4 IS NULL OR age < $4) because now the database (probably) doesn't know the value of the parameters during planning and needs to consider all possibilities.
- oever 2y agoInteresting. I try to use prepared statements to avoid redoing the planning. But since the database schema is small compared to the data, the cost of query planning is quickly negligible compared to running an generic query that is inefficient.
- tsarchitect 2y agoOne of my rules: don't send NULL over the wire, but of course always check for NULL on the server if you're using db functions.