On 11 May 2013, at 10:23am, Paolo Bolzoni <paolo.bolzoni.br...@gmail.com> wrote:
> sqlite> explain query plan SELECT id FROM tour LIMIT 1; > 0|0|0|SCAN TABLE tour USING COVERING INDEX tour_unsorted_path_idx > (~1000000 rows) > > I am not still sure I understand completely the output of explain > query plan. But for sure, a small number in the end sounds a good > thing. In my application after working with indexes and removing > the big numbers of rows I got a great speed up. > > I just have a question about query written in the begin, in my > application I use it to know if a table is empty. I execute it > and just use sqlite3_step return value. Errors a part, if it is > SQLITE_DONE the table is empty, if it is SQLITE_ROW it is not. > > To get this simple result, sqlite3 actually scans the whole > table? If it is so, can be avoided? Your design is good. Your query is an efficient way of finding out whether the table is empty or not. Working from the top of your post ... You might get a more accurate row-count in the EXPLAIN QUERY PLAN by using the ANALYZE command once. But that won't speed up your query. The EXPLAIN QUERY PLAN output says that SQLite has found a covering index, and this means SQLite won't need to make a temporary index to execute this command. So there won't be any need for SQLite to read every row of the table, it will just jump to the next row each time you use _step(). In other words, the command is working the way you want it to. Once you have tested the result of _step() don't forget to _finalize() your query. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
