Оптимизация запроса

[Space]

Оптимизация запроса

поиск по сайту с использованием hash-словаря

итак, есть запрос:
SELECT
dr.region_id,
dt.threed_id,
d.id,
d.title,
COUNT(l.data_id) as found_sum_words,
COUNT(DISTINCT l.word_id) as found_sum_unic_words,
SUM(l.place)/4 AS found_avg_place
FROM b_dict_word as w
LEFT JOIN b_dict_link as l ON (l.word_id=w.id)
LEFT JOIN b_data as d ON (d.id=l.data_id)
LEFT JOIN b_data_threed as dt ON (d.id=dt.data_id)
LEFT JOIN b_data_region as dr ON (d.id=dr.data_id)
WHERE
((w.hash='6134e0734f70d213203ad574b08bd9d1' and w.len='11') or (w.hash='16082c5badb076d702dff1e19ba26fd0' and w.len='9'))
GROUP BY l.data_id
HAVING found_sum_unic_words>='2'
ORDER BY found_sum_unic_words DESC, found_sum_words DESC, found_avg_place ASC

объясняю суть действия:
1. есть таблица-словарь слов b_dict_word, где есть слово в MD5 (w.hash) и его длина (w.len)
2. есть таблица поясняющая b_dict_link поясняющая в каком тексте какое слово находится LEFT JOIN b_data as d ON (d.id=l.data_id)

суть проблемы - долго исполняется запрос. хостер жалуется.

теперь о принципе действия:
надо выбрать записи в которых встречается хотя бы половина из искомых слов и отсортировать по:
found_sum_unic_words - кол-ву уникальных слов встречающихся в тексте
found_sum_words - количеству искомых слов --//--
found_avg_place - средняя позиция искомых слов от начала текста.

простейший поисковый алгоритм.

используемая база - mysql. постгрес и хранимые процедуры не предлагать. хостер не хотит

За ранее благодарен!

 

[Falc]

Space
Эксплаин покажи.

 

[Space]

Falc
http://bigli.ru/sql.htm

 

[Falc]

А что за:
b_data_threed
b_data_region

И почему d.id - у тебя не первичный ключ?

 

[Space]

d.id - индекс.
b_data_threed
b_data_region
это второстепенные таблицы, они содержат регион и раздел.
по региону where не делается, по b_data_threed возможен.

 

[chira]

1. зачем из слова делать MD5? средняя длина слова наверно 5-7 символов. У тебя длина hash 32 символа. Получается делаем лишнюю операцию MD5($clovo) и получаем длинный код который трудней найти.
2. Сделай составной индекс
INDEX ... ON b_dict_word (hash,len)
и такой если нет
INDEX ... ON b_dict_link (word_id,data_id)

 

[Falc]

Space
>>d.id - индекс.
Почему не первичный?

b_data_threed
b_data_region

Они все портят, вообще странные они какие-то, почему они ссылаются на дату? У тебя что одна дата может быть в нескольких регионах? Вообще структура у тебя какая-то запутаная, попробуй нарисовать схемку и выложи ее здесь.

-~{}~ 30.04.04 17:07:

chira
Такие индексы ему не помогут, хешь он и так уникальный, а второй при лефт джойнах бесполезен.
Структура кривая вот и тормозят запросы.

 

[Space]

2chira
name='магазин' гораздо медленее будет обрабатыватся чем hash='".md5('магазин')."'
составной индекс присутствует.
2Falc пожалйста, объясни чем в данном случае лучше первичный ключ?


структуру постараюсь изъяснить

кстати, вот пример того что вы сейчас видели:
http://bigli.ru/ воспользуйтесь поиском. кстати, Falc, ты сразу можешь увидеть в результатах поиска зачем я использую threed_id, и region_id - это что бы показывать юзеру в каком разделе/регионе находится запись

-~{}~ 30.04.04 17:27:

Falc
"LEFT JOIN b_data_region as dr ON (d.id=dr.data_id)"
можно вынести за пределы этого селекта - но надо ли?
структура базы построена как надо - исключение могут составлять ключи и индексы.

-~{}~ 30.04.04 17:35:

пока еще поиск далек от совершенства.
кто искал "форум прикольные ссылки". - кавычки еще не учитываются. но морфология учитывается

 

[Falc]

Space
>>это что бы показывать юзеру в каком разделе/регионе находится запись
Почему тогда они у тебя привязываются не по первичному ключу?

Либо у тебя кривая структура либо, либо ты пишешь одно а на самом деле в базе у тебя все подругому, либо ты привел не верный эксплаин.

Твоий эксплаин должен быть примерно таким:

w range hash hash 32 NULL 4 Using where; Using temporary; Using filesort
l ref word_id word_id 3 w.id 9097
d ref id id 4 l.data_id 1
dt ref data_id data_id 3 d.id 1 Using index
dr ref data_id data_id 3 d.id 1 Using index


Если же у тебя там связи многие ко многим тогда поясни что к чему, а еще лучше покажи схему.

-~{}~ 30.04.04 17:51:

>>2Falc пожалйста, объясни чем в данном случае лучше первичный ключ?

У каждой сушьности должен быть уникальный индентификатор, по которому ее и связывают с другими сущьностями.

 

[Space]

ТАБЛИЦА-словарь
CREATE TABLE `bigli_dict_word` (
`id` mediumint(10) NOT NULL default '0',
`name` varchar(40) NOT NULL default '',
`hash` varchar(32) NOT NULL default '',
`len` smallint(2) NOT NULL default '0',
UNIQUE KEY `id` (`id`),
KEY `hash` (`hash`)
) TYPE=MyISAM;

таблица соответствий - какое слово из словаря в каком тексте находится и на каком месте
CREATE TABLE `bigli_dict_link` (
`word_id` mediumint(10) NOT NULL default '0',
`data_id` mediumint(20) NOT NULL default '0',
`place` smallint(3) NOT NULL default '0',
KEY `word_id` (`word_id`,`data_id`)
) TYPE=MyISAM;


искомые слова:
магазин дерево
1. ищем в словаре эти слова и опредеяем ID слов
получаем:
магазин в словаре несет ID=10
дерево ID=99

2. с помощью таблицы соответствий мы выбираем нужные сайты
т.е.
слово магазин содержится в 22х сайтах
дерево - в 31м сайте

в сумме найденно 53 сайта где содержится како-нибудь из эти слов.

схама проста:
слово - выбор айди слова из словаря(`bigli_dict_word`) - выбор сайта по айди слова(`bigli_dict_link`)

 

[Falc]

Space
В этой схеме как раз нету ошибок, они дальше.

Попробуй убери последние 3 таблицы из запроса и увидешь как он ускорится.

 

[Space]

Falc
посмотри експлайн: http://bigli.ru/sql2.htm
вроде он похож на то что ты сказал..

я сделал PRIMARY для поля id в таблице d а так же добавил индекс для len в таблице-словаре

-~{}~ 30.04.04 18:09:

линк на новый эксплайн: http://olgin.ru/sql2.htm
сейчас хостер днс сменил и тот файл не доступен

-~{}~ 30.04.04 18:14:

я убрал на 3 а 2:
LEFT JOIN b_data_threed as dt ON (d.id=dt.data_id)
LEFT JOIN b_data_region as dr ON (d.id=dr.data_id)

действительно быстрее стало без них.. но почему? причем гораздо быстрее.

 

[Falc]

Space
Это уже лучше, только не понятно почему у тебя
b_data_threed
b_data_region

содержат поля data_id ???

У тебя что сайт может быть в нескольких разделах (регионах)???

-~{}~ 30.04.04 18:19:

Space
>>действительно быстрее стало без них.. но почему? причем гораздо быстрее.

Попробуй нарисовать свою структуру, сразу поймешь где ошибка

 

[Space]

Falc
да, сайт может быть в нескольких разделах. и предположительно может быть в нескольких регионах

 

[Falc]

Space
Тогда нефиг регионы и разделы джойнить в одном запросе.

И вообще не понятно, как тогда твой запрос выдает верные резултаты.

-~{}~ 30.04.04 18:28:

Вообще твой запрос противоречит концепции групировки в SQL.

 

[Space]

-~{}~ 30.04.04 18:34:

главное что бы он сейчас не грузил сервер. а дальше по тихоньку доработаю.
Спасибо!

 

[Falc]

Space
На будующее рекомендую почитать про группировку, причем лучше не мускульную, а вообще SQL'ную.
Тогда поймешь в чем твоя ошибка.

 

[Space]

вот что я выстроил, назовем это продолжением схемы:

вернуть:
1. сколько в тексте искомых слов
2. сколько в тексте уникальных искомых слов
3. среднюю позицию искомых уникальных слов

для этого:
1. COUNT(l.word_id)
2. COUNT(DISTINCT l.word_id)
3. SUM(l.place)/(кол-во искомых слов)

в настоящий момент я использую селект вида:
[sql]
SELECT
d.id,d.title,d.descr,d.url,
COUNT(l.data_id) as found_sum_words,
COUNT(DISTINCT l.word_id) as found_sum_unic_words,
SUM(l.place)/2 AS found_avg_place
FROM bigli_dict_word as w
LEFT JOIN bigli_dict_link as l ON (l.word_id=w.id)
LEFT JOIN bigli_data as d ON (d.id=l.data_id)
WHERE
((w.hash='ac5c74b64b4b8352ef2f181affb5ac2a' and w.len='3') or (w.hash='3a0f2c588c5320f77a8bb5355579721a' and w.len='12'))
GROUP BY l.data_id
HAVING found_sum_unic_words>='1'
ORDER BY found_sum_unic_words DESC, found_sum_words DESC, found_avg_place ASC
[/sql]