Guys,
I’ve identified a detrimental query planner change between 3.7.12 and
3.8.1. It appears to be a change from the 3.8 NGQP. A query with a like where
clause with “collate nocase” now uses a full table scan instead of a covering
index. This occurs even if the case_sensitive_like pragma is left at default /
off. Later it was found removing the “collate nocase" reverted to the previous
plan using a covering index. Removing the modifier on an already case
insensitive should have been a no-op?
Test was run with downloaded sqlite binary running on OSX. The primary
table (terms) has ~1.6 million rows, the others, hundreds. Analyze was run
before queries on both versions. Original .sqlite file was created with 3.7.??
and a pristine copy was made before each test / analyze.
The query:
SELECT terms.term, terms.id, terms.type, terms.product_id FROM terms JOIN
products ON terms.product_id = products.id JOIN sub_categories ON
products.category_id = sub_categories.id WHERE terms.term LIKE 's%' collate
nocase AND sub_categories.parent_id = 89133;
3.7.12 Explain Plan
sele order from deta
---- ------------- ---- ----
0 0 0 SEARCH TABLE terms USING COVERING INDEX
term_id_type_product_id (term>? AND term<?) (~47270 rows)
0 1 1 SEARCH TABLE products USING COVERING INDEX
index_products_on_id (id=?) (~1 rows)
0 2 2 SEARCH TABLE sub_categories USING INDEX
index_sub_categories_on_id (id=?) (~1 rows)
3.8.1. Explain Plan
sele order from deta
---- ------------- ---- ----
0 0 0 SCAN TABLE terms
0 1 1 SEARCH TABLE products USING COVERING INDEX
index_products_on_id (id=?)
0 2 2 SEARCH TABLE sub_categories USING INDEX
index_sub_categories_on_id (id=?)
Important…
Removing “collate nocase” from the like clause changed the 3.8.1 plan back to
the 3.7.12 plan and restored its former performance. The case_sensitive_like
pragma was not changed and later verified to be off. Data is ASCII only and is
actually only lowercase. The programer that added the nocase clause (ok it was
me) must have been unaware / CYA / crazy.
Sorry if this is a known issue or especially if it’s my bad query /
understanding. I did go through the bug list and could not identify a similar
issue. I wonder if this is really a bug fix. Maybe unicode case insensitivity
was broken in 3.7.12 and is now correct.
Thank you so much for SQLite it is an amazing product,
—-Chris
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
