[MySQL] TEXT и очень большие таблицы

>Есть таблица в пару гигабайт

Забей. У меня таблица на 5Гбайт, скорость извлечения по целочисленному ключу зависит только от числа строк (коих около 2 млн). От наличия текста - не зависит.

А так - да, когда-то и сам разносил тексты в отдельную таблицу...

...

А для полнотекстового поиска - используй Sphinx Search Engine. Поиск по указанной выше моей таблице выполняется за время от десятых долей секунды до единиц секунд.

> Забей. У меня таблица на 5Гбайт, скорость извлечения по целочисленному ключу зависит только от числа строк (коих около 2 млн). От наличия текста - не зависит.

Не ясно. Вы имели ввиду, что скорость зависит лишь от числа строк, но не от количества гигов таблицы? Так?

А так - да, когда-то и сам разносил тексты в отдельную таблицу...

Тут подробнее. Теперь вы от этого отказалось?

А для полнотекстового поиска - используй Sphinx Search Engine

Тогда встречный вопрос. Что вы делаете, когда этот самый Sphinx возвращает слишком много ссылок (иль ключей, хз)? Отрубаете полнотекстовой поиск и выводите все через LIKE? Иль в Sphinx костыль на максимальное количество результатов вбит?

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

Почему вы думаете, что MySQL FULLTEXT медленней? У меня проблем с полнотекстовым поиском никаких нет. Проблемы с поиском по атрибутам.

Но, дорогие читатели, если вам нужен исключительно полнотекстовой поиск, то используйте Sphinx. Иначе придется делать свою морфологию.

>Не ясно. Вы имели ввиду, что скорость зависит лишь от числа строк, но не от количества гигов таблицы? Так?

Да.

Тут подробнее. Теперь вы от этого отказалось?

Угу. Только лишняя нагрузка в виде лишнего JOIN при выборке. А пользы никакой. По крайней мере в моём случае (БД сообщений форума).

Тогда встречный вопрос. Что вы делаете, когда этот самый Sphinx возвращает слишком много ссылок (иль ключей, хз)? Отрубаете полнотекстовой поиск и выводите все через LIKE? Иль в Sphinx костыль на максимальное количество результатов вбит?

Да, в Сфинксе ограничиваю объём возврата. Поскольку используется только для поиска по форуму, когда результатов слишком много (более 10 тыс) - пусть пользователь сужает поиск.

Ещё на www.aviaport.ru использую для поиска по ключевым словам - там объём базы меньше (~200 тыс. записей). Когда нужно, скажем, найти все упоминания, скажем, какой-то персоны, делаю поиск по её имени и по результатам поиска - уже уточнение на правильность соответствия. Но не через LIKE, а более изощрённо, там слишком разные сочетания таблиц и полей могут быть, так что всё уже на объектом уровне.

Почему вы думаете, что MySQL FULLTEXT медленней? У меня проблем с полнотекстовым поиском никаких нет.

Ну, когда там чёткий поиск по словам от четырёх символов и более - пожалуйста. А если от двух символов? А если нечёткий поиск с учётом морфологии?

Проблемы с поиском по атрибутам.

Да, и проблема с поиском по атрибутам.

Но, дорогие читатели, если вам нужен исключительно полнотекстовой поиск, то используйте Sphinx. Иначе придется делать свою морфологию.

Я когда-то вообще свой поиск делал :) Таблица пересечений базовых словоформ и материалов сайта в виде INT-INT, по индексам. Как Сфинкса для себя открыл - забыл свой велосипед как страшный сон :)

> Ну, когда там чёткий поиск по словам от четырёх символов и более - пожалуйста. А если от двух символов? А если нечёткий поиск с учётом морфологии?

Количество символов — есть в настройках, после лишь пересобрать индексы. Так же есть список стоп слов. Проблема в другом — очень проблематично переопределить по каким критериям разбивать на слова. Морфологию тоже свою нужно городить (отсюда и база в два раза больше).

Как Сфинкса для себя открыл - забыл свой велосипед как страшный сон :)

А сейчас перед выбором - PostgreSQL или Sphinx+MySQL. В обоих случаях проблем морфологией быть не должно — работали русские.

Ещё на www.aviaport.ru использую для поиска по ключевым словам

http://www.aviaport.ru/search/?q=%D0%B0%D0%B2%D0%B8%D0%B0%D1%86%D0%B8%D1%8F&i...

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 13203381 bytes) in /var/www/bors/bors-core/inc/search/sphinx.php on line 135

Я пытался выяснить, как же делается поиск по атрибутам. Можете в двух словах рассказать как решаете проблему вы? Берете столбец из Sphinx и добавляете условия? Мне так показалось судя по количеству в результатах. Извиняюсь за откровенно нубские вопросы.

> http://www.aviaport.ru/search/?q=авиация&i[]=45&s=co

говорит что

searchd error: unknown command (code=1)

Fatal error: Allowed memory size

Конференцию недавно подключили, ещё не вылизана :)

Я пытался выяснить, как же делается поиск по атрибутам

Например, поиск по категрии. Поскольку у меня система объектная, для унификации использую соответствующий индекс соответствующего класса.

В индексе делаем соответствующее поле, выбираемое прямым указанием. Например, у конференций - id класса = 45.

Собственно, не мудрствуя лукаво, вот весь код соответствующего источника:

source conferences_topics : aviaport
{
    sql_query_range     = SELECT MIN(ID), MAX(ID) FROM conferences_topics
    
    sql_attr_timestamp      = create_time

    sql_attr_uint           = class_id
    sql_attr_uint           = object_id
    sql_attr_uint           = owner_id
    sql_attr_bool           = is_deleted
    sql_attr_bool           = is_paged

    sql_query_info      = SELECT *, (SELECT GROUP_CONCAT(source)  FROM conferences_posts WHERE topic_id = conferences_topics

    sql_query_pre = REPLACE INTO sph_counter SELECT 45, MAX(id) FROM conferences_topics

    sql_query               = \
        SELECT id*1000+45 as id, title, \
        (SELECT GROUP_CONCAT(source)  FROM conferences_posts WHERE topic_id = conferences_topics.id) as source, \
            create_time, \
            owner_id, \
            1 as is_paged, 0 as is_deleted, \
            45 as class_id, id as object_id \
        FROM conferences_topics \
        WHERE is_deleted = 0 \
            AND is_hidden = 0\
            AND id>=$start \
            AND id<=$end \
            AND id <= (SELECT max_doc_id FROM sph_counter WHERE counter_id=45)
}

Хитрый id такой сделан для единой нумерации по всем индексируемым объектам разных классов.

Текст топика - это GROUP_CONCAT по принадлежащим ему постингам.

При поиске именно среди же указываю соответствующий ... индекс... Блин, это не тот пример, что тебе нужен :) Тут у меня нет иного сужения поиска по атрибутам, кроме указания индекса (но стирать пример не буду, мало ли, пригодится). Выборка по атрибутам (автор сообщения, topic_id темы для поиска внутри темы и т.п.) есть на Авиабазе, но она давно лежит, а из бэкапа код разворачивать лениво...

Но могу надёргать из тестовых примеров. Скажем, такой вариант:

        $cl = new SphinxClient ();
        $cl->SetServer ( $host, $port );
        $cl->SetConnectTimeout ( 1 );
//      $cl->SetWeights ( array ( 100, 1 ) );
        $cl->SetIndexWeights ( array ( 'topics' => 1000 , 'forum_2' => 100) );
// ...
        $f = $this->f();
        if($f && $f[0])
            $cl->SetFilter('forum_id', $f);

        if($disabled = airbase_forum_forum::disabled_ids_list())
            $cl->SetFilter('forum_id', $disabled, true);
// ...
        if($this->u())
        {
            $user = objects_first('forum_user', array('username' => $this->u()));
            if($user)
            $cl->SetFilter('owner_id', array($user->id()));
        }

        if($this->t())
            $cl->SetFilter('topic_id', array(intval($this->t())));
// ...
        $res = $cl->Query ( $this->q(), $index );

>говорит что

Я памяти увеличил лимит, но не помогло. Ковыряться сейчас, особо, не получается, дочку спать укладывать надо, так что позже займусь :)

Много буков. Но я сумел понять, что там можно делать поиск почти как в SQL с его WHERE AND. Спасибо.

>Но я сумел понять, что там можно делать поиск почти как в SQL с его WHERE AND

Синтаксически там два подхода. Один, недавно введённый - на прямой SQL-like записи. Ещё не щупал. Плюс старый - на фильтрах. Перед запросом можно задать фильтры на соответствие/несоответствие/диапазон/лимиты и т.п.

Одна особенность, которую вначале можно зевнуть. Текстовые атрибуты (не текстовые поля) не поддерживают поднотекстового поиска. Они пишутся в виже хэша. Поэтому можно, например проверить на равенство автора сообщения, но нельзя отсортировать по авторам по алфавиту.

> Один, недавно введённый - на прямой SQL-like записи. Ещё не щупал

Да, уже посмотрел. Однако, судя по бенчмаркам особых чудес нет. Это может впечатлить лишь человека, который до последнего делал поиск через LIKE '%word%'. Скорость выборки по атрибутам и сортировка может впечатлить лишь человека, который не знает про BTREE индексы в MySQL, о чем автор и не скрывает.

Но если бы писал с нуля и требовалась морфология, то использовал бы.

От темы весьма отдаленно пошло…

Последний вопрос. Если в Sphinx складывать данные в особом порядке, то можно ли обойтись без сортировки? Не выводить ли он насильно по релевантности? Не мешает ли порядок как вздумается? Это для меня было бы хорошим поводом переехать.

P.S. Настораживает нищебродность автора. Плачется везде, деньги клянчит. Жалко такого умного парня.

>А сейчас перед выбором - PostgreSQL или Sphinx+MySQL.

Sphinx + PostgreSQL :)

>Да, уже посмотрел. Однако, судя по бенчмаркам особых чудес нет. Это может впечатлить лишь человека, который до последнего делал поиск через LIKE '%word%'.

LIKE WORD ломается уже на нескольких десятках мегабайт :)

Если в Sphinx складывать данные в особом порядке, то можно ли обойтись без сортировки?

Не знаю, потому что всегда сортирую.

Не выводить ли он насильно по релевантности?

Сортировка может быть по релевантности, по любому числовому атрибуту и пара более хитрых, например, по интервалам да (релевантность + актуальность).

Я бы тоже распилил на id+text_column и остаток+text_column_id.

И был опечален — все строки идут внутри данных

В смысле? MYD это файл со строками.

> В смысле? MYD это файл со строками.

В интернетах пишут, что тип TEXT храниться вне общего потока строк/данных. Но если глянуть в MYD, то будет печаль.

Если бы это было так, то не было бы никакой нужны распиливать таблицы. Атрибуты плотно одним куском, не нужные для частого вывода текстовые строки другим.

интернеты нагло врут, в доках к мусклю всё правильно написано