Работает ли многостолбцовый индекс для отдельных столбцов?
2 ответа
marc_s has the correct answer to your first question. The first key in a multi key index can work just like a single key index but any subsequent keys will not.
As for how much faster the composite index is depends on your data and how you structure your index and query, but it is usually significant. The indexes essentially allow Sqlite to do a binary search on the fields.
Using the example you gave if you ran the query:
SELECT * from orders where customer > 33 && date > 99
Sqlite would first get all results using a binary search on the entire table where customer > 33. Then it would do a binary search on only those results looking for date > 99.
If you did the same query with two separate indexes on customer and date, Sqlite would have to binary search the whole table twice, first for the customer and again for the date.
So how much of a speed increase you will see depends on how you structure your index with regard to your query. Ideally, the first field in your index and your query should be the one that eliminates the most possible matches as that will give the greatest speed increase by greatly reducing the amount of work the second search has to do.
For more information see this: http://www.sqlite.org/optoverview.html
Я довольно конечно, это будет работать, да, в любом случае - в MS SQL Server.
Однако этот индекс не поможет вам, если вам нужно выбрать только дату, например, диапазон дат. В этом случае вам может понадобиться создать второй индекс только для даты, чтобы сделать эти запросы более эффективными.
Marc