Hello again,
Thanks for the responses, here is some additional information.
> Is it hanging, or is it crashing? Your statement of the problem is unclear
> on this point.
It depends on the version. 3.8.0 gives an access violation (a crash) the other
versions I have tried hang, in that the call to step "never" returns, where
"never" means "takes longer than an hour".
As requested I have generated some explain plans, and I have had a go at making
a stand-alone example program but it does not show the problem - I suspect
because the distribution of data values is different and results in a different
plan (I do have evidence for this view). Read on for the details...
Firstly, here is a C++11 example application. This works perfectly with 3.6.16
and 3.17.0. note I had to change the close call at the end from
sqlite_close_v2() to sqlite_close in order for it to work with 3.6.16 - I don't
expect this changes the results.
#include "sqlite3.h"
#include <iostream>
#include <vector>
const std::vector<std::string> query_texts =
{
R"(CREATE TABLE ABUNDANCE(AbundanceId INTEGER PRIMARY KEY NOT NULL,
AbundanceId0 INTEGER, AbundanceId1 INTEGER, AbundanceId2 INTEGER, AbundanceId3
INTEGER, AbundanceId4 INTEGER, AbundanceId5 INTEGER, AbundanceId6 INTEGER,
AbundanceId7 INTEGER, AbundanceId8 INTEGER, SetAbundanceId0 INTEGER,
SetAbundanceId1 INTEGER, SetAbundanceId2 INTEGER))",
R"(CREATE UNIQUE INDEX ABUNDANCE_UNIQUE_DATA_INDEX ON ABUNDANCE(AbundanceId0,
AbundanceId1, AbundanceId2, AbundanceId3, AbundanceId4, AbundanceId5,
AbundanceId6, AbundanceId7, AbundanceId8, SetAbundanceId0, SetAbundanceId1,
SetAbundanceId2))",
R"(CREATE INDEX ABUNDANCE_INDEX ON ABUNDANCE(AbundanceId0, AbundanceId1,
AbundanceId2, AbundanceId3, AbundanceId4, AbundanceId5, AbundanceId6,
AbundanceId7, AbundanceId8))",
R"(CREATE INDEX ABUNDANCE_SetAbundanceId0 ON ABUNDANCE(SetAbundanceId0))",
R"(CREATE INDEX ABUNDANCE_SetAbundanceId1 ON ABUNDANCE(SetAbundanceId1))",
R"(CREATE INDEX ABUNDANCE_SetAbundanceId2 ON ABUNDANCE(SetAbundanceId2))",
R"( CREATE TABLE ABUNDANCE_0(AbundanceId0 INTEGER PRIMARY KEY NOT NULL, Col6
INTEGER, Col16 INTEGER, Col19 INTEGER, Col3l INTEGER, Col14 INTEGER, Col15
INTEGER, Col3 INTEGER, Col8 INTEGER))",
R"( CREATE UNIQUE INDEX ABUNDANCE_0_UNIQUE_DATA_INDEX ON ABUNDANCE_0(Col6,
Col16, Col19, Col3l, Col14, Col15, Col3, Col8))",
R"( CREATE TABLE ABUNDANCE_1(AbundanceId1 INTEGER PRIMARY KEY NOT NULL,
Col16t INTEGER, Col26n INTEGER, Col13o INTEGER, Col9 INTEGER, Col18u INTEGER,
Col14i INTEGER, Col3o INTEGER, Col1r INTEGER, Col19i INTEGER, Col6e INTEGER,
Col1 INTEGER, Col3u INTEGER, Col19n INTEGER, Col23 INTEGER, Col13n INTEGER,
Col16d INTEGER))",
R"( CREATE UNIQUE INDEX ABUNDANCE_1_UNIQUE_DATA_INDEX ON ABUNDANCE_1(Col16t,
Col26n, Col13o, Col9, Col18u, Col14i, Col3o, Col1r, Col19i, Col6e, Col1, Col3u,
Col19n, Col23, Col13n, Col16d))",
R"( CREATE TABLE ABUNDANCE_2(AbundanceId2 INTEGER PRIMARY KEY NOT NULL,
Col15s INTEGER, Col26r INTEGER, Col22 INTEGER, Col9r INTEGER, Col0l INTEGER,
Col0u INTEGER, Col11 INTEGER, Col3d INTEGER, Col20i INTEGER, Col14a INTEGER,
Col12i INTEGER, Col3r INTEGER, Col19e INTEGER, Col18e INTEGER, Col0g INTEGER,
Col18h INTEGER))",
R"( CREATE UNIQUE INDEX ABUNDANCE_2_UNIQUE_DATA_INDEX ON ABUNDANCE_2(Col15s,
Col26r, Col22, Col9r, Col0l, Col0u, Col11, Col3d, Col20i, Col14a, Col12i,
Col3r, Col19e, Col18e, Col0g, Col18h))",
R"( CREATE TABLE ABUNDANCE_3(AbundanceId3 INTEGER PRIMARY KEY NOT NULL,
Col14b INTEGER, Col1i INTEGER, Col14d INTEGER, Col25b INTEGER, Col19m INTEGER,
Col9n INTEGER, Col13g INTEGER, Col16b INTEGER, Col21 INTEGER, Col7e INTEGER,
Col20e INTEGER, Col7a INTEGER, Col8g INTEGER, Col0s INTEGER, Col19b INTEGER,
Col12a INTEGER))",
R"( CREATE UNIQUE INDEX ABUNDANCE_3_UNIQUE_DATA_INDEX ON ABUNDANCE_3(Col14b,
Col1i, Col14d, Col25b, Col19m, Col9n, Col13g, Col16b, Col21, Col7e, Col20e,
Col7a, Col8g, Col0s, Col19b, Col12a))",
R"( CREATE TABLE ABUNDANCE_4(AbundanceId4 INTEGER PRIMARY KEY NOT NULL,
Col4y INTEGER, Col19r INTEGER, Col18b INTEGER, Col8f INTEGER, Col20b INTEGER,
Col16r INTEGER, Col3e INTEGER, Col5r INTEGER, Col20l INTEGER, Col3s INTEGER,
Col1a INTEGER, Col7d INTEGER, Col25 INTEGER, Col3a INTEGER, Col5u INTEGER,
Col20a INTEGER))",
R"( CREATE UNIQUE INDEX ABUNDANCE_4_UNIQUE_DATA_INDEX ON ABUNDANCE_4(Col4y,
Col19r, Col18b, Col8f, Col20b, Col16r, Col3e, Col5r, Col20l, Col3s, Col1a,
Col7d, Col25, Col3a, Col5u, Col20a))",
R"( CREATE TABLE ABUNDANCE_5(AbundanceId5 INTEGER PRIMARY KEY NOT NULL,
Col3m INTEGER, Col16a INTEGER, Col11r INTEGER, Col0m INTEGER, Col0r INTEGER,
Col24e INTEGER, Col16u INTEGER, Col4 INTEGER, Col14p INTEGER, Col20m INTEGER,
Col1e INTEGER, Col20h INTEGER, Col8o INTEGER, Col19c INTEGER, Col12u INTEGER,
Col20c INTEGER))",
R"( CREATE UNIQUE INDEX ABUNDANCE_5_UNIQUE_DATA_INDEX ON ABUNDANCE_5(Col3m,
Col16a, Col11r, Col0m, Col0r, Col24e, Col16u, Col4, Col14p, Col20m, Col1e,
Col20h, Col8o, Col19c, Col12u, Col20c))",
R"( CREATE TABLE ABUNDANCE_6(AbundanceId6 INTEGER PRIMARY KEY NOT NULL,
Col21nknown INTEGER, Col0t INTEGER, Col5s INTEGER, Col6m INTEGER, Col6r
INTEGER, Col12r INTEGER, Col13d INTEGER, Col14e INTEGER, Col14o INTEGER, Col16o
INTEGER, Col18a INTEGER, Col18n INTEGER, Col0c INTEGER, Col1k INTEGER, Col3f
INTEGER, Col16m INTEGER))",
R"( CREATE UNIQUE INDEX ABUNDANCE_6_UNIQUE_DATA_INDEX ON
ABUNDANCE_6(Col21nknown, Col0t, Col5s, Col6m, Col6r, Col12r, Col13d, Col14e,
Col14o, Col16o, Col18a, Col18n, Col0c, Col1k, Col3f, Col16m))",
R"( CREATE TABLE ABUNDANCE_7(AbundanceId7 INTEGER PRIMARY KEY NOT NULL,
Col8e INTEGER, Col1h INTEGER, Col3n INTEGER, Col4b INTEGER, Col4s INTEGER,
Col5s INTEGER, Col6l INTEGER, Col6m INTEGER, Col6r INTEGER, Col8s INTEGER,
Col12r INTEGER, Col12v INTEGER, Col13c INTEGER, Col13d INTEGER, Col13t
INTEGER))",
R"( CREATE UNIQUE INDEX ABUNDANCE_7_UNIQUE_DATA_INDEX ON ABUNDANCE_7(Col8e,
Col1h, Col3n, Col4b, Col4s, Col5s, Col6l, Col6m, Col6r, Col8s, Col12r, Col12v,
Col13c, Col13d, Col13t))",
R"( CREATE TABLE ABUNDANCE_8(AbundanceId8 INTEGER PRIMARY KEY NOT NULL,
Col14h INTEGER, Col14o INTEGER, Col15g INTEGER, Col16o INTEGER, Col18a INTEGER,
Col18f INTEGER, Col18g INTEGER, Col18n INTEGER, Col19g INTEGER, Col20s
INTEGER))",
R"( CREATE UNIQUE INDEX ABUNDANCE_8_UNIQUE_DATA_INDEX ON ABUNDANCE_8(Col14h,
Col14o, Col15g, Col16o, Col18a, Col18f, Col18g, Col18n, Col19g, Col20s))",
R"( CREATE TABLE ABUNDANCE_SETS_0(SetAbundanceId0 INTEGER PRIMARY KEY NOT
NULL, Set4B INTEGER, Set5A INTEGER, Set6A INTEGER, SetX1 INTEGER))",
R"( CREATE UNIQUE INDEX ABUNDANCE_SETS_0_UNIQUE_DATA_INDEX ON
ABUNDANCE_SETS_0(Set4B, Set5A, Set6A, SetX1))",
R"( CREATE TABLE ABUNDANCE_SETS_1(SetAbundanceId1 INTEGER PRIMARY KEY NOT
NULL, Set5B INTEGER, Set7B INTEGER, Set8Y INTEGER, Set1A INTEGER, Set6B
INTEGER, Set8X INTEGER, Set4A INTEGER, Set3A INTEGER, Set2R INTEGER, Set7A
INTEGER, SetTR INTEGER, Set4M INTEGER))",
R"( CREATE UNIQUE INDEX ABUNDANCE_SETS_1_UNIQUE_DATA_INDEX ON
ABUNDANCE_SETS_1(Set5B, Set7B, Set8Y, Set1A, Set6B, Set8X, Set4A, Set3A, Set2R,
Set7A, SetTR, Set4M))",
R"( CREATE TABLE ABUNDANCE_SETS_2(SetAbundanceId2 INTEGER PRIMARY KEY NOT
NULL, Set8A INTEGER, SetAN INTEGER, Set3B INTEGER, Set2A INTEGER, SetLN
INTEGER, Set2B INTEGER, Set2M INTEGER, Set8Z INTEGER, Set1B INTEGER, Set1M
INTEGER, Set3R INTEGER, Set3M INTEGER, Set8B INTEGER, Set1R INTEGER))",
R"( CREATE UNIQUE INDEX ABUNDANCE_SETS_2_UNIQUE_DATA_INDEX ON
ABUNDANCE_SETS_2(Set8A, SetAN, Set3B, Set2A, SetLN, Set2B, Set2M, Set8Z, Set1B,
Set1M, Set3R, Set3M, Set8B, Set1R))",
};
const std::string problem_query =
R"( SELECT AbundanceId FROM[ABUNDANCE]
WHERE
ABUNDANCE.AbundanceId0 in(
SELECT AbundanceId0 FROM ABUNDANCE_0 WHERE
Col6 = 0 AND Col16 = 0 AND Col19 = 0 AND Col3l = 0 AND Col14 = 0 AND
Col15 = 0 AND Col3 = 0
)
AND
ABUNDANCE.AbundanceId1 in(
SELECT AbundanceId1 FROM ABUNDANCE_1 WHERE
Col16t = 0 AND Col26n = 0 AND Col13o = 0 AND Col9 = 0 AND Col18u = 0
AND Col14i = 0 AND Col3o = 0 AND Col1r = 0 AND Col19i = 0 AND Col6e = 0 AND
Col1 = 0 AND Col3u = 0 AND Col19n = 0 AND Col23 = 0 AND Col13n = 0 AND Col16d = 0
)
AND
ABUNDANCE.AbundanceId2 in(
SELECT AbundanceId2 FROM ABUNDANCE_2 WHERE
Col15s = 0 AND Col26r = 0 AND Col22 = 0 AND Col9r = 0 AND Col0l = 0 AND
Col0u = 0 AND Col3d = 0 AND Col20i = 0 AND Col3r = 0 AND Col19e = 0 AND Col18e
= 0 AND Col0g = 0 AND Col18h = 0
)
AND
ABUNDANCE.AbundanceId3 in(
SELECT AbundanceId3 FROM ABUNDANCE_3 WHERE
Col14b = 0 AND Col1i = 0 AND Col14d = 0 AND Col25b = 0 AND Col19m = 0
AND Col9n = 0 AND Col13g = 0 AND Col16b = 0 AND Col21 = 0 AND Col7e = 0 AND
Col20e = 0 AND Col7a = 0 AND Col8g = 0 AND Col0s = 0 AND Col19b = 0 AND Col12a
= 0
)
AND
ABUNDANCE.AbundanceId4 in(
SELECT AbundanceId4 FROM ABUNDANCE_4 WHERE
Col4y = 0 AND Col19r = 0 AND Col8f = 0 AND Col20b = 0 AND Col16r = 0
AND Col3e = 0 AND Col5r = 0 AND Col20l = 0 AND Col1a = 0 AND Col7d = 0 AND
Col25 = 0 AND Col3a = 0 AND Col5u = 0 AND Col20a = 0
)
AND
ABUNDANCE.AbundanceId5 in(
SELECT AbundanceId5 FROM ABUNDANCE_5 WHERE
Col3m = 0 AND Col16a = 0 AND Col11r = 0 AND Col0m = 0 AND Col0r = 0 AND
Col24e = 0 AND Col16u = 0 AND Col14p = 0 AND Col20m = 0 AND Col1e = 0 AND
Col20h = 0 AND Col8o = 0 AND Col19c = 0 AND Col12u = 0 AND Col20c = 0
)
AND
ABUNDANCE.AbundanceId6 in(
SELECT AbundanceId6 FROM ABUNDANCE_6 WHERE
Col21nknown = 0 AND Col0t = 0 AND Col5s = 0 AND Col6m = 0 AND Col12r =
0 AND Col13d = 0 AND Col14e = 0 AND Col14o = 0 AND Col16o = 0 AND Col18a = 0
AND Col18n = 0 AND Col0c = 0 AND Col1k = 0 AND Col3f = 0 AND Col16m = 0
)
AND
ABUNDANCE.AbundanceId7 in(
SELECT AbundanceId7 FROM ABUNDANCE_7 WHERE
Col8e = 0 AND Col1h = 0 AND Col3n = 0 AND Col4b = 0 AND Col4s = 0 AND
Col5s = 0 AND Col6l = 0 AND Col6m = 0 AND Col8s = 0 AND Col12r = 0 AND Col12v =
0 AND Col13c = 0 AND Col13d = 0 AND Col13t = 0
)
AND
ABUNDANCE.AbundanceId8 in(
SELECT AbundanceId8 FROM ABUNDANCE_8 WHERE
Col14h = 0 AND Col14o = 0 AND Col15g = 0 AND Col16o = 0 AND Col18a = 0
AND Col18f = 0 AND Col18g = 0 AND Col18n = 0 AND Col19g = 0 AND Col20s = 0
)
AND
ABUNDANCE.SetAbundanceId0 in(
SELECT SetAbundanceId0 FROM ABUNDANCE_SETS_0 WHERE
SetX1 >= 1 AND SetX1 <= 9999
)
AND
ABUNDANCE.SetAbundanceId1 in(
SELECT SetAbundanceId1 FROM ABUNDANCE_SETS_1 WHERE
Set1A >= 1 AND Set1A <= 9999
)
)";
void run_query(
sqlite3* sqlite_connection,
const std::string& query)
{
sqlite3_stmt* statement = nullptr;
const char* sql_tail = nullptr;
int ret = sqlite3_prepare_v2(sqlite_connection, query.c_str(),
query.size(), &statement, &sql_tail);
if (ret != SQLITE_OK && ret != SQLITE_DONE)
std::cout << "QUERY RETURNED: " << ret << std::endl;
while (sqlite3_step(statement) == SQLITE_ROW)
{
size_t count = sqlite3_column_count(statement);
for (size_t i = 0; i < count; ++i)
{
const char *o = (const char *)sqlite3_column_text(statement, i);
std::cout << o << "|";
}
std::cout << std::endl;
}
sqlite3_finalize(statement);
}
int run_single_insert(
sqlite3* sqlite_connection,
const std::string& query,
const std::vector<int>& values)
{
sqlite3_stmt* statement = nullptr;
const char* sql_tail = nullptr;
int ret = sqlite3_prepare_v2(sqlite_connection, query.c_str(),
query.size(), &statement, &sql_tail);
if (ret != SQLITE_OK && ret != SQLITE_DONE)
std::cout << "INSERT QUERY RETURNED: " << ret;
for (int param = 0 ; param != values.size() ; ++param)
sqlite3_bind_int(statement, param + 1, values[param]);
ret = sqlite3_step(statement);
if (ret != SQLITE_OK && ret != SQLITE_DONE)
std::cout << "INSERT STEP RETURNED: " << ret << std::endl;
int id = static_cast<int>(sqlite3_last_insert_rowid(sqlite_connection));
sqlite3_finalize(statement);
return id;
}
std::vector<int> run_bulk_insert(
sqlite3* sqlite_connection,
const std::string& query,
const std::vector<int>& first_row_values,
int row_count)
{
sqlite3_stmt* statement = nullptr;
const char* sql_tail = nullptr;
int ret = sqlite3_prepare_v2(sqlite_connection, query.c_str(),
query.size(), &statement, &sql_tail);
if (ret != SQLITE_OK && ret != SQLITE_DONE)
std::cout << "INSERT QUERY RETURNED: " << ret;
std::vector<int> returned_ids;
std::vector<int> values(first_row_values);
for ( int row = 0 ; row != row_count ; ++row )
{
for (int param = 0; param != values.size(); ++param)
sqlite3_bind_int(statement, param + 1, values[param]);
ret = sqlite3_step(statement);
if (ret != SQLITE_OK && ret != SQLITE_DONE)
std::cout << "INSERT STEP RETURNED: " << ret << std::endl;
returned_ids.push_back(static_cast<int>(sqlite3_last_insert_rowid(sqlite_connection))
);
sqlite3_clear_bindings(statement);
sqlite3_reset(statement);
// first column is a count so rows are guaranteed unique
++values[0];
for (size_t i = 1; i < values.size(); ++i)
values[i] = rand() % 30;
}
sqlite3_finalize(statement);
return returned_ids;
}
void run_inserts(sqlite3* sqlite_connection)
{
struct InsertParameters
{
std::string query;
int parameter_count;
};
const std::vector< InsertParameters > subtable_inserts =
{
{ R"(INSERT INTO ABUNDANCE_0(Col6, Col16, Col19, Col3l, Col14, Col15,
Col3, Col8) VALUES(?,?,?,?,?,?,?,?) )", 8 },
{ R"(INSERT INTO ABUNDANCE_1(Col16t, Col26n, Col13o, Col9, Col18u,
Col14i, Col3o, Col1r, Col19i, Col6e, Col1, Col3u, Col19n, Col23, Col13n,
Col16d) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) )", 16 },
{ R"(INSERT INTO ABUNDANCE_2(Col15s, Col26r, Col22, Col9r, Col0l,
Col0u, Col11, Col3d, Col20i, Col14a, Col12i, Col3r, Col19e, Col18e, Col0g,
Col18h) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) )", 16 },
{ R"(INSERT INTO ABUNDANCE_3(Col14b, Col1i, Col14d, Col25b, Col19m,
Col9n, Col13g, Col16b, Col21, Col7e, Col20e, Col7a, Col8g, Col0s, Col19b,
Col12a) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) )", 16 },
{ R"(INSERT INTO ABUNDANCE_4(Col4y, Col19r, Col18b, Col8f, Col20b,
Col16r, Col3e, Col5r, Col20l, Col3s, Col1a, Col7d, Col25, Col3a, Col5u, Col20a)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) )", 16 },
{ R"(INSERT INTO ABUNDANCE_5(Col3m, Col16a, Col11r, Col0m, Col0r,
Col24e, Col16u, Col4, Col14p, Col20m, Col1e, Col20h, Col8o, Col19c, Col12u,
Col20c) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) )", 16 },
{ R"(INSERT INTO ABUNDANCE_6(Col21nknown, Col0t, Col5s, Col6m, Col6r,
Col12r, Col13d, Col14e, Col14o, Col16o, Col18a, Col18n, Col0c, Col1k, Col3f,
Col16m) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) )", 16 },
{ R"(INSERT INTO ABUNDANCE_7(Col8e, Col1h, Col3n, Col4b, Col4s, Col5s,
Col6l, Col6m, Col6r, Col8s, Col12r, Col12v, Col13c, Col13d, Col13t)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) )", 15 },
{ R"(INSERT INTO ABUNDANCE_8(Col14h, Col14o, Col15g, Col16o, Col18a,
Col18f, Col18g, Col18n, Col19g, Col20s) VALUES(?,?,?,?,?,?,?,?,?,?) )", 10 },
{ R"(INSERT INTO ABUNDANCE_SETS_0(Set4B, Set5A, Set6A, SetX1)
VALUES(?,?,?,?) )", 4 },
{ R"(INSERT INTO ABUNDANCE_SETS_1(Set5B, Set7B, Set8Y, Set1A, Set6B,
Set8X, Set4A, Set3A, Set2R, Set7A, SetTR, Set4M)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?) )", 12 },
{ R"(INSERT INTO ABUNDANCE_SETS_2(Set8A, SetAN, Set3B, Set2A, SetLN,
Set2B, Set2M, Set8Z, Set1B, Set1M, Set3R, Set3M, Set8B, Set1R)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?) )", 14 }
};
std::string main_table_insert = R"(INSERT INTO ABUNDANCE(AbundanceId0,
AbundanceId1, AbundanceId2, AbundanceId3, AbundanceId4, AbundanceId5,
AbundanceId6, AbundanceId7, AbundanceId8, SetAbundanceId0, SetAbundanceId1,
SetAbundanceId2)
VALUES(?,?,?,?,?,?,?,?,?,?,?,?) )";
const int nrows = 10;
std::vector< std::vector< int > > inserted_ids;
std::vector<int> insert_ids;
for ( const auto& subtable : subtable_inserts)
{
std::vector<int> first_row_values(subtable.parameter_count, 0);
inserted_ids.push_back(run_bulk_insert(sqlite_connection,
subtable.query, first_row_values, nrows));
std::cout << "Finished bulk insert: " << std::endl;
}
for (int row = 0; row != nrows; ++row)
{
std::vector<int> values;
for (const auto& ids : inserted_ids)
values.push_back(ids[row]);
run_single_insert(sqlite_connection, main_table_insert, values);
if (row % 10 == 0)
std::cout << "Inserted row: " << row << std::endl;
}
}
int main(int argc, char **argv)
{
const char* path = "temp_db.sqlite";
unlink(path);
sqlite3* sqlite_connection;
int ret;
ret = sqlite3_open_v2(path,
&sqlite_connection,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
0);
for (const auto& query : query_texts)
run_query(sqlite_connection, query);
run_inserts(sqlite_connection);
std::cout << "running final query" << std::endl;
run_query(sqlite_connection, problem_query);
sqlite3_close(sqlite_connection);
}
Running the above with "EXPLAIN QUERY PLAN" prepended to the text of
"problem_query" gives the following
Version 3.6.16
0|0|TABLE ABUNDANCE WITH INDEX ABUNDANCE_SetAbundanceId1|
0|0|TABLE ABUNDANCE_SETS_1|
0|0|TABLE ABUNDANCE_0 WITH INDEX ABUNDANCE_0_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_1 WITH INDEX ABUNDANCE_1_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_2 WITH INDEX ABUNDANCE_2_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_3 WITH INDEX ABUNDANCE_3_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_4 WITH INDEX ABUNDANCE_4_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_5 WITH INDEX ABUNDANCE_5_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_6 WITH INDEX ABUNDANCE_6_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_7 WITH INDEX ABUNDANCE_7_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_8 WITH INDEX ABUNDANCE_8_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_SETS_0|
Version 3.17.0
0|0|0|SEARCH TABLE ABUNDANCE USING COVERING INDEX ABUNDANCE_UNIQUE_DATA_INDEX
(AbundanceId0=? AND AbundanceId1=? AND AbundanceId2=? AND AbundanceId3=? AND
AbundanceId4=? AND AbundanceId5=? AND AbundanceId6=? AND AbundanceId7=? AND
AbundanceId8=? AND SetAbundanceId0=? AND SetAbundanceId1=?)|
0|0|0|EXECUTE LIST SUBQUERY 1|
1|0|0|SEARCH TABLE ABUNDANCE_0 USING COVERING INDEX
ABUNDANCE_0_UNIQUE_DATA_INDEX (Col6=? AND Col16=? AND Col19=? AND Col3l=? AND
Col14=? AND Col15=? AND Col3=?)|
0|0|0|EXECUTE LIST SUBQUERY 2|
2|0|0|SEARCH TABLE ABUNDANCE_1 USING COVERING INDEX
ABUNDANCE_1_UNIQUE_DATA_INDEX (Col16t=? AND Col26n=? AND Col13o=? AND Col9=?
AND Col18u=? AND Col14i=? AND Col3o=? AND Col1r=? AND Col19i=? AND Col6e=? AND
Col1=? AND Col3u=? AND Col19n=? AND Col23=? AND Col13n=? AND Col16d=?)|
0|0|0|EXECUTE LIST SUBQUERY 3|
3|0|0|SEARCH TABLE ABUNDANCE_2 USING COVERING INDEX
ABUNDANCE_2_UNIQUE_DATA_INDEX (Col15s=? AND Col26r=? AND Col22=? AND Col9r=?
AND Col0l=? AND Col0u=?)|
0|0|0|EXECUTE LIST SUBQUERY 4|
4|0|0|SEARCH TABLE ABUNDANCE_3 USING COVERING INDEX
ABUNDANCE_3_UNIQUE_DATA_INDEX (Col14b=? AND Col1i=? AND Col14d=? AND Col25b=?
AND Col19m=? AND Col9n=? AND Col13g=? AND Col16b=? AND Col21=? AND Col7e=? AND
Col20e=? AND Col7a=? AND Col8g=? AND Col0s=? AND Col19b=? AND Col12a=?)|
0|0|0|EXECUTE LIST SUBQUERY 5|
5|0|0|SEARCH TABLE ABUNDANCE_4 USING COVERING INDEX
ABUNDANCE_4_UNIQUE_DATA_INDEX (Col4y=? AND Col19r=?)|
0|0|0|EXECUTE LIST SUBQUERY 6|
6|0|0|SEARCH TABLE ABUNDANCE_5 USING COVERING INDEX
ABUNDANCE_5_UNIQUE_DATA_INDEX (Col3m=? AND Col16a=? AND Col11r=? AND Col0m=?
AND Col0r=? AND Col24e=? AND Col16u=?)|
0|0|0|EXECUTE LIST SUBQUERY 7|
7|0|0|SEARCH TABLE ABUNDANCE_6 USING COVERING INDEX
ABUNDANCE_6_UNIQUE_DATA_INDEX (Col21nknown=? AND Col0t=? AND Col5s=? AND
Col6m=?)|
0|0|0|EXECUTE LIST SUBQUERY 8|
8|0|0|SEARCH TABLE ABUNDANCE_7 USING COVERING INDEX
ABUNDANCE_7_UNIQUE_DATA_INDEX (Col8e=? AND Col1h=? AND Col3n=? AND Col4b=? AND
Col4s=? AND Col5s=? AND Col6l=? AND Col6m=?)|
0|0|0|EXECUTE LIST SUBQUERY 9|
9|0|0|SEARCH TABLE ABUNDANCE_8 USING COVERING INDEX
ABUNDANCE_8_UNIQUE_DATA_INDEX (Col14h=? AND Col14o=? AND Col15g=? AND Col16o=?
AND Col18a=? AND Col18f=? AND Col18g=? AND Col18n=? AND Col19g=? AND Col20s=?)|
0|0|0|EXECUTE LIST SUBQUERY 10|
10|0|0|SCAN TABLE ABUNDANCE_SETS_0|
0|0|0|EXECUTE LIST SUBQUERY 11|
11|0|0|SCAN TABLE ABUNDANCE_SETS_1|
By way of comparison, and because the example program does not actually show
the issue, I also ran an explain query plan on the original query in the
original database. This gives the following different result (slightly
obfuscated by hand)
Version 3.6.16 looks broadly similar to the example, except for abundance_8 at
the end. This may be due to this particular table having 100 or 1000 rows in
the example and only one or two rows in the full database.
0|0|TABLE ABUNDANCE WITH INDEX ABUNDANCE_INDEX_SetAbundanceId0
0|0|TABLE ABUNDANCE_SETS_0
0|0|TABLE ABUNDANCE_0 WITH INDEX ABUNDANCE_0_UNIQUE_DATA_INDEX
0|0|TABLE ABUNDANCE_1 WITH INDEX ABUNDANCE_1_UNIQUE_DATA_INDEX
0|0|TABLE ABUNDANCE_2 WITH INDEX ABUNDANCE_2_UNIQUE_DATA_INDEX
0|0|TABLE ABUNDANCE_3 WITH INDEX ABUNDANCE_3_UNIQUE_DATA_INDEX
0|0|TABLE ABUNDANCE_4 WITH INDEX ABUNDANCE_4_UNIQUE_DATA_INDEX
0|0|TABLE ABUNDANCE_5 WITH INDEX ABUNDANCE_5_UNIQUE_DATA_INDEX
0|0|TABLE ABUNDANCE_6 WITH INDEX ABUNDANCE_6_UNIQUE_DATA_INDEX
0|0|TABLE ABUNDANCE_7 WITH INDEX ABUNDANCE_7_UNIQUE_DATA_INDEX
0|0|TABLE ABUNDANCE_8
0|0|TABLE ABUNDANCE_SETS_1
Version 3.17.0 - which is quite different. I have edited out most of the
column names but I don't feel this changes the information. The use of "ANY"
in the final clause is quite different from before. I noted that where the
UNIQUE_DATA_INDEX in the subtables are used, it is only a subset of the columns
that are listed due to only some of them being in the WHERE of the subqueries,
so the index will need to skip some of the levels as the columns used are not
always a left prefix of the index.
0|0|0|SEARCH TABLE ABUNDANCE USING COVERING INDEX ABUNDANCE_UNIQUE_DATA_INDEX
(AbundanceId0=? AND AbundanceId1=? AND AbundanceId2=? AND AbundanceId3=? AND
AbundanceId4=? AND AbundanceId5=? AND AbundanceId6=? AND AbundanceId7=? AND
AbundanceId8=? AND SetAbundanceId0=? AND SetAbundanceId1=?)
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SEARCH TABLE ABUNDANCE_S_0 USING COVERING INDEX
ABUNDANCE_S_0_UNIQUE_DATA_INDEX ([7 columns listed])
0|0|0|EXECUTE LIST SUBQUERY 2
2|0|0|SEARCH TABLE ABUNDANCE_S_1 USING COVERING INDEX
ABUNDANCE_S_1_UNIQUE_DATA_INDEX ([16 columns listed])
0|0|0|EXECUTE LIST SUBQUERY 3
3|0|0|SEARCH TABLE ABUNDANCE_S_2 USING COVERING INDEX
ABUNDANCE_S_2_UNIQUE_DATA_INDEX ([6 columns listed])
0|0|0|EXECUTE LIST SUBQUERY 4
4|0|0|SEARCH TABLE ABUNDANCE_S_3 USING COVERING INDEX
ABUNDANCE_S_3_UNIQUE_DATA_INDEX ([16 columns listed])
0|0|0|EXECUTE LIST SUBQUERY 5
5|0|0|SEARCH TABLE ABUNDANCE_S_4 USING COVERING INDEX
ABUNDANCE_S_4_UNIQUE_DATA_INDEX ([2 columns listed])
0|0|0|EXECUTE LIST SUBQUERY 6
6|0|0|SEARCH TABLE ABUNDANCE_S_5 USING COVERING INDEX
ABUNDANCE_S_5_UNIQUE_DATA_INDEX ([7 columns listed])
0|0|0|EXECUTE LIST SUBQUERY 7
7|0|0|SCAN TABLE ABUNDANCE_S_6
0|0|0|EXECUTE LIST SUBQUERY 8
8|0|0|SEARCH TABLE ABUNDANCE_S_7 USING COVERING INDEX
ABUNDANCE_S_7_UNIQUE_DATA_INDEX ([8 columns listed])
0|0|0|EXECUTE LIST SUBQUERY 9
9|0|0|SCAN TABLE ABUNDANCE_S_8
0|0|0|EXECUTE LIST SUBQUERY 10
10|0|0|SCAN TABLE ABUNDANCE_SETS_0
0|0|0|EXECUTE LIST SUBQUERY 11
11|0|0|SEARCH TABLE ABUNDANCE_SETS_1 USING COVERING INDEX
ABUNDANCE_SETS_1_UNIQUE_DATA_INDEX (ANY(Col1) AND ANY(Col2) AND ANY(Col3) AND
Col4>? AND Col4<?)
It might help me reproduce the issue to get an idea of if the data content of
the table and index cardinality etc. are likely to have caused the above
differences in the query plans between my search on the whole database and the
stand alone example application.
Matthew
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
