SQLite ORDER BY does not work on integers: time for an index

My current project uses SQLite as it is really the only game in town on iOS. SQLite indexes provide an incredible performance boosts when you are dealing with large datasets. They apparently play a crucial role when it comes to something of a foregone conclusion in other databases – sorting.

What stumped my colleagues and I was that we had a database table where one of the columns had an INT (and also tested using an INTEGER) type (and yes – they are all NUMERIC to SQLite) – and ORDER BY kept returning incorrectly sorted results. According to this post on Stack Overflow, when you have a SQLite query that uses the ORDER BY, it may rely on temporary tables. Those in turn 'confuse' the database when it runs the query to sort your table.

The answer – add an index. Something as simple as


CREATE INDEX index_name ON table_name
(
   NAME_OF_COLUMN_TO_SORT ASC
);


Once you add it – you are, well – sorted out.