Eu tenho uma mesa com algumas centenas de milhares de linhas. (É uma tabela pré-computada que expressa a relação entre lemas de palavras e outras tabelas grandes.) Preciso fazer várias seleções para encontrar uma combinação de entradas diferentes, ou seja, preciso usar "AS" para selecionar ... de ltc como l0, ltc como l1, ltc como l2 ... classificar por ... A velocidade da consulta depende da classificação: sem classificação, são alguns milissegundos, com a classificação, pode demorar alguns minutos. Isso se deve, até onde eu sei, à B-Tree temporária que o Sqlite cria para classificação, mesmo que eu tenha um índice na coluna classificada "nr". Não entendo por que o Sqlite não usa esse índice.

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)

Aqui está a versão simplificada do meu comando select:

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;

o EXPLAIN QUERY PLAN fornece:

(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')

e isso com o ORDER BY removido (ou reduzido a apenas uma colunaorder 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=?)')

Eu tentei todos os tipos de indeces únicos e combinados, mas não parece fazer nenhuma diferença.

O problema parece ser a dupla ordenação em si, não a dupla seleção: Mesmo uma dupla ORDER BY inútil cria uma árvore b temporária (mesmo que neste caso o resultado seja imediato):

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')

AtProblema de desempenho do SQLite ORDER BY diz-se que as consultas não podem ser ordenadas por indeces de tabelas diferentes. Esse é o problema aqui? Existe uma maneira de contornar? Isso é uma restrição específica do Sqlite ou todos os sistemas SQL fazem isso?

EDITAR:

Após adicionar o índice, conforme sugerido pelo CL, o problema de desempenho permanece. Como exemplo, faça uma consulta mais completa com quatro termos de pesquisa:

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;

dá esta explicação:

(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')

(sem varreduras mais completas.)

mas: tempo: 388 segundos !!!

ao remover oorder by, Recebo exatamente a mesma explicação menos a última temperatura B-tree!

tempo: 0.00025 segundos !!!

Essa consulta corresponde a algum tipo de associação. Também posso representar a consulta como uma junção (interna) (com condições). Esse pode ser o motivo pelo qual o tempo parece aumentar exponencialmente com o número de termos de pesquisa: {1 termo de pesquisa: 0,08 segundos, 2: 0,5, 3: 3, 4: 9, 5: 116, ...} Mas, de alguma forma, Não entendo por que o banco de dados não pode simplesmente usar o índice nonr coluna para classificar. Afinal, são apenas muitos resultados, cada um contendonr, que precisam ser solicitados.

Conforme sugerido pelo CL, coloquei o problema subjacente em uma nova pergunta:Selecionando tuplas de linhas de uma tabela Sqlite e classificando as tuplas eficientemente