This situation you’d really just want to pick one of the two and adding aĭISTINCT clause on user_id in the above example would do just that.
A larger one if you are doing something user-facing. A small one if you areĭisplaying the latest orders to the admins of your application (they see two The upshot is that this query would includeĢ entries for that user. What if two orders have the same datetime (specifically the most recent)? In theĬase of users making orders it’s pretty unlikely short of a double-submitīug, but depending on your use-case for the table in question and the scale of One caveat: there’s a slight issue with using a datetime as an identifier here. Return only the subset of user_ids paired with their most recent order this The orders that match the user_id and the created_at timestamp of the created_atĪlright, this is slightly less intuitive. * FROM ( SELECT user_id, MAX ( created_at ) AS created_at FROM orders GROUP BY user_id ) AS latest_orders INNER JOIN orders ON orders. sqlite> select b.* from (select match, name from data a limit 100) b WHERE 1 order by b.SELECT orders. The subquery uses LIMIT and outer query has other sources besides the transient table produced by the subquery.Īttempting to trick the optimizer was as simple as adding a WHERE clause, thus invalidating conditionġ9 If the subquery uses LIMIT then the outer query may not have a dummy WHERE clause. However, it might be worth considering if another condition should be added for flattening to occur to prevent queries like the above from being flattened, if deemed safe.ġ3 The subquery and outer query do not both use LIMIT. So the answer to my original question is then that it is working as designed. I'm not suggesting this behavior should change in Sqlite, especially if it is well documented as above. This simplest behavior is actually what was expected in this query and, at least in this instance, was the most optimal.
Sqlite order by and limit top and bottom full#
Such a plan can be suboptimal since the transient table will not have any indexes and the outer query (which is likely a join) will be forced to do a full table scan on the transient table. When a subquery occurs in the FROM clause of a SELECT, the simplest behavior is to evaluate the subquery into a transient table, then run the outer SELECT against the transient table. In the case of my original query, I didn't care about ordering of the subquery's result set, so that's why I didn't specify order by rowid as suggested above, which does make the optimizer skip the flattening.
Looking through the conditions that need to be met for the query optimizer to use flattening here, this simple query meets all conditions and therefore is being flattened, as pointed out by Keith. Statement Heap/Lookaside Usage: 7104 bytes
Number of Pcache Overflow Bytes: 69136264 (max 69153184) bytes Number of Outstanding Allocations: 18951 (max 18964) My version is 3.39.2 sqlite> select * from (select addrprilo, crroute from zip4base limit 100) order by addrprilo ġ OpenEphemeral 2 4 0 k(1,NOCASE) 0 nColumn=4Ģ Integer 100 1 0 0 r=100 LIMIT counterģ OpenRead 1 2 0 9 0 root=2 iDb=0 zip4baseħ IfNotZero 1 11 0 0 if r!=0 then r-, goto 11ġ2 MakeRecord 2 3 6 0 r=mkrec(r)Ģ1 Transaction 0 0 56 0 1 usesStmtJournal=0 But because the LIMIT is inside the sub-query and separate from the ORDER BY, it's going through the entire table when it only needs to grab 100.Ĭould someone look at this EXPLAIN output and let me know if that is indeed what's happening? Which would make sense if it was "ORDER BY x LIMIT 100" outside of the sub-query. What feels like is happening is that rather than getting 100, stopping the scan, and then sorting them, it's going through the whole 47 million records, constantly keeping the 100 lowest records when sorted by the sort column. If I'm reading the EXPLAIN plan correctly, it is indeed doing the 100 sample before sorting. I've got a 47 million record table, I'm limiting the query to 100, and it's doing a full scan of 47 million? Even if I run it back to back so it's as cached as can be. Doing this sort of select with the LIMIT 100 inside the subquery, and the ORDER BY outside takes 12 seconds. I tried this on an old jumbo table I have with 47 million records. Ok, so I have to admit, something weird does seem to be going on here.
0 kommentar(er)
