SQLite Forum


5 forum posts by user markxwagner

19:13 Post: why do I get a row when querying a table with no rows? (artifact: 9c0901907c user: markxwagner)

I'm trying to decide if this is a bug or a lack of understanding on my part.

This selects on t and joins t2, neither of which has any rows. I would have thought the result would be zero rows. ??

Any help would be appreciated.

sqlite> .nullvalue VIS_NULL

sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE t (foo); CREATE TABLE t1 (bar); COMMIT;

sqlite> select group_concat(quote(bar)) from t left join t1; group_concat(quote(bar)) VIS_NULL sqlite>

16:13 Post: different query plan result when executed within transaction (artifact: 4a6beb0964 user: markxwagner)

I wrote some code to automatically run each of my queries through explain query plan to check for full table scans.

This only runs while executing tests.

But I noticed that in some cases I get a different result when the eqp is executed within a transaction. Specifically, when the eqp is run outside of the transaction it correctly shows a search using index. When executed within a transaction it show a full table scan.

I can try to put together an example but thought I'd check ahead if there area any known issues around this.


07:14 Reply: index to help with selection and ordering (artifact: f1a6189d6e user: markxwagner)

Fair point.

Thinking through my scenario a bit more I conclude that the temp b=tree for ordor by is far less of an issue compared to the table scan. In other words, if the index is used to retrieve a reasonable number of rows (using table search) then the sorting is a pretty much a non-issue. If the select is returning a large number of rows then the order by is a problem but the whole query is problematic.

Thanks again.

23:19 Post: index to help with selection and ordering (artifact: ed09856324 user: markxwagner)

I'm sure this must be faq and perhaps obvious but I appear to not see it.

It seems difficult to create an index which helps with both selection and ordering. For example, imagine a table with a timestamp column and a name column. Now imagine wanting to select all rows whose timestamp is greater than X and then order that result by name.

From what I understand, only one index can be used per table on a given query. And a multi column index won't help because in this example both columns would need to be in the first position of the index.

Is there an approach that solves this goal of making both the selection and the ordering use an index?


17:33 Post: unique column constraint vs unique index (artifact: 5e249867c3 user: markxwagner)

I've always assumed that a unique index on a single column was superior to a unique column constraint since the former can be dropped and/or recreated, etc (e.g. in the future I decide that really it should be unique across two columns or really it doesn't need to be unique at all).

Is there any downside to using a unique index in this way? Is it really all the same under the covers?

Am I missing something?