Ich habe einen Tisch mit ein paar hunderttausend Zeilen. (Es handelt sich um eine vorberechnete Tabelle, die die Beziehung zwischen Wortmustern und anderen großen Tabellen ausdrückt.) Ich muss mehrere Einträge auswählen, um eine Kombination aus verschiedenen Einträgen zu finden als l1, ltc als l2 ... sortieren nach ... Die Geschwindigkeit der Abfrage hängt von der Sortierung ab: Ohne Sortieren sind es einige Millisekunden, mit Sortieren kann es einige Minuten dauern. Dies liegt, soweit ich das beurteilen kann, am temporären B-Tree, den Sqlite zum Sortieren erstellt, obwohl ich einen Index für die sortierte Spalte "nr" habe. Ich verstehe nicht, warum Sqlite diesen Index nicht verwendet.

CREATE TABLE ltc
(nr INTEGER, lemId INTEGER, cId INTEGER, bId INTEGER,
-- UNIQUE (lemId, cId, bId), 
-- if I add this uniqueness constraint, strangely enough it doesn’t use my index at all, even at a simple ORDER BY.
PRIMARY KEY(nr,lemId,cId),
FOREIGN KEY(lemId) REFERENCES lems(rowid),
FOREIGN KEY(cId) REFERENCES cs(rowid),
FOREIGN KEY(bId) REFERENCES bs(rowid) )

CREATE INDEX nri ON ltc(nr)

Hier ist die abgespeckte Version meines Auswahlbefehls:

SELECT  l0.nr,l1.nr,l2.nr
FROM ltc as l0, ltc as l1, ltc as l2
WHERE 
    l0.lemId IN (1001) -- in reality 1001 is some simple sub select.
AND l1.lemId IN (1002,1003)
AND l2.lemId IN (1004 )
ORDER BY
    l0.nr,
    l1.nr,
    l2.nr
LIMIT 10;

Der EXPLAIN QUERY PLAN enthält:

(0, 0, 0, u'SCAN TABLE ltc AS l0')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 1')
(1, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 1, 1, u'SCAN TABLE ltc AS l1')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 2')
(2, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 2, 2, u'SCAN TABLE ltc AS l2')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 3')
(3, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 0, 0, u'USE TEMP B-TREE FOR ORDER BY')

und dies bei entferntem ORDER BY (oder auf nur eine Spalte reduziert)order by l0.nr):

(0, 0, 0, u'SCAN TABLE ltc AS l0 USING COVERING INDEX sqlite_autoindex_ltc_1')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 1')
(1, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 1, 1, u'SCAN TABLE ltc AS l1 USING COVERING INDEX sqlite_autoindex_ltc_1')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 2')
(2, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 2, 2, u'SCAN TABLE ltc AS l2 USING COVERING INDEX sqlite_autoindex_ltc_1')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 3')
(3, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')

Ich habe alle Arten von einzelnen und kombinierten Indices ausprobiert, aber es scheint keinen Unterschied zu machen.

Das Problem scheint die doppelte Reihenfolge selbst zu sein, nicht die doppelte Auswahl: Selbst ein nutzloses doppeltes ORDER BY erzeugt einen temporären B-Tree (obwohl in diesem Fall das Ergebnis sofort ist):

EXPLAIN QUERY PLAN SELECT  ltc.nr
FROM ltc
WHERE 
ltc.lemId = 716 
ORDER BY
    ltc.nr,
    ltc.nr
LIMIT 10;

(0, 0, 0, u'SCAN TABLE ltc')
(0, 0, 0, u'USE TEMP B-TREE FOR ORDER BY')

BeimSQLite ORDER BY-Leistungsproblem Es wird gesagt, dass Abfragen nicht nach Indecies aus verschiedenen Tabellen sortiert werden können. Ist das hier das Problem? Gibt es einen Ausweg? Handelt es sich um eine SQLite-spezifische Einschränkung oder tun dies alle SQL-Systeme?

BEARBEITEN:

Nach dem Hinzufügen des Index, wie von CL vorgeschlagen, bleibt das Leistungsproblem bestehen. Nehmen Sie als Beispiel eine umfassendere Abfrage mit vier Suchbegriffen:

select  l0.nr,l1.nr,l2.nr,l3.nr
    from ltc as l0, ltc as l1, ltc as l2, ltc as l3 

    where 
        l0.lemId in (select rowid from lems where lems.lem = "catch" )
        and l1.lemId in (select rowid from lems where lems.lem = "cause" )
        and l2.lemId in (select rowid from lems where lems.lem = "score" )
        and l3.lemId in (select rowid from lems where lems.lem = "guest" )

    order by
        l0.nr asc

    LIMIT 10;

gibt diese Erklärung:

(0, 0, 0, u'SEARCH TABLE ltc AS l0 USING INDEX lid (lemId=?)')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 1')
(1, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 1, 1, u'SEARCH TABLE ltc AS l1 USING INDEX lid (lemId=?)')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 2')
(2, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 2, 2, u'SEARCH TABLE ltc AS l2 USING INDEX lid (lemId=?)')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 3')
(3, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 3, 3, u'SEARCH TABLE ltc AS l3 USING INDEX lid (lemId=?)')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 4')
(4, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 0, 0, u'USE TEMP B-TREE FOR ORDER BY')

(Keine vollständigen Scans mehr.)

aber: zeit: 388 sekunden !!!

beim entfernen derorder byIch bekomme genau die gleiche Erklärung abzüglich des letzten temporären B-Baums!

Zeit: 0,00025 Sekunden !!!

Diese Abfrage entspricht einer Art Join. Ich kann die Abfrage auch als (inneren) Join (mit Bedingungen) darstellen. Dies kann der Grund sein, warum die Zeit mit der Anzahl der Suchbegriffe exponentiell zu wachsen scheint: {1 Suchbegriff: 0,08 Sekunden, 2: 0,5, 3: 3, 4: 9, 5: 116, ...} Aber irgendwie Ich verstehe nicht ganz, warum die Datenbank nicht einfach den Index für die Datenbank verwenden kannnr zu sortierende Spalte. Immerhin sind es nur eine Menge Ergebnisse, die jeweils enthaltennr, die bestellt werden müssen.

Wie von CL vorgeschlagen, habe ich das zugrunde liegende Problem in eine neue Frage gestellt:Auswählen von Zeilentupeln aus einer SQLite-Tabelle und effizientes Sortieren der Tupel