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
