Have you run ANALYZE? This may provide the QP with better cost estimates. LIKE and GLOB can sometimes be optimized (differently, because GLOB is case sensitive and requires COLLATE BINARY and LIKE by default is not and requires COLLATE NOCASE), in which case the index will be used.
See https://www.sqlite.org/optoverview.html#the_like_optimization The other comparison operators ( =, !=, > ,<, >=, <= ) can always use the index to effect a partial table scan. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von David Burgess Gesendet: Freitag, 08. Juni 2018 09:23 An: [email protected] Betreff: [EXTERNAL] [sqlite] Explain Query plan I have a table with a 16 byte column (500,000 rows), the column is indexed. When I use explain query plan in the shell, with LIKE in the WHERE clause it responds with "SCAN TABLE" whereas GLOB (or EQUALS or >= ) use the index. LIKE 'exact hit', LIKE 'matches start%' or 'matches start _' returns "SCAN TABLE" GLOB, =, >= on the other hand return --SEARCH TABLE x USING INDEX sqlite_autoindex_x_1 LIKE is also about 100 times slower. Is this expected behaviour? using 3.24 _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
