LIMIT speeds up JOIN
(1) By Roman (moskvich412) on 2022-03-04 20:02:57 [source]
Dear SQLiters,
I noticed strange behavior that LIMIT speeds up JOIN dramatically. Not suspecting anything, to test my code (not to create test case) I first wrote this query:
WITH table1(inspectionID, ...) AS ( SELECT inspectionID, ... ORDER BY inspectionID LIMIT 10),
table2(inspectionID, ...) AS ( SELECT inspectionID, ... ORDER BY inspectionID LIMIT 10)
SELECT * FORM table1 JOIN table2 USING (inspectionID)
I limited each table expression to speed up my tests and verify the query (in reality it is much longer). Subsequently, after I removed the LIMIT's first row of the output was produced in no time, but then nothing, consuming CPU. I put the LIMITs back and was increasing the limit, always getting results fast. Setting limit past the number of records (and -1) also made it fast. But, removing the LIMIT ... slow. So, I kept LIMIT -1.
Does this indicate anything? I used sqlite3 shell, version 3.38.0
Roman
(2) By Rico Mariani (rmariani) on 2022-03-04 23:19:41 in reply to 1 [link] [source]
Maybe someone knows offhand (I don't) but I suggest you include a complete working example that illustrates the issue. The "..." might matter and in any case it's easier to convince people to paste than to create an example of maybe what is happening.
(3) By Warren Young (wyoung) on 2022-03-05 02:00:54 in reply to 2 [link] [source]
It might also help to know what the OP gets from running EXPLAIN on that SQL.
(4) By Roman (moskvich412) on 2022-03-09 05:09:39 in reply to 3 [link] [source]
Dear SQLiters, Unfortunately, I do not know how to interpret EXPLAIN QUERY PLAN results, Here they are: ------------------------------ -- no LIMIT ------------------------------ QUERY PLAN |--MATERIALIZE numberedData | |--CO-ROUTINE SUBQUERY 29 | | `--SCAN data USING INDEX dataAccessionIndex | `--SCAN SUBQUERY 29 |--MATERIALIZE scoredResponse | `--COMPOUND QUERY | |--LEFT-MOST SUBQUERY | | |--MATERIALIZE extentScore | | | `--SCAN 5 CONSTANT ROWS | | |--SCAN s | | `--SCAN r | |--UNION USING TEMP B-TREE | | |--MATERIALIZE degreeScore | | | `--SCAN 5 CONSTANT ROWS | | |--SCAN s | | `--SCAN r | `--UNION USING TEMP B-TREE | `--SCAN response |--SCAN inspection |--SEARCH numberedData USING AUTOMATIC PARTIAL COVERING INDEX (number=? AND accession=?) |--SEARCH numberedData USING AUTOMATIC PARTIAL COVERING INDEX (number=? AND accession=?) |--SEARCH scoredResponse USING AUTOMATIC PARTIAL COVERING INDEX (variableName=?) |--SEARCH scoredResponse USING AUTOMATIC PARTIAL COVERING INDEX (variableName=?) |--SEARCH numberedData USING AUTOMATIC PARTIAL COVERING INDEX (number=? AND accession=?) |--SEARCH scoredResponse USING AUTOMATIC PARTIAL COVERING INDEX (variableName=?) |--SEARCH scoredResponse USING AUTOMATIC PARTIAL COVERING INDEX (variableName=? AND inspectionID=?) |--SEARCH scoredResponse USING AUTOMATIC PARTIAL COVERING INDEX (variableName=? AND inspectionID=?) `--SEARCH scoredResponse USING AUTOMATIC PARTIAL COVERING INDEX (variableName=? AND inspectionID=?) ------------------------------ -- with LIMIT ------------------------------ QUERY PLAN |--MATERIALIZE allData | |--MATERIALIZE numberedData | | |--CO-ROUTINE SUBQUERY 29 | | | `--SCAN data USING INDEX dataAccessionIndex | | `--SCAN SUBQUERY 29 | |--SCAN inspection | |--SEARCH numberedData USING AUTOMATIC PARTIAL COVERING INDEX (number=? AND accession=?) | |--SEARCH numberedData USING AUTOMATIC PARTIAL COVERING INDEX (number=? AND accession=?) | `--SEARCH numberedData USING AUTOMATIC PARTIAL COVERING INDEX (number=? AND accession=?) |--MATERIALIZE allScores | |--MATERIALIZE scoredResponse | | `--COMPOUND QUERY | | |--LEFT-MOST SUBQUERY | | | |--MATERIALIZE extentScore | | | | `--SCAN 5 CONSTANT ROWS | | | |--SCAN s | | | `--SCAN r | | |--UNION USING TEMP B-TREE | | | |--MATERIALIZE degreeScore | | | | `--SCAN 5 CONSTANT ROWS | | | |--SCAN s | | | `--SCAN r | | `--UNION USING TEMP B-TREE | | `--SCAN response | |--SCAN scoredResponse | |--SEARCH scoredResponse USING AUTOMATIC PARTIAL COVERING INDEX (variableName=? AND inspectionID=?) | |--SEARCH scoredResponse USING AUTOMATIC PARTIAL COVERING INDEX (variableName=? AND inspectionID=?) | |--SEARCH scoredResponse USING AUTOMATIC PARTIAL COVERING INDEX (variableName=? AND inspectionID=?) | |--SEARCH scoredResponse USING AUTOMATIC PARTIAL COVERING INDEX (variableName=? AND inspectionID=?) | |--SEARCH scoredResponse USING AUTOMATIC PARTIAL COVERING INDEX (variableName=? AND inspectionID=?) | `--USE TEMP B-TREE FOR ORDER BY |--SCAN allData |--SEARCH allScores USING AUTOMATIC COVERING INDEX (inspectionID=?) `--USE TEMP B-TREE FOR ORDER BY With LIMIT -1, the speed up is enormous Roman