SQLite не использует индекс по дате

Question

[Алексей Скахин]

Есть простенькая табличка:

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')
)

И пара индексов по ней:

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

Строгий запрос по определенной дате использует нужный индекс по 3 полям:

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

План:
SEARCH TABLE cashes AS c USING INDEX XIF_CASHES_DUV (date=?) (~2 rows)

А вот запрос с benween уже нет:

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

Судя по плану уже подхватывается не тот индекс:
SEARCH TABLE cashes AS c USING INDEX XIF_CASHES_USR (uid=?) (~2 rows)

Собственно, как заставить SQLite 3 использовать индекс по дате в запросах с BETWEEN?

Answer 1

[mayorovp]

А почему вы не сделаете индекс (uid, visible, date)?

Comments

[Алексей Скахин]

Почему? Сделал:

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

или важна последовательность?

[mayorovp]

Важна последовательность. Поле, по которому идет поиск диапазона, должно быть последним. В таком случае индекс будет использоваться независимо ни от какой статистики.

[Алексей Скахин]

Неожиданно :) Действительно стал браться индекс по всем 3 полям.
SEARCH TABLE cashes AS c USING INDEX XIF_CASHES_DUV (uid=? AND visible=? AND date>? AND date<?) (~42 rows)
Спасибо за информацию.

Answer 2

[Валерий]

Попробуйте

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

Либо FORCE INDEX. Только не знаю есть ли он в SQLite

Comments

[Алексей Скахин]

Упс, ответил ниже.

Answer 3

[Алексей Скахин]

Больше/меньше не меняют план.
А вот хинт, да, помогает.
Но хотелось бы без него…

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

План:
SEARCH TABLE cashes AS c USING INDEX XIF_CASHES_DUV (date>? AND date<?) (~600 rows)
Насколько я понял из плана, взялась только часть индекса?

Все же хотелось бы стандартный подход без использования хинтов…