[Django]-Efficient SELECT with complex WHERE condition – do I need to store a column with the calculated value?

Depending on the database type you are using, you might want to use a trigger to calculate the derived field. That way, they can never get out of synch.

This means that the field (length) could be re-calculated every time start or end changes.

I’d store the length, but I’d make sure the calculation was done in my insert and update sprocs so that as long as everyone uses your sprocs there is no more overhead for them.

Unfortunately neither of your target databases support computed columns. I would do the following:

1. First, determine whether you really have a performance problem. It is true that WHERE end - start = ? will perform more slowly than WHERE length = ?, but you don’t define what a “really big table” is in your application, nor what the required performance is. No need to optimize away a problem that may not exist.
2. Determine whether you can support any latency in your searches. If so, you can add the calculated column to the table but dedicate a separate task, running every five minutes, each hour, or whatever, to fill in the values.
3. In PostgreSQL you could consider a materialized view, which I believe are supported at the engine level. (See Catcall’s comment, below).
4. Finally, if all else fails, consider using a trigger to maintain the calculated column.