tested with sqlite-3.7.3
Hello,
I'm trying to use the collations with BLOB because I am sorting
something else that text. For example I have some types which
can not be used as-is with SQLite because these types can be
more complex. Then I have a collation which provides the right
order. But my tables have millions of BLOB to sort and without
indexes it is very very slow.
I'm trying to use an index + collation with my BLOBs but there
is something strange. It seems that my index on the collation
is not used accordingly to EXPLAIN QUERY PLAN. But it seems that
the callback for the comparison is not called then is suggests
that the index is used.
I've made a small program with this output (please, look at
the attached C code _first_):
insert into tmp
.................
insert into lol
step for first query
0 | 1 : TABLE tmp WITH INDEX idx_foobar ORDER BY
1 | 0 : TABLE tmp USING PRIMARY KEY
step for second query
0 | 0 : TABLE lol
1 | 1 : TABLE tmp USING PRIMARY KEY
There are two queries. Both provide the same output but the second
uses ID from the table 'lol' for an INNER JOIN with the table tmp.
The first query is stupid (it uses the tmp table with the JOIN on
itself). It is just an example.
A dot [.] is printed every time that the collation is computing a
comparison. In the case, the comparisons are done with the INSERTs.
My question:
Why EXPLAIN QUERY PLAN does not show idx_foobar in the second query?
Is the index used? It seems that this index is used, because otherwise
more dot [.] should be printed between "step for first " and
"step for second ". No?
Thanks
Regards,
Mathieu SCHROETER
#include <stdio.h>
#include <string.h>
#include "sqlite3.h"
static int
cmp (void *data,
int len1, const void *d1,
int len2, const void *d2)
{
printf (".");
return -memcmp (d1, d2, 4);
}
int
main (void)
{
sqlite3 *db;
sqlite3_stmt *stmt;
char *err = NULL;
unlink ("./tmp.db");
sqlite3_open ("./tmp.db", &db);
sqlite3_create_collation (db, "foobar", SQLITE_UTF8, NULL, cmp);
sqlite3_exec (db, "CREATE TABLE tmp (id INTEGER PRIMARY KEY AUTOINCREMENT,
data BLOB);"
"CREATE TABLE lol (id INTEGER);"
"CREATE INDEX idx_foobar ON tmp (data COLLATE foobar);",
NULL, NULL, &err);
printf ("insert into tmp\n");
sqlite3_exec (db, "INSERT INTO tmp (data) values (CAST(x'0100' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0400' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0050' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'1800' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0090' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0105' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0000' AS TEXT));"
"INSERT INTO tmp (data) values (CAST(x'0001' AS TEXT));",
NULL, NULL, &err);
printf ("\ninsert into lol\n");
sqlite3_exec (db, "INSERT INTO lol (id) values (1);"
"INSERT INTO lol (id) values (2);"
"INSERT INTO lol (id) values (3);"
"INSERT INTO lol (id) values (4);"
"INSERT INTO lol (id) values (5);"
"INSERT INTO lol (id) values (6);"
"INSERT INTO lol (id) values (7);"
"INSERT INTO lol (id) values (8);",
NULL, NULL, &err);
if (err)
{
fprintf (stderr, "%s\n", err);
sqlite3_free (err);
goto out;
}
sqlite3_prepare_v2 (db, "EXPLAIN QUERY PLAN "
"SELECT data "
"FROM ("
"SELECT id AS id0 "
"FROM tmp "
") INNER JOIN tmp "
"ON id0 = tmp.id "
"ORDER BY data COLLATE foobar;",
-1, &stmt, NULL);
printf ("step for first query\n");
/*while (sqlite3_step (stmt) == SQLITE_ROW)
printf ("%10s | %10s : %s\n",
sqlite3_column_text (stmt, 0),
sqlite3_column_text (stmt, 1),
sqlite3_column_text (stmt, 2));*/
//sqlite3_finalize (stmt);
printf ("\nstep for second query\n");
sqlite3_prepare_v2 (db, "EXPLAIN QUERY PLAN "
"SELECT data "
"FROM ("
"SELECT id AS id0 "
"FROM lol "
") INNER JOIN tmp "
"ON id0 = tmp.id "
"ORDER BY data COLLATE foobar;",
-1, &stmt, NULL);
while (sqlite3_step (stmt) == SQLITE_ROW)
printf ("%10s | %10s : %s\n",
sqlite3_column_text (stmt, 0),
sqlite3_column_text (stmt, 1),
sqlite3_column_text (stmt, 2));
sqlite3_finalize (stmt);
sqlite3_close (db);
out:
unlink ("./tmp.db");
return 0;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
