On Sat, 2014-11-08 at 10:23 -0500, James K. Lowden wrote:
> On Sat, 08 Nov 2014 22:55:46 +0900
> Tristan Van Berkom <tris...@upstairslabs.com> wrote:
>
> > So I would have to say, the "right way to do it" is the most efficient
> > way, the one which provides SQLite with the best indications of how
> > to plot an efficient query plan.
>
> Keith is suggesting that the right way to do it is neither "any way that
> works" nor necessarily "whatever is fastest" but "the clearest
> formulation of the query". Clarity has the salutary property of being
> most likely to be correct (because understood by the human) and stands
> a better than fair chance of being executed efficiently (because it
> translates easily to a good query plan).
>
> Most of time -- not every time, but most of the time -- indexes
> and table design matter much more to efficient execution than query
> syntax. When a clearly expressed query is not executed efficiently in
> the presence of useful indexes, and especially when a slightly different
> one does, that's usually considered a defect of the query planner.
I see what you're saying, as I mentioned in the initial email I do
consider myself to be a relative newbie, and the majority of work
I've been doing with SQL (until this year) has been working on
optimizing existing schemas/queries for embedded use.
While I do understand SQL as a functional language, most functional
programming I've done still has rather explicit syntax/rules, so I get
particularly uncomfortable with writing vague statements, such as
JOIN tableA, tableB WHERE ... without being exactly specific on the
heart/nature of the join which is happening.
Also what I've found in my limited experience is that nesting SELECT
statements, at least in SQLite, completely throws off the planner,
as in it has no opportunity to make a good guess and has to deal
with the result set of a nested SELECT as an opaque table, which
is generally not an indexed table (or perhaps it is, but I wouldnt
know because those indexes don't seem to be declarative in any way).
So indeed, I am not comfortable with 'leaving it up to chance',
and if there is a way to get higher specificity, I try to achieve that.
In any case, I do have queries which work well at this point, but
posted this question to the list in the hope I could find the right
specificity for the given query - it's not a very big deal, I'll
probably just stick with what works and try to fix it in the places
where profiling reveals that I'm doing something wrong.
Cheers,
-Tristan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
