Comparison operators in SQLite are performed on all rows?

Asked Mar 05 '22 at 14:39

Active Mar 05 '22 at 14:42

Viewed 37 times

Suppose that we have a table called agents and an integer column called commission with 100000 rows, and then we will do the following query:

SELECT *
FROM agents
WHERE commission > 5;

Will the database iterate through all the rows in the table? this can be very slow if we have many rows. I am working with SQLite

edited Mar 05 '22 at 14:42

Zakaria

asked Mar 05 '22 at 14:39

Ricardo Casimiro

5

You can add an index on the `commission` column. – Zakaria Mar 05 '22 at 14:41
_this can be very slow if we have many rows_ - that's one of the major challenges when working with somewhat bigger tables. – jarlh Mar 05 '22 at 15:22
@jarlh No, not really - that's what indexes are for. Searching tabular data _never needs to be slow_, regardless of the size of the table, provided there are indexes defined that match the queries being run. In the OP's case, they should run `CREATE INDEX IX_commission ON agents ( commission ) INCLUDE ( etc )`. And they should avoid `SELECT *` so the `INCLUDE` list can be defined. – Dai Mar 05 '22 at 16:52

0 Answers0