@astaxov81

Как правильно проиндексировать поля в БД?

Здравсвуйте.
Решил попрактиковаться и решаю задачку. Дана таблица с деревом категорий:
CREATE TABLE category (
    id integer not null primary key,
    parent_category_id integer references category(id),
    name varchar(100) not null
);

И необходимо составить следующие запросы:
На выборку всех категорий верхнего уровня, начинающихся на “авто”.
Сделал так
SELECT id, parent_category_id, name FROM category WHERE parent_category_id IS NULL AND name LIKE 'авто%';

На выборку всех категорий, имеющих не более трёх подкатегорий следующего уровня (без глубины).
Сделал так
SELECT id, name, parent_category_id FROM test_270220.category WHERE parent_category_id IN 
    (SELECT parent_category_id 
     FROM test_270220.category
     GROUP BY parent_category_id
     HAVING COUNT(parent_category_id) < 4
    );

На выборку всех категорий нижнего уровня (т.е. не имеющих детей)
Сделал так
SELECT cat1.id, cat1.name, cat1.parent_category_id
	FROM test_270220.category AS cat1
	LEFT JOIN test_270220.category AS cat2 ON cat2.parent_category_id = cat1.id
	WHERE cat2.id IS NULL;

Запросы работают, но в задание есть ещё такой пункт:
Напишите индексы, которые позволят сделать эти запросы быстрее.

С SQL не так часто взаимодействую, погуглив нашел такие варианты:
CREATE FULLTEXT INDEX name on test_270220.category(name)

CREATE INDEX idx_category_parent_category_id  ON test_270220.category (parent_category_id)

Но после создания индеков, время запроса увеличилось о_О, я предположил, что так недолжно быть :). Подскажите с чем может связанно?

После такого вот поворота, начал гуглить, накнулся на статью на хабре. Уже хотел попробовать сделать, как советуется в статье (т.е. создать вторую таблицу. хранить там ссылку на потомка и предка), но в задание не сказанно, что нужно создавать вторую таблицу и т.д.

Именно поэтому нахожусь в небольшом смятенье, или я что-то сделал не так (что после добавления индексов время запроса увеличивается). Или задание составленно не совсем корректно и создания индексов оптимизации запроса не добиться?
  • Вопрос задан
  • 1076 просмотров
Пригласить эксперта
Ответы на вопрос 2
@dimuska139
Backend developer
С SQL не так часто взаимодействую, погуглив нашел такие варианты:

Нужно не гуглить варианты, а построить индексы конкретно под ваши запросы.

Для начала нужно заполнить таблицу достаточно большим количеством тестовых данных, чтобы СУБД гарантировано при построении плана запроса использовала индексы, а не выбирала полное сканирование. После этого нужно с помощью EXPLAIN ANALYZE SELECT ... проанализировать запросы, на основании чего создать нужные индексы.
Ответ написан
Комментировать
BojackHorseman
@BojackHorseman Куратор тега SQL
...в творческом отпуске...
ммм. ну тут надо переписать 2 запроса и добавить 2 индекса и в одном запросе по одному индексу подавить. так будет правильно.

Но после создания индеков, время запроса увеличилось

на этом вопросе падают 95% собеседуемых, потому что не знают, что чтение индекса тоже операция чтения.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы