SQLite Forum

get only first record found

get only first record found

(1) By anonymous on 2021-09-21 03:14:17 [link] [source]

I am using SELECT to search a value

SELECT * FROM test WHERE available = '1' LIMIT 1

LIMIT seems to still do the work and then discard the extra results but I only need the first record and want to stop SQLITE after that to save resources

(2) By Simon Slavin (slavin) on 2021-09-21 06:00:34 in reply to 1 [source]

You have the best SELECT command. However, you may not have the best schema to keep time and processing to a minimum. Do this …

CREATE INDEX test_available ON test (available)

… just once. Then try the SELECT command again.

PS: I see that this is just a test, but other people may find this code, so I remind them it is considered bad style to use 'SELECT *' in real production code. You should name the columns you want.

(3) By John Dennis (jdennis) on 2021-09-21 09:36:14 in reply to 1 [link] [source]

In a simple test I loaded a million rows into a table into an unindexed column:

create table one_million (counter text);
insert into one_million
WITH RECURSIVE counter(value) AS (
   SELECT value + 1 FROM counter LIMIT 1000000
) select * from counter;

Then with .timer on I executed these selects:

select * from one_million where counter=1 limit 1;
Run Time: real 0.000 user 0.000000 sys 0.000000

select * from one_million where counter=999999 limit 1;
Run Time: real 0.108 user 0.109375 sys 0.000000

This suggests that the select is indeed stopping after finding the first matching row. Zero time to select the first row, 0.1 secs to get to the last row

(4) By anonymous on 2021-09-21 11:36:16 in reply to 3 [link] [source]

thanks a lot! nyl