An example from my own data: explain query plan select * from categories where cat_name = ?
order | from | detail -------------------------------------------------------------------------- 0 | 0 | TABLE categories WITH INDEX sqlite_autoindex_categories_1 -------------------------------------------------------------------------- You can tell it uses the index because it says so. If you omit the "query plan" part you will get a longer more detailed result. If it has Idx* opcodes you know it is using an index to look up a record. I also get the same result (WITH INDEX) for: explain query plan select * from categories where cat_name LIKE "foo%" explain query plan select * from categories where cat_name LIKE "foo_" explain query plan select * from categories where cat_name LIKE "foo_%" explain query plan select * from categories where cat_name LIKE "foo_bar_%" Oddly enough, The following does NOT use the index: explain query plan select * from categories where cat_name LIKE "foo" I'm sure this is a bug with the optimizer, because this query is index capable. Also, be aware that the following can never use the index: explain query plan select * from categories where cat_name LIKE ? The reason it can't use the index with a bound parameter is that the statement is compiled in advance, and has to select a plan that will work for any input. Since the bound parameter might not be index capable, it has to compile a query that uses a slower plan. If the optimizer problem for strings without wildcards is a performance concern, you watch for this and build your query differently depending on whether the string has wildcards or not. BEWARE!!!! To take advantage of the index with user supplied strings you will have to build your query as a string rather than a prepared statement. MAKE SURE YOU SANITIZE YOUR INPUTS! John -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf Sent: Thursday, October 22, 2009 4:55 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Inner Join Performance Issue I just ran EXPLAIN, how can I tell if the Indexes are used? I just read, that with an operator "like '%a%'" SQLite won't use an Index. Is this the case? Thanks Ralf > -----Ursprüngliche Nachricht----- > Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] Im Auftrag von John Crenshaw > Gesendet: Donnerstag, 22. Oktober 2009 05:53 > An: General Discussion of SQLite Database > Betreff: Re: [sqlite] Inner Join Performance Issue > > Try to EXPLAIN the query and verify that the index is actually used. > There are a lot of reasons why this query would probably NOT be using > the index. > > John > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf > Sent: Wednesday, October 21, 2009 5:50 PM > To: 'General Discussion of SQLite Database' > Subject: [sqlite] Inner Join Performance Issue > > Hello Forum, > [>> ] > I have a select that joins 15 Tables the where clause consist of 8 like > relations (all fields are indexed), this is to implement a sort of > "search > engine". > The performance is awful. It takes around 10sec. > Is this how it should be or is there anything I can do? > > If you need more infos pls. let me know > > Thx > Ralf > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
