LINUX.ORG.RU

Оптимизации 5 000 000 таблицы

 ,


0

1

Есть mysql таблица c ~5 000 000 записей

CREATE TABLE IF NOT EXISTS `chat` (
`id` int(11) NOT NULL,
  `record_id` int(11) unsigned NOT NULL,
  `login_owner` varchar(255) NOT NULL,
  `login` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `owner` tinyint(1) unsigned NOT NULL,
  `text` text NOT NULL,
  `date_create` datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5542329 DEFAULT CHARSET=cp1251;

PRIMARY KEY (`id`)
KEY `login` (`login`)
KEY `username` (`username`);


Банальные запросы типа SELECT COUNT(*) FROM `chat` where `username` LIKE '%вася%'; занимают 13 сек., а иногда и 5-6 минут. Как бы это оптимизировать?

В таблицу идет постоянная запись(insert), может это влияет?

★★★

%вася%

Насколько я помню, обычный индекс умеет обслуживать только запросы вида «вася%», а для «%вася%» нужен полнотекстовый поиск.

amomymous ★★★ ()

Заменить `username` LIKE '%вася%' на `username` = 'вася'. Или тебе действительно нужно узнать сколько чего-то там у пользователей в юзернейме которых есть «вася»?

MrClon ★★★★★ ()
Ответ на: комментарий от amomymous

Что %вася%, что %вася один эффект - очень долго ищет

gobot ★★★ ()

Как бы это оптимизировать?

Хранимые процедуры.

shrub ★★★★★ ()

сдается мне, что индекс username не используется при выполнении ваших запросов.

EXPLAIN SELECT COUNT(*) FROM `chat` where `username` LIKE '%вася%';
что показывает? индекс используется или нет?

zaharov ()

FROM `chat`
where `username` LIKE '%вася%'

Зачем тут like?

winlook38 ★★ ()
Ответ на: комментарий от zaharov
+----+-------------+-------------+-------+---------------+----------+---------+------+---------+--------------------------+
| id | select_type | table       | type  | possible_keys | key      | key_len | ref  | rows    | Extra                    |
+----+-------------+-------------+-------+---------------+----------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | chat | index | NULL          | username | 257     | NULL | 5413070 | Using where; Using index |
+----+-------------+-------------+-------+---------------+----------+---------+------+---------+--------------------------+
1 row in set (0.62 sec)
gobot ★★★ ()
Ответ на: комментарий от gobot

Что %вася%, что %вася один эффект - очень долго ищет

Для обоих случаев получишь fullscan индекса, чему удивляться то ?

TEX ★★ ()

зачем тебе +5 млн ?

CHARSET=cp1251 - винда ?

сколько времени идет поиск по ID ?

anonymous ()
Ответ на: комментарий от anonymous

Ну база такая 5 млн., а что удивительного в этом? Нет, не винда, просто однобайтовую кодировку использую

gobot ★★★ ()
Ответ на: комментарий от gobot

Все правильно. Такое будет быстро только с полнотекстовым индексом. Обычные используются только для вася%.

Vit ★★★★★ ()

поставь сфинкс

anonymous ()
Ответ на: комментарий от gobot

тогда ок - можно попробовать добавить поле фиксированной длины nick с индексом

anonymous ()

Сударь, вы часом не количество сообщений для юзера считаете?

ThisNameWasFree ()

Структуру таблицы измени. Вместо username VARCHAR(255) -> uid INT UNSIGNED, это ускорит поиск. И соответственно отдельная таблица для пользователей. Поиск по INT быстрее чем по VARCHAR.

turay ()

1.Перенести пользователей в отдельную таблицу
2.Делать в ней select id from users where username like '%вася%'
3.Парсить id и делать select count(*) from chat where iduser id ($id_list)
4.Можно попробовать связать: select count(*) from users,chat where users.id=chat.iduser and username like «%вася%», но для твоего примера это не очень хорошо подходит.

crutch_master ★★★★★ ()

нужен полнотекстовый поиск

В данном случае, не нужен совершенно. А те, кто это пишут, просто ничего не понимают или жирно троллят.

crutch_master ★★★★★ ()
Последнее исправление: crutch_master (всего исправлений: 1)
Ответ на: комментарий от gobot

Я конечно выгляжу занудой, но я бы попросил подробнее прочитать ссылку которую я пересылал https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html Как вы можете заметить, тут ясно написано innodb fulltext index. Мб просто версия mysql старая, тогда да, не пойдет.

Tark ★★ ()
Ответ на: комментарий от Tark

Да нахрена ему fulltext index для поиска юзеров? Как из пушки в муху палить же. Надо делать декомпозицию и сваливать login,username в отдельную таблицу, а не страдать хернёй.

crutch_master ★★★★★ ()
Последнее исправление: crutch_master (всего исправлений: 1)
Ответ на: комментарий от crutch_master

Скорее всего для автодополнения в каком-нибудь поле для отправки сообщения. В принципе это лучше делать через sphinx, но если человек сам не разобрался, значит лучше ему сделать это чуть медленне но с mysql из коробки чем парится со sphinx.

Tark ★★ ()
Ответ на: комментарий от Tark

для автодополнения в каком-нибудь поле для отправки сообщения.
sphinx

Не проще ли табличку, в которой будут его 3,5 флудера?

crutch_master ★★★★★ ()
Последнее исправление: crutch_master (всего исправлений: 1)

что за ебантроп это проектировал? или у тебя анонимный чат без пользователей?

anonymous ()

судя по всему таблица спроектирована не правильно. вся информация о юзере (login_owner,login,user_name) должна быть в отдельной таблице (user_info). как минимум это сильно уменьшит размер таблицы chat, таблица user_info будет маленькой и запросы на поиск по ней даже '%вася%' будет быстрый.

что можно сделать быстро - так это дефрагментировать таблицу и индекс.

vtVitus ★★★★★ ()
Ответ на: комментарий от ThisNameWasFree

Нет, мне нужен поиск по нику и тексту и сортировка по дате

gobot ★★★ ()
Ответ на: комментарий от turay

Так поиск нужен не по уид, а по нику, тем более нет отдельной таблицы с юзерами

gobot ★★★ ()
Ответ на: комментарий от crutch_master

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

gobot ★★★ ()
Ответ на: комментарий от Tark

Мускуль новейший на дебе 8, пробовал создать на innodb, говорит only myisam. Пришлось в этот формат таблицу перегнать. Ссылку пкречитаю, просто с телефона пишу

gobot ★★★ ()
Ответ на: комментарий от Tark

Ну да, в innodb поддержка fulltex с версии 5.6, а в дебе 8 версия 5.5.44

gobot ★★★ ()

Только если. Повторю — ТОЛЬКО ЕСЛИ логин (или логин_овнер) можно считать уникальными:

Создать таблицу для инфы пользаков (повесить уникальный индекс на логин/логин_овнер), в таблице пользаков и автоинкрементное поле id. Положить туда автоинкрементом всех пользаков примерно так:

INSERT INTO users_info (id, a, b, c, d) SELECT (NULL) id, a, b, c, d FROM chat GROUP BY a
Добавить поле uid в таблицу чата, повесить на него неуникальный индекс. Если нужны разделения на автор/получатель то добавлять надо ессно два поля в таблицу чата (типа sender_id, receiver_id). Проапдейтить в цикле (бекендом/функцией/процедурой как умеешь) все uid в чате, вставить айдишники. Пример для одной итерации:
UPDATE chat SET uid = X WHERE login = Y
И так для всех логинов (или что там у тебя уникальное).

Теперь твой поиск будет работать быстрее:

SELECT
        u.*,
        c.*
    FROM users_info u
    INNER JOIN chat c
        ON c.uid = u.id /* AND c.`text` LIKE '%дибил%' */
    WHERE u.login LIKE '%вася%'
    /* ORDER BY c.date_create DESC */

deep-purple ★★★★★ ()
Ответ на: комментарий от gobot

но чем это будет отличаться от индекса

Тем, что поиск будет 0.01 сек вместо 10 минут.

crutch_master ★★★★★ ()
Ответ на: комментарий от crutch_master

Это будет плохой full text индекс, который неудобно поддерживать. Лучше использовать настоящий FT индекс или родной или через sphinx. Десятки миллисекунд запросы на индекс работают если надо на диск лезть, закешированные данные (что в page cache что в buffer pool) будут доли миллисекунд-миллисекунды.

anonymous ()
Ответ на: комментарий от gobot

И вообще, гугли «3НФ» и сделай все нормально, вместо кучи костылей с индексами.

crutch_master ★★★★★ ()
Ответ на: комментарий от anonymous

неудобно поддерживать

Одну таблицу в 1нф для всего, конечно же намного удобнее поддерживать, да.

crutch_master ★★★★★ ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.