Есть связанные таблицы по принципу "один к многим". Если упрощенно, есть таблица тем, и есть страница фраз.
При запросе по теме, происходит выборка из таблицы фраз по полю идентификатора темы (INT 6). Все хорошо, все работает довольно шустро даже с таблицей, приближающейся к миллиону записей.
Но возникла проблема дублирования записей в таблице фраз: например, фраза "день рождения ребенка" есть и для темы "день рождения", и для темы "дети". Получается, две записи, отличающиеся лишь полем theme_id. Но именно по этому полю и идет выборка при запросе фраз по теме. Пока это поле числовое индексированное - запрос выполняется быстро; другие параметры поля на больших таблицах дают ужасные тормоза...
Не могу придумать, как избавиться от дублирования фраз в таблице фраз...
Спустя 1 час, 30 минут, 17 секунд (24.04.2010 - 20:37) vasa_c написал(а):
Вынести тексты в третью таблицу, а во второй оставить связку для первой и третьей?
Или объяснить зачем вообще понадобилось выносить. Таких совпадений настолько много, что об этом следует задуматься?
Или объяснить зачем вообще понадобилось выносить. Таких совпадений настолько много, что об этом следует задуматься?
Спустя 1 час, 51 минута, 37 секунд (24.04.2010 - 22:28) FatCat написал(а):
| Цитата (vasa_c @ 24.04.2010 - 21:37) |
| во второй оставить связку |
Как реализовать связь?
Давай начну сначала.
В простейшем виде достаточно одной таблицы на 2 поля: тема и словосочетание. Но бешеные тормоза запроса
SELECT * FROM table_name WHERE theme = 'слово'при более-менее существенном количестве строк в таблице.
Индексация по полю theme проблемы не решает: начинаются бешеные тормоза при добавлении новых строк.
Поэтому темы и словосочетания разнесены на 2 таблицы, связанных по полю theme_id: в таблице тем он уникальный, в таблице словосочетаний множественный - отношения один ко многим.
Какую таблицу поставить между ними для связи - не представляю...
| Цитата (vasa_c @ 24.04.2010 - 21:37) |
| Таких совпадений настолько много, что об этом следует задуматься? |
Сейчас порядка 14%.
Беда в том, что в таблице словосочетаний хранятся не только сами словосочетания, но еще 2 цифры, и эти цифры требуется периодически обновлять...
На таблице 620 000 записей полное обновление заняло больше 20 часов... А при выходе проекта на рассчетную мощность, словарь словосочетаний ожидается в десятки миллионов...
Спустя 11 часов, 37 минут, 21 секунда (25.04.2010 - 10:06) vasa_c написал(а):
`тема`: `id`, `название`
`словосочетания`: `id`, `текст`
`связка`: `тема_id`, `словосочетание_id`
+ чтобы не делать трехэтажные JOIN'ы на больших объёмах - вынести словосочетания в memcacheDB или Redis, из базы доставать только ID и тянуть их мультигетом из мемкэша.
`словосочетания`: `id`, `текст`
`связка`: `тема_id`, `словосочетание_id`
+ чтобы не делать трехэтажные JOIN'ы на больших объёмах - вынести словосочетания в memcacheDB или Redis, из базы доставать только ID и тянуть их мультигетом из мемкэша.
Спустя 1 час, 32 минуты, 11 секунд (25.04.2010 - 11:38) FatCat написал(а):
Увы, не годится. Даже по двум причинам.
Во-первых, требуется сортировка не только по тема_id, но и еще по двум цифровым полям. Если называть вещи своими именами, то речь о низкочастотниках из вордстата, и кроме принадлежности теме требуется тягать ключи по критерию частоты (от и до) и по критерию актуальности данных (время последнего парсинга вордстата).
А вот типичный пример запроса; такие запросы должны работать быстро:
Во-первых, требуется сортировка не только по тема_id, но и еще по двум цифровым полям. Если называть вещи своими именами, то речь о низкочастотниках из вордстата, и кроме принадлежности теме требуется тягать ключи по критерию частоты (от и до) и по критерию актуальности данных (время последнего парсинга вордстата).
CREATE TABLE `ibf_wordstat` (
`wid` int(10) NOT NULL auto_increment,
`wstatus` tinyint(1) NOT NULL default '0',
`wtime` int(10) NOT NULL default '0',
`wkey` varchar(255) NOT NULL,
`wfreq` int(8) NOT NULL,
`wwch_id` int(6) NOT NULL default '0',
PRIMARY KEY (`wid`),
KEY `wfreq` (`wfreq`),
KEY `wwch_id` (`wwch_id`)
) ENGINE=MyISAM AUTO_INCREMENT=868706 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=868706 ;
А вот типичный пример запроса; такие запросы должны работать быстро:
SELECT wkey, wfreq, wtime, wwch_id
FROM ibf_wordstat
WHERE wwch_id IN(1,5,99,456,789,987,1234,1235,1259)
AND wstatus = 1
AND wfreq>=50
AND wfreq<=500
ORDER BY wfreq DESC
LIMIT 0,1000
Спустя 3 минуты, 1 секунда (25.04.2010 - 11:41) FatCat написал(а):
А во-вторых, будут бешеные тормоза при добавлении новых ключей: при парсинге вордстата, мы имеем только текст ключа; значит нам потребуется текстовый поиск с бешеными тормозами поиска по неиндексированному текстовому полю. Если же проиндексировать текстовое поле, начнутся бешеные тормоза ребилда индекса после каждого инсерта...
В общем, много вариантов продумал, но везде тупик, везде начинает тормозить...
В общем, много вариантов продумал, но везде тупик, везде начинает тормозить...
Спустя 1 час, 14 минут, 24 секунды (25.04.2010 - 12:55) vasa_c написал(а):
эти цифровые поля привязаны к самой фразе (тексту) или фразе в конкретной теме?
Спустя 1 час, 53 минуты, 52 секунды (25.04.2010 - 14:49) HardWoman написал(а):
Угу я тоже с этим бьюсь. Пока не придумала ничего лучшего как хранить путь/параметры в двоичной результирующей строке. По сути материализованный путь.
| Цитата |
| Поэтому темы и словосочетания разнесены на 2 таблицы, связанных по полю theme_id: в таблице тем он уникальный, в таблице словосочетаний множественный - отношения один ко многим. Какую таблицу поставить между ними для связи - не представляю... |
Я сейчас думаю в таком направлении
У нас задачи очень похожие, только у меня все равно конечно не такой высоконагрузочный проект
Просто мое направление изысканий.
тема> работа> работа постоянная> работа постоянная с частичной занятостью.
Это если вам нужно хранить именно словосочетания в массивах
У меня так
Доска объявлений>
работа>постоянная>частичная занятость
журналистика> тексты> корректоры
Журналистика> тексты> переводчики
Я разбиваю все параметры на некие логические массивы
Возможно у вас будет другой принцип разбиения на логические массивы.
Хотя логику у вас определить сложно
Каждый массив у меня рассматривается как параметр
Есть связи массивов по конкретному фильтру
В данном случае фильтрами являются ID элементов работа и журналистика
Нумерация же массивов одинакова - как параметров - по сути некий шаблон который от фильтра вызывает свои массивы, которые содержат свои группы элементов.
Элементы тоже не несут уникальных значений. Каждый элемент в массиве начинается с 1
Есть таблица связей
Фильтр, ID массива, ID связи элементов
Так как нумерация начинается с 1, то и количество связей ограничено/их не мало, но не так при уникальных значениях
буду переводить все индексы в двоичную систему .
начала пытаться решить поиск от ребенка к родителю. Для меня бинарная строка видится лучшим решением.
У вас нет связных объектов с последними листами ветки.
Может просто мои поиски что то вам и подскажут, возможно мое решение ошибочно и возможно вам оно не подойдет, но ярешила написать
Спустя 26 минут, 16 секунд (25.04.2010 - 15:16) HardWoman написал(а):
кстати в голову пришло пока только одна логика пока - для вашей задачи
Формирование массивов по букве алфавита. Сами же значения/элементы в списке тоже сортируете по буквенным сочетаниям - присваиваете двоичный идентификатор, то есть идентификаторы у вас тоже будут идти не хаотично, а возрастающей последовательности, то есть массив уже будут отсортирован.
Производить поиск по двоичному отсортированному массиву, как пишут несложно.
Прошу извинить, если посчитаете, что я не в тему
Формирование массивов по букве алфавита. Сами же значения/элементы в списке тоже сортируете по буквенным сочетаниям - присваиваете двоичный идентификатор, то есть идентификаторы у вас тоже будут идти не хаотично, а возрастающей последовательности, то есть массив уже будут отсортирован.
Производить поиск по двоичному отсортированному массиву, как пишут несложно.
Прошу извинить, если посчитаете, что я не в тему
Спустя 1 час, 46 минут, 19 секунд (25.04.2010 - 17:02) FatCat написал(а):
| Цитата (vasa_c @ 25.04.2010 - 13:55) |
| цифровые поля привязаны к самой фразе (тексту) или фразе в конкретной теме? |
К фразе.
У одинаковой фразы они должны быть одинаковыми.
| Цитата (HardWoman @ 25.04.2010 - 15:49) |
| бинарная строка видится лучшим решением. |
Когда от одного ко многим через нескольких - конечно. Последовательно можно и нужно связывать любое количество массивов, но в порядке возрастания размеров в одну сторону. А тут бОльший массив в середине...
| Цитата (HardWoman @ 25.04.2010 - 16:16) |
| Формирование массивов по букве алфавита. |
Отпадает. Увы.
Я далеко не всё рассказал в алгоритме.
Есть еще и таблица семантики, в которой "ягодам" сопоставлены "клубника", "малина" и т.д.
Я стал понимать яндекс, который делает периодические апдейты базы. Некоторые связи невозможно выстраивать динамически, никаких ресурсов не хватит; приходится время от времени перегенерировать таблицы связей.
Сейчас попробовал сделать таблицу связей "один к нескольким через многих"; загрузил сервер по самые бакенбарды, отмолачивает 100 строк в среднем за 25 секунд, а у меня таких строк 600 000. А когда строк будет миллионов 20, а нагрузка будет расти квадратично? Я же состарюсь прежде чем база обновится...
Увы, не выход.
Спустя 6 минут, 16 секунд (25.04.2010 - 17:08) vasa_c написал(а):
| Цитата |
| AND wstatus = 1 AND wfreq>=50 AND wfreq<=500 |
почему wstatus без индекса? причем здесь бы пошел составной ключ на оба поля.
Спустя 36 минут, 41 секунда (25.04.2010 - 17:45) HardWoman написал(а):
| Цитата |
| Я далеко не всё рассказал в алгоритме. Есть еще и таблица семантики, в которой "ягодам" сопоставлены "клубника", "малина" и т.д. |
А можно немного подробнее об алгоритме? В частности это сопоставление каким образом вы видите сделать?
То есть формировать некий массив детей от слов в запросе?
По входящему запросу? если не придумать какую то вменяемую логику мне задача видится нерешаемой.
Вы по сути создаете поисковик
Спустя 48 минут, 31 секунда (25.04.2010 - 18:33) FatCat написал(а):
| Цитата (vasa_c @ 25.04.2010 - 18:08) |
| почему wstatus без индекса? |
Проверял, при индексации скорость не меняется.
Собственно, это поле вообще можно убрать, оно осталось от старой версии, когда все ключи заливались штучно вручную. Это поле премодерации; сейчас нет ни одного ключа со значением "0".
| Цитата (HardWoman @ 25.04.2010 - 18:45) |
| Вы по сути создаете поисковик |
Нет, не поисковик.
Есть такой пихологический феномен: "ожидание" следующего слова. За словом "красный" обычно ждут "флаг" или "тюльпан", но никак не "чемодан".
Чем больше текст соответствует ожидания читателя - тем легче он воспринимается, тем комфортнее чтение.
На основе статистики, собранной яндексом, делаю анализ словарного запаса и устойчивых лексических связей между словами среднестатистического пользователя интернета. И частота запросов в месяц - численное выражение "активности использования" слова или словосочетания.
Таким образом, при анализе текста можно получить суммарный вес имеющихся лексических связей. Чем вес больше - тем ближе этот текст к "ожиданиям" читателя. Выраженное цифрой выдается по результатам анализа текста. В левой колонке список найденных ожиданий (без разметки) и ненайденных ожиданий (с разметкой), в правой колонке размеченный текст.
Что-то очень похожее по-видимому используют и поисковки. Я проанализировал трафик по низкочастотникам на более чем 30 000 страниц трех больших форумов, на которых стоят "дарлинги" (один из трех - этот), и вывел коэффициенты в определении индекса тематичности таким образом, что единица - это как бы порог посещаемости. И определяет сейчас довольно точно: если индекс больше единицы, страница получит посетителей по низкочастотникам из поисковок; если ниже единицы - не будет посетителей.
| Цитата (HardWoman @ 25.04.2010 - 18:45) |
| В частности это сопоставление каким образом вы видите сделать? |
Уже сделал.
Темы тоже имеют айдишники, в таблице тем есть поля "родительские айдишники" и "дочерние айдишники" - текстовые, заполняются при апдейте списком через запятую, чтобы сразу в следующий запрос подставлять.
Да, нашел пока временное решение.
$DB->query("SELECT DISTINCT wwch_id FROM ibf_wordstat WHERE wtime < ".(time()-60*60*24*30));
Получаю список айдишников тем, у которых есть хоть одна фраза с актуальностью старше месяца.
Простым селектом по таблице тем получаю список самих слов, и загоняю в парсер.
Вопрос дублирования словосочетаниев этим не решается, но решает главный вопрос: актуальность словаря.
_____________
Бесплатному сыру в дырки не заглядывают...