Re: [sqlite] Scan table in explain query. Question.

Staffan Tylen wrote: > SEARCH USING COVERING INDEX > SEARCH USING INDEX > SCAN USING COVERING INDEX > > What is the difference and what do they imply? Full table scans, index lookups (searches), and covering indices are explained on this page: . Regards,

Re: [sqlite] Scan table in explain query. Question.

Reading this thread I decided to run an explain query plan for a query that I have with multiple selects combined with union. The (simplified) result is: SEARCH TABLE UB USING COVERING INDEX UBS (UBS=?) (~5108 rows) USE TEMP B-TREE FOR GROUP BY USE TEMP B-TREE FOR ORDER BY SEARCH TABLE UB USING

Re: [sqlite] Scan table in explain query. Question.

ANALYZE analyzes the data distribution of the indexes. This is affected by the data distribution of the tables, since the indexes index the tables. The statistics computed by the analysis are stored in the database and used to choose the most efficient way to execute a query. If your data

Re: [sqlite] Scan table in explain query. Question.

At the moment EXPLAIN gives me a very small number of rows for all the queries (thanks to indexes) a part of this one, so if I understand correctly ANALYZE is not needed anymore. Or I can get a speed up executing once in a while? Regards, Paolo On Sat, May 11, 2013 at 1:06 PM, Clemens Ladisch

Re: [sqlite] How do I optimize this?

Igor Korot schrieb am 11.05.2013 08:14: There is no WHERE filtering on positionsforleague table, so I'm not sure what index to create. What am I missing? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Scan table in explain query. Question.

Paolo Bolzoni wrote: > sqlite> explain query plan SELECT id FROM tour LIMIT 1; > 0|0|0|SCAN TABLE tour USING COVERING INDEX tour_unsorted_path_idx > (~100 rows) > > I am not still sure I understand completely the output of explain > query

Re: [sqlite] Scan table in explain query. Question.

On 11 May 2013, at 10:23am, Paolo Bolzoni wrote: > sqlite> explain query plan SELECT id FROM tour LIMIT 1; > 0|0|0|SCAN TABLE tour USING COVERING INDEX tour_unsorted_path_idx >(~100 rows) > > I am not still

[sqlite] Scan table in explain query. Question.

sqlite> explain query plan SELECT id FROM tour LIMIT 1; 0|0|0|SCAN TABLE tour USING COVERING INDEX tour_unsorted_path_idx (~100 rows) I am not still sure I understand completely the output of explain query plan. But for sure, a small number

Re: [sqlite] How do I optimize this?

Igor Korot wrote: > EXPLAIN QUERY PLAN > SELECT positions.positionname, positionsforleague.value > FROM positionsforleague, positions, leagues > WHERE leagues.id = positionsforleague.id > AND positions.positionid = positionsforleague.positionid > AND leagues.name = "test"; > > 0|0|0|SCAN TABLE

Re: [sqlite] B-Tree usage

Thank you. That clear things up. On Fri, May 10, 2013 at 11:28 PM, Nico Williams wrote: > On Sat, May 11, 2013 at 1:12 AM, Igor Korot wrote: > > On Fri, May 10, 2013 at 11:07 PM, Nico Williams >wrote: > >> It'd help if you

Re: [sqlite] B-Tree usage

On Sat, May 11, 2013 at 1:12 AM, Igor Korot wrote: > On Fri, May 10, 2013 at 11:07 PM, Nico Williams wrote: >> It'd help if you posted a simplified schema and statements that are >> not planned correctly by SQLite3. > > I'm not saying that my statements

[sqlite] How do I optimize this?

Hi, ALL, sqlite> EXPLAIN QUERY PLAN SELECT positions.positionname, positionsforleague.val ue FROM positionsforleague, positions, leagues WHERE leagues.id = positionsforle ague.id AND positions.positionid = positionsforleague.positionid AND leagues.nam e = "test"; 0|0|0|SCAN TABLE

Re: [sqlite] B-Tree usage

Hi, Nico, On Fri, May 10, 2013 at 11:07 PM, Nico Williams wrote: > It'd help if you posted a simplified schema and statements that are > not planned correctly by SQLite3. > I'm not saying that my statements are not planned correctly. What I'm saying is that by reading the

Re: [sqlite] B-Tree usage

It'd help if you posted a simplified schema and statements that are not planned correctly by SQLite3. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] B-Tree usage

SQLite3 only uses B-trees, for tables and indexes. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] B-Tree usage

Hi, ALL, Looking at http://www.sqlite.org/eqp.html#section_1_2 it would be nice to mention in which case B-Tree is unavoidable. Only one use case with B-Tree vs indexing is used but from reading first paragraph it looks like indexing is B-Tree replacement everywhere. I have a query with ORDER BY