kms писал(а) 09. Июля 2008 :: 10:20:Саша, а можно тогда уж еще небольшой ликбез.
Цитата:--create table test(f1, f2, f3)
--create index on_test1 on test(f1)
--create index on_test2 on test(f2)
--create index on_test3 on test(f3)
--insert into test values(1, 1, 2)
--insert into test values(1, 2, 8)
--insert into test values(2, 1, 15)
Как эффективно строить выборки с участием нескольких индексов?
Цитата:select * from test where f1 = 1 and f2 = 1 order by f3
select * from test where f1 = 1 or f2 = 1 order by f3
select * from test where f1 < 1 and f2 > 1 order by f3
select * from test where f1 < 1 or f2 > 1 order by f3
"or" надо переписывать как union?
А "and" и так нормально ляжет на индексы?
Начнем с того, что SQLite, как и MSSQL делает проход по одной таблице ТОЛЬКО максимум по одному индексу.
То есть если хотим заюзать несколько индексов, то надо соотвественно, делать несколько подзапросов, организуя их так, чтобы в каждом подзапросе для таблицы участвовало только одно условие, попадающее в индекс.
И объединять результаты подзапросов через union, union all, intersect, и разные join
По порядку
запрос
select * from test where f1 = 1 and f2 = 1 order by f3
План выполнения:
Использование таблиц
order from detail
0 0 TABLE test WITH INDEX on_test2
Поэксперементировал и преобразовал:
select *
from test left join
(
select rowid from test where f1 = 1
intersect
select rowid from test where f2 = 1
) tConstr on test.rowid = tConstr.rowid
where tConstr.rowid is not null
order by f3
План:
Использование таблиц
order from detail
0 0 TABLE test WITH INDEX on_test1
0 0 TABLE test WITH INDEX on_test2
0 0 TABLE test WITH INDEX on_test3 ORDER BY
1 1 TABLE AS tConstr
Естественно, если посмотреть детально программу выполнения видно, что создаются временные таблицы для операций intersect, собственно, обслуживание самого intersect. Так что на небольших таблицах или плохих условиях это наоборот даст проигрыш.
select * from test where f1 = 1 or f2 = 1 order by f3
План:
Использование таблиц
order from detail
0 0 TABLE test WITH INDEX on_test3 ORDER BY
Переделываем
select *
from test left join
(
select rowid from test where f1 = 1
union
select rowid from test where f2 = 1
) tConstr on test.rowid = tConstr.rowid
where tConstr.rowid is not null
order by f3
План:
Использование таблиц
order from detail
0 0 TABLE test WITH INDEX on_test1
0 0 TABLE test WITH INDEX on_test2
0 0 TABLE test WITH INDEX on_test3 ORDER BY
1 1 TABLE AS tConstr
те принцип примерно понятен. and заменяем на intersect, or заменяем на union.
Для данных примеров пришлось извратится с запросом, чтобы попасть еще и в order by.
Если использовать inner join, SQLite почему-то идет не по on_test3, а по primary key (rowid), и сортирует во временной таблице.
Если бы в запросах не было
order by f3, то было бы проще:
select * from test where f1 = 1 and f2 = 1
заменилось бы на
select * from test where f1 = 1
intersect
select * from test where f2 = 1
Страницы: 
1sqlite (число прочтений - 464521 )
