Op 13 mrt 2015, om 00:03 heeft Wolfgang Enzinger het volgende geschreven:
> Am Sun, 8 Mar 2015 14:06:51 +0100 schrieb E.Pasma: > >> Actually query one appears slightly faster, >> Searching the PK index is faster as that is always a COVERING index. > > I was under the impression that the opposite is true, but I wasn't > sure > about that. > >> From the secunsary indexes only a part oh the key is used. >> Note there is not much use on adding PK as second column in the >> additional indexes. It is there anyway a a pointer to the row. > > You're right, that index doesn't make much sense; in my real > application it > looks different, what I was showing here was just an example (one > that was > not very well thought of, obviously). > >> I agree that it is strange that the execution plan for the two >> queries >> is different, After EXISTS the optimizer might ignore the expression >> in the select part of the sub-query. And Query one looks better as it >> soes not mention any column names. Personally I'd write SELECT NULL >> instead of SELECT *. > > I prefer "SELECT 1 ...", like in Gunter's post. But that's a matter of > taste, of course. > > Well, my actual point was that the query planner seems to > unnecessarily > visit the table row in order to read a column value that will be > discarded > lateron anyway, and that this could probably be optimized out > automatically. But my point is obsolete of course when the way it is > right > now is the faster one. Then again, it's not quite clear why this very > strategy is *not* chosen when "SELECT 1 ..." or similar is being > used. Not > a big deal indeed, just curious. > >> If speed matters instead of EXIST you can use IN and a list sub- >> query. >> This is superfast now: >> >> SELECT a1 FROM a WHERE a1 in (SELECT b.a1 FROM b INNER JOIN c >> USING(b1) WHERE c.c1=222); >> >> 0|0|0|SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?) >> 0|0|0|EXECUTE LIST SUBQUERY 1 >> 1|0|1|SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?) >> 1|1|0|SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?) > > I avoided IN for a long time, but that must originate from the time > when I > mostly used Jet (Access) file databases ... with SQLite, it's really > fast > indeed. > > Wolfgang Excuses Wolfgang, my impression that the PK is automatically covering was wrong. I like to make an other correction: >> After EXISTS the optimizer might ignore the expression >> in the select part of the sub-query. This is not true if the expression is an aggrehate function. It remains true that there seems to be a optimization opportunity for query 2.
