S.Chushkin
Когда нет утечки памяти, то 0.05-0.2 сек без сортировки, с сортировкой почти так же.

Хочу добиться результата, чтобы через 1-2 года не выполнялся данный запрос 2-3 секунды в виду неверной архитектуры.

_____________
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).

И так...
1. Утечки в принципе не должно быть. Если есть - менять версию. (на 5.6.23 утечки не наблюдаю)
2. 0.05 сек на 20млн записей это хороший результат, 0.2 - нормальный. Можно попытаться улучшить (см.далее), но не факт что получится.
3. В запросе сначала идёт выборка, затем упорядочивание. Пара тысяч записей это немного и быстро - можно и без индекса обойтись. В общем тут лучше положиться на оптимизатор. Но если очень хочется, то можно потестить force/ignore index - разница будет маленькая, но если есть желание...
4. Приемлемый результат зависит от конкретных условий - если запрос раз в сутки/сервер, то и несколько секунд приемлемо, если несколько в секунду на сервер, то надо добиваться времени менее 0.1 сек.
А как - см. далее.

_____________
Рекламка / ad.pesow.com Хрень / mr-1.ru

Есть такие виды запросов:
1. с использованием только индекса (самый быстрый)
2. с использование индекса и таблицы (медленнее, иногда сильно)
3. комбинированный (соответственно, медленнее чем первый, но быстрее чем второй)
Этот вариант иногда даёт приличный выигрыш по сравнению со 2-ым, если в запросе используются join-ы или очень большие таблицы (в байтах).

Вероятно, в вашем случае оптимальным будет третий. Но не факт, вполне возможно, что и 2-й будет быстрее, - всё зависит от конкретики (структура, индексы, объёмы и т.п.). У меня были случаи, что в простых запросах 2-й вариант отрабатывал в 2-а раза быстрее.

Для 3-го алгоритм простой:
1. выборка, которая возвращает только ИД
2. по списку полученных ИД "достать" все остальные данные, не входящие в индекс.
Есть два варианта как этого достич:
1. Два отдельных запроса на клиенте.
2. Один запрос с подзапросом и JOIN.

Выглядеть это будет примерно так:
1. получить ИД, используя первый вид запросов (только индекс). Это быстро - ~0.003-0.005 сек (вспомним, что там логарифмическая зависимость скоростей + индекс всегда в RAM, естественно)

SELECT id FROM mess_sett
WHERE man_id = ... AND man_where = ... 

Естественно, все поля, участвующие в запросе, должны быть в индексе. (золотое правило: все индексы д.б. в RAM)
Далее на клиенте формируется список ИД - типа, implode(',', array)
и второй запрос, уже с использованием таблицы. Если требуемые данные в буфере, то это быстро. Если данные ещё не в буфере, то скорость выполнения на 100% будет зависеть от от диска (тут может ускорить только SSD).

SELECT * FROM mess_sett
WHERE id in (...)
ORDER BY `date` DESC

2. Это комбинация запросов из 1. в виде запроса с подзапросом. Что-то вроде:

SELECT t2.* 
  FROM (SELECT id FROM mess_sett
    WHERE man_id = ... AND man_where = ... ) t1,
  mess_sett t2
WHERE t2.id = t1.id
ORDER BY t2.date DESC

Этот запрос должен выполняться ~0.01 сек (+/-).

Не берусь сказать, какой из вариантов будет быстрее - надо пытать в реальности фактическую БД. (у меня, например, подобные запросы на 10млн. выполняются <= 0.01 сек.)
Таким образом можно достигнуть максимума скоростей выборки на больших объёмах (по числу записей) и/или больших размерах. Дальнейшее ускорение это только кеш результатов выборки, увы.
Попытайте, а потом расскажите нам, что получилось в реальности - бастрее/медленнее и насколько.

О сферических индексах и пр. далее...

_____________
Рекламка / ad.pesow.com Хрень / mr-1.ru

Допустим я попробую индекс только по man_id, и ещё попробую по man_id + man_where. Я могу запустить и подождать неделю и так и так.
Вопрос такой, как в итоге собрать всю информацию о том, какой всё же оказался более оптимальным вариантом в учете боевого сервера? То есть какая-нибудь сторонняя программа, или аддон к мускулу, который собирает информацию по запросам к определённой таблице, а далее выводит средний отчёт, или отчёт за Х запросов. Таблица же не только работает как SELECT, там ещё и 400 тысяч INSERT в сутки, чуть меньше DELETE, но тоже много.

Даже если я не буду париться с запросами, мне прям сейчас ставить индекс какой из:

1) `man_id`
2) `man_id` + `man_where`
3) `man_id` + `man_where` + `man_group`
4) `man_id` + `man_where` + `man_group` + `date`
5) Несколько отдельных индексов:
    а) `man_id` + `man_where` + `man_group` + `date`
    б) `man_id` + `man_where` + `man_group` + `man_saw`
    в) `man_id` + `man_where` + `man_group` + `girl_id`

_____________
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).

S.Chushkin

В принципе всё это есть в доке, но всё же народная мудрость говорит, что "повторение мать учения". Иногда полезно повторить, в т.ч. для себя.
И так, о сферических конях...

Индексы:
- В теории, наивысшая скорость выборки будет, если все поля в запросе есть в используемом индексе. PRIMARY индекс наиболее предпочтителен. (в EXPLAIN такие запросы имеют метки "Using where" и "Using index")
- Используемые индексы должны быть в RAM. (в т.ч. поэтому иногда применяют их предварительную загрузку в буфер, чтобы первый юзер не ждал долго)
- Индекс должен начинаться с поля, в котором разнообразие значений наименьшее. Второе - больше, третье - ещё больше и т.д.
Например: В Вашем примере наиболее правильным идексом будет вероятно (man_where, man_group, man_id). Но если у Вас будет задача выбрать NN записей из десятков тысяч найденных, то попробуйте добавить поля сортировки, что-то вроде (man_where, man_group, man_id, date, id) - это может ускорить выборку на 1-2 порядка за счёт исключения создания индекса сортировки (но увеличит затраты на изменение БД, естественно, и требование к размеру RAM).
- Порядок ASC быстрее, чем DESC.
- Индексы с числовыми полями быстрее, чем строковые.
- Стоит помнить, что в InnoDB PRIMARY-индекс определяет физическое расположение записей. Поэтому, наивысшая скорость добавления записей будет, если поля имеют последовательные значения.
- В сложных запросах оптимизатор иногда не справляется со своей задачей. В таких случаях ему нужно помочь в конкретном запросе (use index и т.п.). Оптимизация таблиц также иногда помогает.
- Полнотекстовый поиск по индексу может ускорить выборку на порядок и более (если смириться с ограничениями и кривостью).

Прочее:
- Наивысшая скорость выборки будет если все индексы и все данные будут в RAM.
- Если таблицы, используемые в запросе, изменяются много реже, чем читаются (на 1-2 порядка), то стоит использовать кеш запросов. Это ускорит получение данных в несколько раз и заметно уменьшит нагрузку на движок.
- Для не очень ответственных систем нужно отключить сброс каждой транзакции на диск. Это увеличит скорость добавления/изменения записей на порядок-два (в пределе до десятков тысяч/сек). Применять нужно только на относительно надёжном оборудовании и/или смириться с потерей пары-другой секунд изменений БД в случае сбоя.
- Несколько последовательных изменений завёрнутые в транзакционные рамки выполнятся быстрее, чем по отдельности. В т.ч. это надёжнее в плане целостности данных.
- Самый быстрый способ вставки строк - INSERT с множественными значениями.
- Не правильные/оптимальные настройки движка могут замедлить его работу от 10-20% до нескольких раз.

Структура:
- Денормализация больших данных почти всегда плохо. В основном это связано с работой с диском, но много "мусора" в RAM это тоже плохо. Денормализация небольших данных может ускорить выборку, если поле используется в условиях выборки.
- Для больших данных имеет смысл разбивать таблицы на части, согласно логике доступа к ним. Это позволит более эффективно использовать RAM (где только актуальные данные) и ускорить выборку актуальных, особенно в режиме DESC.
Например: В Вашем случае маловероятно, что юзеров будет интересовать переписка старше 1 месяца при каждом просмотре. Поэтому имеет смысл подумать над вариантом: 1 месяц это актуальные данные, остальное - архив. Вероятно, что запросов в архив будет на 2-3 порядка меньше, чем актуальных и их можно выдавать по требованию, не напрягаясь по поводу времени выборки (в разумных пределах, конечно).
- Обработка однобайтных кодировок быстрее, чем многобайтных.
- Партицирование стоит использовать для очень больших (сотни-тысячи гигов) и реально секционированных данных для запросов. В остальных случаях они бесполезны или вредны.

Ну, как-то так. Если что-то не сказал - спрашивайте, отвечу если знаю.

_____________
Рекламка / ad.pesow.com Хрень / mr-1.ru