Привет!

У меня есть простая задача, я изложу свое решение, и задам вам вопрос: не знаете ли вы решения лучше?

Есть два отношения t1 и t2, и есть отношение t1_t2 для связи атрибутов из t1 и t2:



Тип данных main_search_attr VARCHAR(45) менять нельзя, все остальное - можно.

В t1 есть 10 000 000 кортежей.

Наиболее часто выполняемый поиск == SELECT * FROM t1 WHERE main_search_attr LIKE 'текст123%';

Он выполняется за 0.01 сек с B-TREE индексом main_search_attr, это меня устраивает.

Одному t1.main_search_attr может соответствовать от 1 до N t2.t2_id

По-этому, в отношении t1_t2 может содержаться примерно 15 000 000 кортежей.

До создания отношения t2 и тестов написанного выше решения руки пока не дошли из-за других причин.

Я думаю создать комбинированный B-TREE индекс (main_search_attr,t1_id) в t1 - в этом действии меня смущает то, что я совмещаю в индексе VARCHAR PRIMARY KEY и MEDIUMINT

Я думаю создать комбинированный B-TREE индекс (t1_id,t2_id) в t1_t2

и выполнить поиск SELECT t1_t2.t2_id FROM t1_t2 INNER JOIN t1 ON t1_t2.t1_id=t1.t1_id WHERE t1.main_search_attr LIKE 'текст123%';

В результате я хочу получить польный список t2_id, соответствующих найденным main_search_attr, за всремя, не превышающее 0.05 сек

SERG1257из всего вышеуказанного не понятно зачем вам поле t1_id в t1 если ключ у вас main_search_attr
Тащите его в кросс-таблицу t1_t2

И указывая общее количество строк в таблицах вы забыли указать сколько строк должен возвращать типичный/средний запрос.

Значение атрибута main_search_attr связано с несколькими t2_id

я немного пересмотрел идею о индексах, получилась такая:

создаем два индекса в отношении t1
первый индекс - по main_search_attr VARCHAR(45) PRIMARY KEY
вторй индекс - по t1_id MEDIUMINT UNSIGNED NOT NULL

и создаем два индекса в отношении t1_t2
первый индекс - по t1_id MEDIUMINT UNSIGNED NOT NULL,
второй индекс - по t2_id MEDIUMINT UNSIGNED PRIMARY KEY

тогда в отношениях t1 и t1_t2 будет одинаковый индекс - по t1_id MEDIUMINT UNSIGNED NOT NULL

второй индекс в t1_t2 - для обратного поиска main_search_attr по t2_t1

для того, чтобы в запросе


WHERE clause выполнялось по индексу main_search_attr
и JOIN выполнялся по индексу t1_id

Не создавать "еще одну таблицу" t1_t2 c 15 000 000 кортежей не получится, потому что иначе придется отказаться от PRIMARY KEY в t1, и ввести дупликаты main_search_attr для всех значений, у которых одинаковый main_search_attr и разые t2_id
На практике, введение первичного ключа дало оптимизацию в 0.5 сек.
Еще один аргумент в пользу создания t1_t2 - дупликаты в t1 значения VARCHAR(45) - дорогостоящие

C Вашей точки зрения, насколько корректное решение я предложил?

SERG1257из всего вышеуказанного не понятно зачем вам поле t1_id в t1 если ключ у вас main_search_attr
Тащите его в кросс-таблицу t1_t2

И указывая общее количество строк в таблицах вы забыли указать сколько строк должен возвращать типичный/средний запрос.
Ой, прошу прощения, не дописал к предыдущему сообщению:

Идея как раз в том, чтобы от дорогостоящего VARCHAR(45) перейти к MEDIUMINT t1_id
и t1_id в кросс таблице будет иметь множество дупликатов
я посчитал, что 5 000 000 дупликатов, по которым нужно искать, будучи MEDIUMINT - лучше, чем 5 000 000 дупликатов VARCHAR(45)

moonglowя посчитал, что 5 000 000 дупликатов, по которым нужно искать, будучи MEDIUMINT - лучше, чем 5 000 000 дупликатов VARCHAR(45)
неправильно делаю?



возвращает, в среднем, 10-20 кортежей