SQLite doesn't use index by date

Question

SQL

There is a simple sign:

CREATE TABLE "cashes" (
    "id" INTEGER PRIMARY KEY  NOT NULL,
    "date" DATE NOT NULL,
    "uid" INT(10) NOT NULL DEFAULT ('1'),
    "visible" TINYINT(4) NOT NULL DEFAULT ('1')
)

And a couple of indexes on it:

CREATE INDEX "XIF_CASHES_USR" on cashes (uid ASC);
CREATE INDEX "XIF_CASHES_DUV" on cashes (date DESC, uid ASC, visible ASC);

A strict query on a specific date uses the desired index on 3 fields:

EXPLAIN QUERY PLAN SELECT
      c.id, c.uid, c.date
     FROM cashes c
     WHERE
      c.date = '2013-04-01'
      AND c.uid = 1 AND c.visible = 1
     ORDER BY
      c.date

Plan:
SEARCH TABLE cashes AS c USING INDEX XIF_CASHES_DUV (date=?) (~2 rows)

But the request with benween is gone:

EXPLAIN QUERY PLAN SELECT
      c.id, c.uid, c.date
     FROM cashes c
     WHERE
      c.date BETWEEN '2013-04-01' AND '2013-06-01'
      AND c.uid = 1 AND c.visible = 1
     ORDER BY
      c.date

Judging by the plan, the wrong index is already being picked up:
SEARCH TABLE cashes AS c USING INDEX XIF_CASHES_USR (uid=?) (~2 rows)

Actually, how to force SQLite 3 to use an index by date in queries with BETWEEN?

Answer 1

Why don't you make an index (uid, visible, date)?

Answer 2

Try

WHERE c.`date` > '2013-04-01' AND c.`date` <  '2013-06-01'

Or FORCE INDEX . I just don't know if it's in SQLite

Answer 3

It selects that index because there is a where uid=1, and this operation is supposedly faster than two comparisons. The standard approach here is analyze .
That is, cram data, execute analyze caches, execute a query - the plan will be different depending on whether there are more different dates or different uids in the data (only this should be checked not on 5 records, but more).

Answer 4

More/less do not change the plan.
And here is a hint, yes, it helps.
But I wish I didn't have it...

EXPLAIN QUERY PLAN SELECT
      c.id, c.uid, c.date
     FROM cashes c INDEXED BY XIF_CASHES_DUV
     WHERE
      c.date >= '2013-04-01' AND c.date <= '2013-06-01'
      AND c.uid = 1 AND c.visible = 1
     ORDER BY
      c.date

Plan:
SEARCH TABLE cashes AS c USING INDEX XIF_CASHES_DUV (date>? AND date<?) (~600 rows)
As far as I understood from the plan, only part of the index came from?
Still, I would like a standard approach without the use of hints ...