оптимизация таблицы/выборки
id INT,
h_from VARCHAR(255),
h_to VARCHAR(255),
body LONGTEXT
индексы на id, h_from, h_to
основной ключ - id
в h_from всегда 1 email адрес, в h_to один или несколько (формат по RFC-822) адресов
body - тело письма (может быть довольно большим)
в таблице ~40000 записей, на запрос
select count(id) from messagebase where (h_from like '%email@some.ru%' or h_to like '%email@some.ru%')
уходит больше минуты, загрузка ЦП сервера во время выборки не превышает 40%, свободной памяти несколько сотен mb, файловая система на RAID'e - практически не активна
как увеличить скорость выборки?
MySQL Distrib 3.23.54, for redhat-linux-gnu (i386)
RedHat Linux 2.4.20-9
есть таблица
id INT,
h_from VARCHAR(255),
h_to VARCHAR(255),
body LONGTEXT
индексы на id, h_from, h_to
основной ключ - id
в h_from всегда 1 email адрес, в h_to один или несколько (формат по RFC-822) адресов
body - тело письма (может быть довольно большим)
в таблице ~40000 записей, на запрос
select count(id) from messagebase where (h_from like '%email@some.ru%' or h_to like '%email@some.ru%')
уходит больше минуты, загрузка ЦП сервера во время выборки не превышает 40%, свободной памяти несколько сотен mb, файловая система на RAID'e - практически не активна
как увеличить скорость выборки?
MySQL Distrib 3.23.54, for redhat-linux-gnu (i386)
RedHat Linux 2.4.20-9
Все проблемы здесь лишь оттого, что база данных не нормализована. Про 1,2,3 нормальные формы знаешь? Вот, к ним и надо стремиться.
Самое первое - условие вида FIELD LIKE ... всегда приводит к полному сканированию всех записей в таблице.
Затем прими во внимание, что "в h_to один или несколько (формат по RFC-822) адресов" - т.е. налицо неформализованное хранение данных.
Отсюда и тормоза. Решение проблемы - в изменении структуры базы данных с тем, чтобы привести ее к 3NF. Например вот так (синтаксис не соблюдаю, просто в качестве наброска):
create table EMAILS (
ID integer not null primary key,
NAME varchar(100),
EMAIL varchar(100)
)
create table MESSAGES (
ID integer not null primary key
FROM integer foreign key EMAILS(ID)
MESSAGE varchar(32767)
)
create table RECIPIENTS (
EMAIL_ID integer not null foreign key EMAILS(ID)
MESSAGE_ID integer not null foreign key MESSAGES(ID)
)
Тогда твой запрос будет выглядеть например так:
select count(id) from messages m inner join emails e on (m.from=e.id) inner join recipients r on (r.message_id=m.id) where e.email='email@some.ru'
Сканирование адреса в этом случае будет идти по индексам, а большая часть сообщений будет вообще отсекаться на этапе соединения таблиц.
Если список адресов ОТ которых и КОМУ посылаются сообщения не пересекается (т.е. заранее известно, что те, КТО посылает сообщения никогда не появятся в списке получателей), тогда можно разнести эти списки по разным таблицам и еще более ускорить работу сервера.
Про 1,2,3 нормальные формы знаешь? Вот, к ним и надо стремиться.
Нет, логически можно догадаться, но я не прочь с этим ознакомиться конкретно...
Затем прими во внимание, что "в h_to один или несколько (формат по RFC-822) адресов" - т.е. налицо неформализованное хранение данных.
список получателей в этом поле можно привести к формату "email@one.ru [email]email@two.ru[/email] ..." (да и к любому в принципе, есть список получателей)
но факт в том, что получателей может быть 1, 2... 1000.
как мне в этом случае это хранить?
PS. попробовал полнотекстовый поиск (MATCH ... AGAINST) с использованием FULLTEXT индексов, аналогичный запрос выполнился за пару секунд, а повторные кешируются.
Нет, логически можно догадаться, но я не прочь с этим ознакомиться конкретно...
Поищи в инете книгу "Understanding SQL", она есть и на русском. Отличный учебник по SQL с самого начала.
но факт в том, что получателей может быть 1, 2... 1000.
как мне в этом случае это хранить?
Я ведь вроде привел раскладку по таблицам? В таблице EMAILS будут лежать все адреса (EMAILS.NAME - имя, EMAILS.EMAIL - адрес). В MESSAGES.FROM - ID отправителя из таблицы EMAILS, таблица RECIPIENTS - список получателей, а точнее их ID из той же таблицы EMAILS. Типичное отношение 1:M, почитай литературу, там все это описано.
Возможно, я сам не использую MySQL.
Где можно почитать о работе с JOIN? ru/en - все равно.
В той же книге "Understanding SQL" например:
http://www.opennet.ru/soft/sql/sql-cont.html
В 8-й главе как раз описывается связывание таблиц.