How to store string array in sqlite database and keep order

Question

i want to store a great amount of strings into my sqlite database. I want them to be always in the same order when i read them as i add them to the database. I know i could give them an autoincrementing primary key and sort by that but since there can be up to 100.000 strings this is a performance issue. Besides the order should NEVER change or be sorted in any different way.

short example:

sql insert "hghtzdz12g" sql insert "jut65bdt" sql insert "lkk7676nbgt"

sql select * should give ALWAYS this order {"hghtzdz12g", "jut65bdt", "lkk7676nbgt" }

Any ideas how to achive this ?

Thanks

Yeah. A while - in miliseconds. This is 2014. Processors frequency is not mhz ;) a million rows was small 25 years ago. — TomTom, Jun 13 '14 at 07:52

CL. · Accepted Answer · 2014-06-13T08:08:56.280

2

In a query like

SELECT * FROM MyTable ORDER BY MyColumn

the database does not need to sort the results if the column is indexed, because it can just scan through the index entries in order.

The rowid (or whatever you call the autoincrementing column) is an index, and is even more efficient than a separate index.

edited Jun 13 '14 at 08:08

answered Jun 13 '14 at 07:47

CL.

173,858
17
217
259

If i understand you right this "CREATE TABLE IF NOT EXISTS strings (name TEXT)" should be enough ? I am just starting to learn about database handling and i am no shure if i understand the rowid thing correct. Does any table have a rowid or do i have to specify that when i create the table? – Tristan Heitzinger Jun 13 '14 at 07:53
You'd have to say [WITHOUT ROWID](http://www.sqlite.org/withoutrowid.html) to omit the rowid. – CL. Jun 13 '14 at 08:09

score 0 · Answer 2 · edited May 23 '17 at 12:08

If you are sure you will never need anything but exactly this array in exactly this order, you can cheat the database and put in a single blob field.

But then you should ask yourself why you chose a database in the first place.

The correct database solution is indeed a table using a key that you can sort by.

If this performance is not enough, you can have a look here for performance hints.

If you need ultra-fast performance, maybe a database is not the best tool for the job. Databases are used for their ACID abilities and speed is not one of them but rather a secondary objective of everything in software.

How to store string array in sqlite database and keep order

2 Answers2