Best performance for index on many identical values-CodePudding

I have an SQLite file containing unique identifiers:

 -------------- --------- ------- 
|     uid      | request | print |
 -------------- --------- ------- 
| slisn39r     |       1 |     1 |
| 91na039d     |       1 |     1 |
| 039184ms     |       1 |     1 |
| ..(16 mio).. |         |       |   << could be millions of used records
| 3948mass     |       0 |     0 |   << select first non-requested
 -------------- --------- -------

I repeatedly (at intervals of < 300 msec) select the next unused, limit 1:

SELECT uid from uidtable where (request=0 and print=0) limit 1

When number of used rows is low this is near instant, but at 6 million used, it's in seconds. Given the criteria above (millions of identical values), is it correct to use:

CREATE INDEX if not exists idx_uid on uidtable ("request" ASC, "print" ASC);

CodePudding user response：

Appreciate that a limit query really only makes much sense if it has an ORDER BY clause. That being said, there must be some third column here which provides the ordering with which to speak of "next unused." Assuming that such an ordering column ord does exist, we can try using the following index:

CREATE INDEX IF NOT EXISTS idx_uid ON uidtable (request, print, ord);

Now consider the following query:

SELECT *
FROM uidtable
WHERE request = 0 AND print = 0
ORDER BY ord
LIMIT 1;

Ideally SQLite can use the above index to quickly find the sub tree of the index for which request = 0 and print = 0. Then, since the index is also sorted by ord, SQLite can simply take the record corresponding to the lowest ord value.

CodePudding user response：

If the data does not change between requests, you do not want to use "LIMIT 1" clause. What you should do is iterating on the cursor. It involves, obviously "client" side code, rather than SQL. I execute the query once and then fetch result rows. You can fetch one row at a time (which is the only mode supported by the SQLite C library) or in batches, if you use a wrapper supporting batch mode.