On 12 Feb 2016, at 9:28am, Michele Pradella <michele.pradella at selea.com> wrote:
> I'm already using my software. The scope of test my query with sqlite shell > is to have a test environment without other interaction, just to test witch > is the fast version of my query to use in my software. Okay. I understand why you want to do this but unfortunately it will not work. You will always have cache interactions and it is extremely difficult to get 'pretend' figures which ignore them. > Another question is, if I have a query like this: > SELECT *field* FROM car_plates WHERE (*field* LIKE 'EX011%') AND > (DateTime>=1455058800000000) AND (DateTime<=1455231599999999) ORDER BY > DateTime; > > If I try to force the use of an index on *field *but I think sqlite can't use > it, is it right? It might be bad to use an index based on *field* for this. It might be better to use one which started with DateTime since that would provide both the selection of DateTime within a range, and also the ORDER BY clause. But it would be best of all to let SQLite choose, since it knows more about SQLite than you do. So don't force any index. If you want to test out something like this, do the following in the shell tool: CREATE INDEX cp_fd ON car_plates (field,DateTime); CREATE INDEX cp_df ON car_plates (DateTime,field); ANALYZE; EXPLAIN QUERY PLAN SELECT *field* FROM car_plates WHERE (*field* LIKE 'EX011%') AND (DateTime>=1455058800000000) AND (DateTime<=1455231599999999) ORDER BY DateTime; The output from EXPLAIN QUERY PLAN will tell you which index it has chosen to use. Then you can delete the other one. For ANALYZE to give useful results you need to have typical data in your tables. SQLite will look at how 'chunky' this data is and use that to make its decisions. You can run the commands with empty tables and they may not lead to the best results. Simon.
