Мой текущий запрос выглядит примерно так:
SELECT SUBSTR(name,1,1), COUNT(*) FROM files GROUP BY SUBSTR(name,1,1)
Но для подсчета в таблице, которая уже проиндексирована столбцом name, требуется довольно много времени. Из этого вопроса я понял, что некоторые движки могут не использовать индексы правильно для функции SUBSTR, и на самом деле sqlite не будет использовать индексы для SUBSTR(string,1,1).
Есть ли какой-либо другой подход, который бы использовал индекс и ускорил бы мои запросы?
Одна из стратегий, которая соответствует вашему шаблону доступа, состоит в том, чтобы добавить в таблицу новый индексированный столбец «first_letter». Используйте триггер, чтобы установить значение при вставке и обновлении. Тогда ваш запрос представляет собой простую группу по first_letter.
Другая стратегия заключается в создании теневой таблицы, которая содержит агрегацию материнской таблицы. Это непросто, потому что ваша работа как разработчика — поддерживать согласованность теневой таблицы с материнской таблицей. Каждое удаление, обновление или вставка в таблицу files должно сопровождаться изменением в теневой таблице.
Базы данных, такие как Oracle, поддерживают материализованные представления для автоматического достижения этого, но sqlite этого не делает.
