Про perl, или применение на работе полученных знаний в процессе разработки сайта

На работе возникла задача - "вытащить" запросом из базы информацию о сотрудниках нашей организации. Причем вытащить из системы, разработанной подрядчиками, правда по системе есть документация. Нашел в документации обзор который показывает нужную информацию, выполнил запрос, посмотрел что получилось. Оказалось, что запрос показывал не все записи, но вот как понять, по каким полям фильтрует данные подрядчик, в документации этого нет, на "рытье" в исходниках времени нет. Попробовал открыть на портале страницу, возвращающую все доступные записи в виде таблицы. Скопировал эту таблицу в буфер обмена, отрыл Excel, нажал Paste и ... Процессор ушел в 100% загрузку, Excel минут 10 пытался вставить информацию из буфера обмена, но безрезультатно. Мне надоела ждать, "срубил" Excel. Открыл исходник страницы с этой таблицей, вырезал из него только таблицу, попробовал открыть получившийся документ в Word'е. Результат аналогичный Excel'ю. Вот незадача, но надо же понять почему на портале отображается на 10 записей больше чем в запросе. Перебирать все 1500 фамилий сотрудников в поисках несовпадений глазами весьма затруднительно. Как же выбрать из портальной страницы только фамилии, чтобы их сопоставить с найденными запросом.
Задача была решена меньше чем за 5 минут. Я написал маленький скрипт на perl, который "вырезал" из html-ой таблицы фамилии сотрудников. Еще через 5 минут, после использования функции ВПР в Excel я нашел недостающие в запросе фамилии и понял, что всего-то надо было поставить NVL(value, 0) на одно из фильтруемых полей.
Кстати, perl-овый обрабатывал html-ную таблицу размером примерно 3.5 мегабайта около 3-х секунд. Word и Excel отдыхают. Вот этот код:

require HTML::TokeParser;
my $p = HTML::TokeParser->new('c:\qqq.html');
while (my $token = $p->get_tag("a"))
{
print $p->get_trimmed_text . "\n"
   if($token -> [1] -> {onclick} eq 'showEmployee(); return false;');
}

Небольшой оффтопик. Случайно набрел на блог праздного небогача, в котором наткнулся на превосходную идею тиражирования блога. Скоро появятся http://nobyru.blog.ru, http://nobyru.ya.ru, http://nobyru.livejournal.com. Ссылки на сайт Отзывы о путешествиях никогда не будут лишними.

Склонение русских слов

Посещаемость моего сайта пока не высока. Не более 100 человек в день. Просматривая статистику посещения наткнулся на рекламу организации, предлагающей услуги по продвижению сайта. Эти услуги я пока не готов заказывать, но полезную информацию о раскрутке сайта я нашел.
Для автоматической генерации ключевых слов, по которым пользователи производят поиск информации, требовалось проспрягать названия стран и курортов. Найти подходящие классы или пакеты для perl или php не удалось. Но зато один опытный программист прислал ссылку на Russian ispell. Скачав оттуда последнюю версию словарей, обнаружил файл с географическими названиями (жаль, что не со всеми, имеющимися в моем каталоге) и правила для склонения слов. Через несколько часов я смог автоматически проскланять большинство стран и курортов, жаль что не все.
Большое спасибо Александру Лебедеву за предоставленный специальным образом закодированный орфографический словарь русского языка. Кстати, в общем словаре примерно 130 тысяч русских слов, и, теперь, у меня есть код, которых может их склонять.

Добавление на поисковые сервера и в каталоги

Сайт готов. По крайней мере информацию можно смотреть. Возможность вносить статьи постараюсь сделать в ближайшее время.
Надо сообщить о своем существовании. Самым первым сообщением была первая публикация на этом блоге. Но блог-то мало кто пока читает.
Добавить о себе информацию я решил в гулг и в яндекс. Да, есть еще русскоязычные поисковики, но эти самые распространенные.
Прочел на гугле о рекомендации для вебмастеров, создал у себя страницу 404, т.е. страницу, которая отображается, когда запрашиваемая пользователем страница не найдена. Затем добавил свой сайт в список сайтов, создал файл для ботов - robots.txt, а так же файл sitemap.xml, точнее sitemap.xml.gz. Кстати, генерация и сжатие этого файла у меня занимает всего 1.11 секунды, причем в файл добавляется 9065 ссылки на страницы со статьями, индексные файлы я решил не включать. Эти файлы постоянно меняются, да и служат только для показа ссылок на статьи.
Затем проделал аналогичную регистрацию на яндексе. Интересно, что на в описании не сказано, что можно указывать заархивированые файлы sitemap.xml.gz, но яндекс их легко понимает и разбирает. Правда, гугл намного оперативнее яндекса обрабатывает файлы sitemap.
На следующий день гугл в поиске выводил 2 мои страницы, причем те, на которые были ссылки в этом блоге, яндекс молчал. Через неделю гугл показывал 10 страниц, а яндекс почти все!!! Ура. Я появился в поисковых системах.
Правда узнал об этом с 3-х дневным опозданием, мой провайдер трое суток не мог устранить проблему, из-за которой несколько домов нашего района остались без интернета.
И у меня начали появляться посетители. В воскресенье 37 уникальных сайтов, в понедельник уже 52! Интересно, когда их количество добежит до сотни. Надеюсь к концу февраля.
Один из приятелей посетовал, что нет возможности поиска внутри сайта. Свой поиск в силу ограничений, описанных в предыдущих статьях, я сделать не могу. Значит надо воспользоваться решениями, предоставляемыми профессионалами. Поиск от гугл делается за несколько минут. Получаемый код встраивается в готовую страницу и сразу же начинает работать. Но только одна проблема, гугл пока что показывает на этой странице поиска всего 18 страниц, т.е. менее 0.2%. Поиск от яндекс сделать не сложно, только вот результаты поиска не совсем меня устроили. Во-первых, окно с результатами открывается уже на сайте яндекса, а во-вторых, если результат на сайте не найден, то выдается информация с других сайтов, что тоже не привлекает. По-этому буду ждать когда же гугл захочет показать в своем поисковике большинство моих страниц, потому что проиндексировал он их очень давно. Причем, первая индексация была сразу после публикации первой статьи на блоге. Жаль, что я не знал о такой его прыти, потому что при первой индексации было обнаружено больше 1000 ошибочных ссылок. Ну да, я же при отладке делал только первые 20 страниц, а не все 10900 (статьи + индесные файлы).

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

У сервера, любезно предоставленного мне для размещения сайта, были довольно средние характеристики. Поэтому было решено все страницы, за исключением feedback, делать статическими. Для генерации страниц был разработан скрипт на perl. После отладки скрипта оказалось, что на генерацию 24 отзывов вместе с индексными файлами уходит примерно 120 секунд. На мой взгляд, непростительно много. После анализа времени функцией, которое тратится на производство одной страницы, выяснилось, что построение ссылок на страны, курорты, отели, относящиеся к опубликованному отзыву занимает больше всего времени.
Но почему? Вот запрос, получающий эту информацию:

SELECT MIN(p.name) name, count(*) cnt, p.id
  FROM props p
      ,article_props ap
      ,articles a
 WHERE p.up_id = 171
   AND p.tp = 'P'
   AND p.id = ap.prop_id
   AND ap.art_id = a.id
   AND a.is_can_show = 'Y'
 GROUP BY p.id

Для таблицы props (в дальнейшем p) есть индекс по полям up_id и tp, для таблицы article_props (в дальнейшем ap) — индекс по полю prop_id, ну а у таблицы articles (a) — первичный ключ по id. Но запрос сначала шел по таблице a (по индексу по полю is_can_show), затем по ap и по p. В таблице ap записей примерно в 5 раз меньше, чем в p, может быть по-этому анализатор выбирал сначала эту таблицу.
Знаток MySQL рекомендовал мне:

1. переписываем с оракловского синтаксиса на общечеловеческий, расставляя таблицы в желаемом порядке

   SELECT MIN(p.name) name, count(*) cnt, p.id
     FROM props p
    INNER JOIN article_props ap ON p.id = ap.prop_id
    INNER JOIN articles a ON (ap.art_id = a.id AND a.is_can_show = 'Y')
    WHERE p.up_id = 171
      AND p.tp = 'P'
    GROUP BY p.id
   

2. Применить хинт

   SELECT /*+ORDERED*/ ...

3. А лучше переделать все на LEFT JOIN

   SELECT /*+ORDERED*/
          MIN(p.name) name, count(*) cnt, p.id
     FROM props p
     LEFT JOIN article_props ap ON p.id = ap.prop_id
     LEFT JOIN articles a ON (ap.art_id = a.id AND a.is_can_show = 'Y')
    WHERE p.up_id = 171
      AND p.tp = 'P'
      AND a.id IS NOT NULL
    GROUP BY p.id

И добавил: «вообще я INNER JOIN стараюсь не применять вообще никогда.
Потому что LEFT JOIN как раз диктует порядок построения запроса и не даёт оптимизатору умничать. ВСЕГДА ставьте LEFT JOIN. Почувствуйте себя хозяином положения».
И действительно, после этих манипуляций запрос «пошёл» по нужным индексам, и стал отрабатывать за 0.6 секунды, вместо 9.6.
Я создал все страницы, на что ушло около 15 минут, начал проверять что получилось, и заметил, что запрос-то ошибочный. При его выполнении выдаются все записи из таблицы p, даже те, для которых нет ссылок в ap. При замене первого LEFT JOIN article_props ap ON p.id = ap.prop_id на INNER JOIN и эта проблема была решена. Но все же, использование LEFT JOIN меня сильно коробило. Не красиво это.
И элегантное решение было найдено. Оказывается, в MySQL есть возможность указания индексов в хинтах. В результате запрос стал

SELECT MIN(p.name) name, count(*) cnt, p.id
  FROM props p,
       article_props ap,
       articles a IGNORE INDEX(art_is_can_show_i)
 WHERE p.up_id = 171
   AND p.tp = 'P'
   AND p.id = ap.prop_id
   AND ap.art_id = a.id
   AND a.is_can_show = 'Y'
 GROUP BY p.id

24 отзыва вместе с индексными файлами созданы менее чем за 12 секунд (сравните со 120 секундами до оптимизации).