Carl Jacobs wrote:
Hello, I'm a new user to this group as I haven't had any issues with sqlite
till now. I tried searching to see if this question has been asked before,
but couldn't find anything.
I'm using Windows.
I have a table with ~45 colums and ~17000 records. I do a search for some
stuff using 'SELECT * FROM MyTable WHERE Field1 LIKE "%val%"', and I know
That particular comparison demands a full scan of the table.
that it returns 5 records. When I step through the code sqlite_prepare and
sqlite_step return immediately, the column names/types process immediately
using sqlite_column_* functions, as does the first row of data. I then call
sqlite_step which returns immediately (as far as I can tell when single
stepping), and does so till all the data has been collected (all 5 rows). So
far, no problems . . .
. . . BUT . . .
after collecting the results for the last row I then call sqlite_step to go
on with the next step of the operation. Eventually this returns SQLITE_DONE
(as expected) but takes almost 2 seconds to execute!!!
What's happening?
Sounds to me like the matching rows are located near the beginning of
the table's physical storage, so you get them quickly, and then the next
sqlite3_step() has to scan the rest of the table.
If I have a search that returns only a single record it still takes 2
seconds.
If field1 is part of an index, then it still takes 2 seconds.
An index won't help with a %xxxx% comparison; there's no way to do it
short of examining every non-NULL value in the column.
