SQLite Forum

IN parameters within a query
Login

IN parameters within a query

(1.1) By Surge (999999999) on 2021-03-07 19:48:42 edited from 1.0 [link] [source]

Hi,

I'm having trouble with this query:

SELECT expr2.txt,expr2.langvar
FROM denotationx
JOIN expr ON (expr.id = denotationx.expr)
JOIN denotationx denotationx2 ON (denotationx2.meaning = denotationx.meaning)
JOIN expr expr2 ON (expr2.id = denotationx2.expr)
WHERE denotationx.expr != denotationx2.expr AND expr.langvar IN (5741,2626) AND expr2.langvar = 620
ORDER BY expr.langvar

The database is largish (6Gb, 76M+ rows). Nevertheless, when I use this query with only two parameters within IN, I get the results within a couple of secs. BUT (!) whenever I try to add even ONE more parameter within IN, the sqlite balks. Just to make sure I waited for an HOUR. Nothing, zilch, nada. A blinking cursor. Though the output should have been 30 measly rows...

Any ideas on how to fix this thing would be extremely welcome...

(2) By Richard Hipp (drh) on 2021-03-07 18:42:56 in reply to 1.0 [link] [source]

Have you tried running:

 PRAGMA analysis_limit=200;
 ANALYZE;

And then rerunning your query?

If that doesn't work, then please send us the schema of your database.

(3) By Surge (999999999) on 2021-03-07 19:37:38 in reply to 2 [link] [source]

Nope. Doesn't work.

It's an open PanLex database. If you can spare 2Gb of traffic, it's available here: https://db.panlex.org/panlex_lite.zip

The schema is: 

CREATE TABLE langvar (
    id integer PRIMARY KEY,
    lang_code text,
    var_code integer,
    uid text,
    meaning integer,
    name_expr integer,
    name_expr_txt text,
    region_expr integer,
    region_expr_txt text,
    script_expr integer,
    script_expr_txt text
);
CREATE TABLE source (
    id integer PRIMARY KEY,
    grp integer,
    label text,
    reg_date text,
    url text,
    isbn text,
    author text,
    title text,
    publisher text,
    year text,
    quality integer,
    note text,
    license text,
    ip_claim text,
    ip_claimant text,
    ip_claimant_email text
);
CREATE TABLE expr (
    id integer PRIMARY KEY,
    langvar integer,
    txt text
);
CREATE TABLE denotationx (
    meaning integer,
    source integer,
    grp integer,
    quality integer,
    expr integer,
    langvar integer
);
CREATE VIEW lv AS SELECT id as lv, lang_code as lc, var_code as vc, uid, meaning as mn, name_expr as ex, name_expr_txt as tt, region_expr as rg, region_expr_txt as rgtt, script_expr as sc, script_expr_txt as sctt FROM langvar
/* lv(lv,lc,vc,uid,mn,ex,tt,rg,rgtt,sc,sctt) */;
CREATE VIEW ex AS SELECT id as ex, langvar as lv, txt as tt FROM expr
/* ex(ex,lv,tt) */;
CREATE VIEW dnx AS SELECT meaning as mn, source as ap, grp as ui, quality as uq, expr as ex, langvar as lv FROM denotationx
/* dnx(mn,ap,ui,uq,ex,lv) */;
CREATE INDEX expr_langvar ON expr (langvar);
CREATE INDEX expr_txt_langvar ON expr (txt, langvar);
CREATE INDEX denotationx_meaning ON denotationx (meaning);
CREATE INDEX denotationx_expr ON denotationx (expr);
CREATE INDEX denotationx_langvar ON denotationx (langvar);
CREATE TABLE sqlite_stat1(tbl,idx,stat);

(4) By Keith Medcalf (kmedcalf) on 2021-03-07 20:27:50 in reply to 3 [link] [source]

Can you post a query that displays the problem you are seeing?

(5) By Surge (999999999) on 2021-03-07 21:12:15 in reply to 4 [link] [source]

The query in the original post gets me my results in about 3.5 secs. 

With IN expanded into IN (1261,1776,3003,5741) I'm waiting for a whopping 160 rows for 45 minutes and counting....

_______________________________

SELECT expr.langvar,langvar.uid,expr.txt,expr2.txt,expr2.langvar
FROM denotationx
JOIN expr ON (expr.id = denotationx.expr)
JOIN denotationx denotationx2 ON (denotationx2.meaning = denotationx.meaning)
JOIN expr expr2 ON (expr2.id = denotationx2.expr)
JOIN langvar ON (expr.langvar=langvar.id)
WHERE denotationx.expr != denotationx2.expr AND expr.langvar IN (1261,1776,3003,5741) AND expr2.langvar = 187
ORDER BY expr.langvar;

(6) By Keith Medcalf (kmedcalf) on 2021-03-07 21:44:34 in reply to 2 [link] [source]

That does not help, but if you do a full analyze then eveything works fine.

(7) By Keith Medcalf (kmedcalf) on 2021-03-07 21:49:52 in reply to 6 [link] [source]

In the working case, expr is in the outer loop. In the non-working case, the optimizer errantly puts expr2 in the outer loop.

If you either (a) do a full analyze or (b) delete the statistics tables, then the results work correctly (puts expr in the outer loop).

(8.1) By Keith Medcalf (kmedcalf) on 2021-03-07 21:56:47 edited from 8.0 in reply to 7 [link] [source]

Also, interestingly, with that database using the .expert command causes an abend (with the usual Windows "Sorry for you luck" error message). This is all with the current tip version.

(9) By Surge (999999999) on 2021-03-07 22:41:17 in reply to 7 [link] [source]

Tried doing both. No dice.

Can this issue be Windows-specific? Still using XP at home.
If not, what else can be done in this case?
Convert this thing into a different format maybe?

(10) By Keith Medcalf (kmedcalf) on 2021-03-07 22:51:35 in reply to 5 [link] [source]

After running analyze (or making sure that sqlite_stat1 and sqlite_stat4 are deleted), I get the following:

sqlite> .timer on
sqlite> .stats on
sqlite> .eqp on
sqlite> SELECT expr.langvar,langvar.uid,expr.txt,expr2.txt,expr2.langvar
   ...> FROM denotationx
   ...> JOIN expr ON (expr.id = denotationx.expr)
   ...> JOIN denotationx denotationx2 ON (denotationx2.meaning = denotationx.meaning)
   ...> JOIN expr expr2 ON (expr2.id = denotationx2.expr)
   ...> JOIN langvar ON (expr.langvar=langvar.id)
   ...> WHERE denotationx.expr != denotationx2.expr AND expr.langvar IN (1261,1776,3003,5741) AND expr2.langvar = 187
   ...> ORDER BY expr.langvar;
QUERY PLAN
|--SEARCH TABLE expr USING INDEX expr_langvar (langvar=?) (~2304 rows)
|--SEARCH TABLE langvar USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
|--SEARCH TABLE denotationx USING INDEX denotationx_expr (expr=?) (~4 rows)
|--SEARCH TABLE denotationx AS denotationx2 USING INDEX denotationx_meaning (meaning=?) (~2 rows)
`--SEARCH TABLE expr AS expr2 USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
┌─────────┬─────────┬─────────────────────────┬─────────────────────────┬─────────┐
│ langvar │   uid   │           txt           │           txt           │ langvar │
├─────────┼─────────┼─────────────────────────┼─────────────────────────┼─────────┤
│ 1261    │ roa-000 │ Agropoli                │ Agropoli                │ 187     │
│ 1261    │ roa-000 │ Aieta                   │ Aieta                   │ 187     │
│ 1261    │ roa-000 │ Aquara                  │ Aquara                  │ 187     │
│ 1261    │ roa-000 │ Birori                  │ Birori                  │ 187     │
│ 1261    │ roa-000 │ Bonarcado               │ Bonarcado               │ 187     │
│ 1261    │ roa-000 │ Bracigliano             │ Bracigliano             │ 187     │
│ 1261    │ roa-000 │ Buccino                 │ Buccino                 │ 187     │
│ 1261    │ roa-000 │ Calvanico               │ Calvanico               │ 187     │
│ 1261    │ roa-000 │ Camerota                │ Camerota                │ 187     │
│ 1261    │ roa-000 │ Casalbuono              │ Casalbuono              │ 187     │
│ 1261    │ roa-000 │ Casaletto Spartano      │ Casaletto Spartano      │ 187     │
│ 1261    │ roa-000 │ Ceraso                  │ Ceraso                  │ 187     │
│ 1261    │ roa-000 │ Cuccaro Vetere          │ Cuccaro Vetere          │ 187     │
│ 1261    │ roa-000 │ Curitiba                │ Curitiba                │ 187     │
│ 1261    │ roa-000 │ Filiano                 │ Filiano                 │ 187     │
│ 1261    │ roa-000 │ Futani                  │ Futani                  │ 187     │
│ 1261    │ roa-000 │ Ispani                  │ Ispani                  │ 187     │
│ 1261    │ roa-000 │ Laino Borgo             │ Laino Borgo             │ 187     │
│ 1261    │ roa-000 │ Massa Lubrense          │ Massa Lubrense          │ 187     │
│ 1261    │ roa-000 │ Novi Velia              │ Novi Velia              │ 187     │
│ 1261    │ roa-000 │ Nuphar                  │ Nuphar                  │ 187     │
│ 1261    │ roa-000 │ Padula                  │ Padula                  │ 187     │
│ 1261    │ roa-000 │ Palermiti               │ Palermiti               │ 187     │
│ 1261    │ roa-000 │ Papa Benedetto XVI      │ Pope Benedict XVI       │ 187     │
│ 1261    │ roa-000 │ Polla                   │ Polla                   │ 187     │
│ 1261    │ roa-000 │ Pollica                 │ Pollica                 │ 187     │
│ 1261    │ roa-000 │ Sala Consilina          │ Sala Consilina          │ 187     │
│ 1261    │ roa-000 │ Santa Marina            │ Santa Marina            │ 187     │
│ 1261    │ roa-000 │ Silvio Berlusconi       │ Silvio Berlusconi       │ 187     │
│ 1261    │ roa-000 │ Solarino                │ Solarino                │ 187     │
│ 1261    │ roa-000 │ Stazzema                │ Stazzema                │ 187     │
│ 1261    │ roa-000 │ Stio                    │ Stio                    │ 187     │
│ 1261    │ roa-000 │ Tortora                 │ Tortora                 │ 187     │
│ 1261    │ roa-000 │ Uetersen                │ Uetersen                │ 187     │
│ 1261    │ roa-000 │ Caggiano                │ Caggiano                │ 187     │
│ 1261    │ roa-000 │ Centola                 │ Centola                 │ 187     │
│ 1261    │ roa-000 │ Due Carrare             │ Due Carrare             │ 187     │
│ 1261    │ roa-000 │ Gela                    │ Gela                    │ 187     │
│ 1261    │ roa-000 │ Gianfranco Fini         │ Gianfranco Fini         │ 187     │
│ 1261    │ roa-000 │ Laurito                 │ Laurito                 │ 187     │
│ 1261    │ roa-000 │ Montoro Inferiore       │ Montoro Inferiore       │ 187     │
│ 1261    │ roa-000 │ Oliveto Citra           │ Oliveto Citra           │ 187     │
│ 1261    │ roa-000 │ Orsago                  │ Orsago                  │ 187     │
│ 1261    │ roa-000 │ Perdifumo               │ Perdifumo               │ 187     │
│ 1261    │ roa-000 │ Pertosa                 │ Pertosa                 │ 187     │
│ 1261    │ roa-000 │ Praia a Mare            │ Praia a Mare            │ 187     │
│ 1261    │ roa-000 │ Regnum Plantae          │ Plant                   │ 187     │
│ 1261    │ roa-000 │ Romano Prodi            │ Romano Prodi            │ 187     │
│ 1261    │ roa-000 │ Salerno                 │ Salerno                 │ 187     │
│ 1261    │ roa-000 │ Sant'Angelo a Fasanella │ Sant'Angelo a Fasanella │ 187     │
│ 1261    │ roa-000 │ Sapri                   │ Sapri                   │ 187     │
│ 1261    │ roa-000 │ Sassano                 │ Sassano                 │ 187     │
│ 1261    │ roa-000 │ Sessa Cilento           │ Sessa Cilento           │ 187     │
│ 1261    │ roa-000 │ Tarde                   │ Taranto                 │ 187     │
│ 1261    │ roa-000 │ Tortolì                 │ Tortolì                 │ 187     │
│ 1261    │ roa-000 │ Voltaire                │ Voltaire                │ 187     │
│ 1261    │ roa-000 │ Alfano                  │ Alfano                  │ 187     │
│ 1261    │ roa-000 │ Amendolara              │ Amendolara              │ 187     │
│ 1261    │ roa-000 │ Arzano                  │ Arzano                  │ 187     │
│ 1261    │ roa-000 │ Ascea                   │ Ascea                   │ 187     │
│ 1261    │ roa-000 │ Auletta                 │ Auletta                 │ 187     │
│ 1261    │ roa-000 │ Baronissi               │ Baronissi               │ 187     │
│ 1261    │ roa-000 │ Calabritto              │ Calabritto              │ 187     │
│ 1261    │ roa-000 │ Caselle in Pittari      │ Caselle in Pittari      │ 187     │
│ 1261    │ roa-000 │ Catalao                 │ Catalao                 │ 187     │
│ 1261    │ roa-000 │ Colico                  │ Colico                  │ 187     │
│ 1261    │ roa-000 │ Controne                │ Controne                │ 187     │
│ 1261    │ roa-000 │ Denis Diderot           │ Denis Diderot           │ 187     │
│ 1261    │ roa-000 │ Felitto                 │ Felitto                 │ 187     │
│ 1261    │ roa-000 │ Giffoni Sei Casali      │ Giffoni Sei Casali      │ 187     │
│ 1261    │ roa-000 │ Giffoni Valle Piana     │ Giffoni Valle Piana     │ 187     │
│ 1261    │ roa-000 │ Ginosa                  │ Ginosa                  │ 187     │
│ 1261    │ roa-000 │ Kurów                   │ Kurów                   │ 187     │
│ 1261    │ roa-000 │ Massafra                │ Massafra                │ 187     │
│ 1261    │ roa-000 │ Minervino Murge         │ Minervino Murge         │ 187     │
│ 1261    │ roa-000 │ Moncalieri              │ Moncalieri              │ 187     │
│ 1261    │ roa-000 │ Pontecagnano Faiano     │ Pontecagnano Faiano     │ 187     │
│ 1261    │ roa-000 │ Poznan                  │ Poznan                  │ 187     │
│ 1261    │ roa-000 │ Quentin Tarantino       │ Quentin Tarantino       │ 187     │
│ 1261    │ roa-000 │ Rofrano                 │ Rofrano                 │ 187     │
│ 1261    │ roa-000 │ Salènde                 │ Salento                 │ 187     │
│ 1261    │ roa-000 │ Teggiano                │ Teggiano                │ 187     │
│ 1261    │ roa-000 │ Torchiara               │ Torchiara               │ 187     │
│ 1261    │ roa-000 │ Avë Marije              │ Hail Mary               │ 187     │
│ 1261    │ roa-000 │ Angri                   │ Angri                   │ 187     │
│ 1261    │ roa-000 │ Caraglio                │ Caraglio                │ 187     │
│ 1261    │ roa-000 │ Castelcivita            │ Castelcivita            │ 187     │
│ 1261    │ roa-000 │ Colliano                │ Colliano                │ 187     │
│ 1261    │ roa-000 │ Fisciano                │ Fisciano                │ 187     │
│ 1261    │ roa-000 │ Homo neanderthalensis   │ Neanderthal             │ 187     │
│ 1261    │ roa-000 │ Italie                  │ Italy                   │ 187     │
│ 1261    │ roa-000 │ Jean-Jacques Rousseau   │ Jean-Jacques Rousseau   │ 187     │
│ 1261    │ roa-000 │ Mercato San Severino    │ Mercato San Severino    │ 187     │
│ 1261    │ roa-000 │ Montano Antilia         │ Montano Antilia         │ 187     │
│ 1261    │ roa-000 │ Montecorice             │ Montecorice             │ 187     │
│ 1261    │ roa-000 │ Nardò                   │ Nardò                   │ 187     │
│ 1261    │ roa-000 │ Pellezzano              │ Pellezzano              │ 187     │
│ 1261    │ roa-000 │ Piaggine                │ Piaggine                │ 187     │
│ 1261    │ roa-000 │ Piombino                │ Piombino                │ 187     │
│ 1261    │ roa-000 │ Postiglione             │ Postiglione             │ 187     │
│ 1261    │ roa-000 │ Ricigliano              │ Ricigliano              │ 187     │
│ 1261    │ roa-000 │ Rivello                 │ Rivello                 │ 187     │
│ 1261    │ roa-000 │ San Gregorio Magno      │ San Gregorio Magno      │ 187     │
│ 1261    │ roa-000 │ Sarno                   │ Sarno                   │ 187     │
│ 1261    │ roa-000 │ Serramezzana            │ Serramezzana            │ 187     │
│ 1261    │ roa-000 │ Siano                   │ Siano                   │ 187     │
│ 1261    │ roa-000 │ Sirignano               │ Sirignano               │ 187     │
│ 1261    │ roa-000 │ Torraca                 │ Torraca                 │ 187     │
│ 1261    │ roa-000 │ Tortorella              │ Tortorella              │ 187     │
│ 1261    │ roa-000 │ Torun                   │ Torun                   │ 187     │
│ 1261    │ roa-000 │ Lódz                    │ Lódz                    │ 187     │
│ 1261    │ roa-000 │ Bari                    │ Bari                    │ 187     │
│ 1261    │ roa-000 │ Breinigerberg           │ Breinigerberg           │ 187     │
│ 1261    │ roa-000 │ Brindisi                │ Brindisi                │ 187     │
│ 1261    │ roa-000 │ Cannalonga              │ Cannalonga              │ 187     │
│ 1261    │ roa-000 │ Capitignano             │ Capitignano             │ 187     │
│ 1261    │ roa-000 │ Castellabate            │ Castellabate            │ 187     │
│ 1261    │ roa-000 │ Crotone                 │ Crotone                 │ 187     │
│ 1261    │ roa-000 │ Lauria                  │ Lauria                  │ 187     │
│ 1261    │ roa-000 │ Laurino                 │ Laurino                 │ 187     │
│ 1261    │ roa-000 │ Lonigo                  │ Lonigo                  │ 187     │
│ 1261    │ roa-000 │ Maratea                 │ Maratea                 │ 187     │
│ 1261    │ roa-000 │ Mediaset                │ Mediaset                │ 187     │
│ 1261    │ roa-000 │ Monguzzo                │ Monguzzo                │ 187     │
│ 1261    │ roa-000 │ Montoro Superiore       │ Montoro Superiore       │ 187     │
│ 1261    │ roa-000 │ Nanto                   │ Nanto                   │ 187     │
│ 1261    │ roa-000 │ Portoferraio            │ Portoferraio            │ 187     │
│ 1261    │ roa-000 │ Prali                   │ Prali                   │ 187     │
│ 1261    │ roa-000 │ Sacco                   │ Sacco                   │ 187     │
│ 1261    │ roa-000 │ San Giuliano Terme      │ San Giuliano Terme      │ 187     │
│ 1261    │ roa-000 │ San Rufo                │ San Rufo                │ 187     │
│ 1261    │ roa-000 │ Senerchia               │ Senerchia               │ 187     │
│ 1261    │ roa-000 │ Torre Annunziata        │ Torre Annunziata        │ 187     │
│ 1261    │ roa-000 │ Trentinara              │ Trentinara              │ 187     │
│ 1261    │ roa-000 │ 'ngeclopedije           │ Encyclopedia            │ 187     │
│ 3003    │ rng-000 │ -ñwe                    │ one                     │ 187     │
│ 3003    │ rng-000 │ -birji                  │ two                     │ 187     │
│ 3003    │ rng-000 │ -rjarju                 │ three                   │ 187     │
│ 3003    │ rng-000 │ mune                    │ four                    │ 187     │
│ 3003    │ rng-000 │ ntlhanu                 │ five                    │ 187     │
│ 3003    │ rng-000 │ ntlhanu na xiñwe        │ six                     │ 187     │
│ 3003    │ rng-000 │ ntlhanu na bsibirji     │ seven                   │ 187     │
│ 3003    │ rng-000 │ ntlhanu na bsirjarju    │ eight                   │ 187     │
│ 3003    │ rng-000 │ ntlhanu na mune         │ nine                    │ 187     │
│ 3003    │ rng-000 │ khume                   │ ten                     │ 187     │
│ 5741    │ yuk-004 │ bowik                   │ one                     │ 187     │
│ 5741    │ yuk-004 │ opik                    │ two                     │ 187     │
│ 5741    │ yuk-004 │ molmik                  │ three                   │ 187     │
│ 5741    │ yuk-004 │ hilkilópik              │ four                    │ 187     │
│ 5741    │ yuk-004 │ powbát                  │ five                    │ 187     │
│ 5741    │ yuk-004 │ powtít                  │ six                     │ 187     │
│ 5741    │ yuk-004 │ ó?edo                   │ seven                   │ 187     │
│ 5741    │ yuk-004 │ mólmetit                │ eight                   │ 187     │
│ 5741    │ yuk-004 │ hilkilópetit            │ nine                    │ 187     │
│ 5741    │ yuk-004 │ bo:bátedit              │ ten                     │ 187     │
└─────────┴─────────┴─────────────────────────┴─────────────────────────┴─────────┘
Memory Used:                         30880088 (max 30910304) bytes
Number of Outstanding Allocations:   9481 (max 9569)
Number of Pcache Overflow Bytes:     30580008 (max 30596928) bytes
Largest Allocation:                  65544 bytes
Largest Pcache Allocation:           4360 bytes
Lookaside Slots Used:                41 (max 123)
Successful lookaside attempts:       2170
Lookaside failures due to size:      36
Lookaside failures due to OOM:       381
Pager Heap Usage:                    30465968 bytes
Page cache hits:                     984127
Page cache misses:                   7008
Page cache writes:                   0
Page cache spills:                   0
Schema Heap Usage:                   123896 bytes
Statement Heap/Lookaside Usage:      16704 bytes
Fullscan Steps:                      0
Sort Operations:                     0
Autoindex Inserts:                   0
Virtual Machine Steps:               1227159
Reprepare operations:                0
Number of times run:                 1
Memory used by prepared stmt:        16704
Run Time: real 0.249 user 0.078125 sys 0.046875
sqlite>

(which is 155 rows)

(11) By Keith Medcalf (kmedcalf) on 2021-03-07 23:26:02 in reply to 7 [source]

Fascinating, but dropping the statistics tables is inconsistent. If there was previously good tables, then dropping them and the query still works, but if you close and re-open the database, it does not. Almost like the statistics are used even though they have been dropped. BTW, you need analysis_limit=45000 "sufficiently correct" statistics generated.

(12) By Keith Medcalf (kmedcalf) on 2021-03-07 23:27:07 in reply to 9 [link] [source]

OS is likely irrelevant. What version of SQLite3 are you using?

(13) By Keith Medcalf (kmedcalf) on 2021-03-07 23:58:33 in reply to 12 [link] [source]

3.33.0 (at least) and later show the bad behaviour. The upcoming 3.35 version appears to work properly once the statistics are correct ...

(14.1) By Surge (999999999) on 2021-03-08 12:04:52 edited from 14.0 in reply to 13 [link] [source]

Downloaded a whole bunch of versions. As early as 3.19.x. Same mess every time.
I keep wondering how whoever compiled this thing works with it.
Hopefully, new version will deal with all these issues.

Is there a compiled binary for the upcoming 3.35 version?

(15) By Larry Brasfield (larrybr) on 2021-03-08 12:11:59 in reply to 14.1 [link] [source]

Binaries are published along with releases. The pre-release snapshots are code.

They are readily made into binaries.

(16) By anonymous on 2021-03-08 12:35:58 in reply to 9 [link] [source]

Workaround: Lock out index usage and reordering of table linking. By changing the query from WHERE denotationx.expr! = Denotationx2.expr AND expr.langvar IN (5741,2626,33) AND expr2.langvar = 620 to WHERE denotationx.expr! = Denotationx2.expr AND expr.langvar IN (5741,2626,33) AND +expr2.langvar = 620

(17) By Surge (999999999) on 2021-03-09 05:38:27 in reply to 16 [link] [source]

You're my champion! Worked like a charm. Cannot thank you enough.

(18) By Dan Kennedy (dan) on 2021-03-09 11:16:29 in reply to 8.1 [link] [source]

How do you reproduce this problem?

On Linux I just get:

$ ~/sqlite/bld4/sqlite3 ./panlex_lite/db.sqlite SQLite version 3.35.0 2021-03-08 17:22:01 Enter ".help" for usage hints. sqlite> .expert sqlite> SELECT expr.langvar,langvar.uid,expr.txt,expr2.txt,expr2.langvar ...> FROM denotationx ...> JOIN expr ON (expr.id = denotationx.expr) ...> JOIN denotationx denotationx2 ON (denotationx2.meaning = denotationx.meaning) ...> JOIN expr expr2 ON (expr2.id = denotationx2.expr) ...> JOIN langvar ON (expr.langvar=langvar.id) ...> WHERE denotationx.expr != denotationx2.expr AND expr.langvar IN (1261,1776,3003,5741) AND expr2.langvar = 187 ...> ORDER BY expr.langvar; CREATE INDEX denotationx_idx_761331ba ON denotationx(meaning, expr); . SEARCH TABLE expr AS expr2 USING INDEX expr_langvar (langvar=?) SEARCH TABLE denotationx AS denotationx2 USING INDEX denotationx_expr (expr=?) SEARCH TABLE denotationx USING COVERING INDEX denotationx_idx_761331ba (meaning=?) SEARCH TABLE expr USING INTEGER PRIMARY KEY (rowid=?) SEARCH TABLE langvar USING INTEGER PRIMARY KEY (rowid=?) USE TEMP B-TREE FOR ORDER BY

Cheers,

Dan.