Производительность

[istepan]

На форуме более 4-х мл. сообщений.
Разбил таблицу posts на партиции по 1 мл. Дышать стало легче.

Однако была одна тема, где сообщений больше 150 т., там запрос выполнялся более 30 сек.

Конкретно этот:

Код: Выделить всё

SELECT p.post_id
	FROM phpbb_posts p
	WHERE p.topic_id = 50479
		AND p.post_visibility = 1		
	ORDER BY p.post_time DESC, p.post_id DESC
 LIMIT 13;

Проблема в том, что база не может использовать индексы при сортировки, и вынуждена сортировать более 150 т. строк самым медленным алгоритмом.

Саму тему закрыл, сказав чтоб открывали новую. Однако осадочек остался. Как-то хотелось бы оптимизировать форум под большие объемы данных.

[rxu]

Индексы в таблице постов все на месте?

[istepan]

Индексы в таблице постов все на месте?

Да. Более того добавил "post_time".
По спецификации mysql, индексы в таком запросе не будут задействованы.
http://www.mysql.ru/docs/man/ORDER_BY_optimisation.html

[rxu]

Более того добавил "post_time".

Он там есть по умолчанию.

Отправлено спустя 1 минуту 23 секунды:

По спецификации mysql, индексы в таком запросе не будут задействованы.

Там такого не сказано. Почему конкретно?

[istepan]

Почему конкретно?

Ниже приведены некоторые случаи, когда MySQL не может использовать индексы, чтобы выполнить ORDER BY (обратите внимание, что MySQL тем не менее будет использовать индексы, чтобы найти строки, соответствующие выражению WHERE):


Сортировка ORDER BY делается по нескольким ключам: SELECT * FROM t1 ORDER BY key1,key2
Сортировка ORDER BY делается, при использовании непоследовательных частей ключа: SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2
Смешиваются ASC и DESC. SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 ASC
Для выборки строк и для сортировки ORDER BY используются разные ключи: SELECT * FROM t1 WHERE key2=constant ORDER BY key1
Связываются несколько таблиц, и столбцы, по которым делается сортировка ORDER BY, относятся не только к первой неконстантной (const) таблице, используемой для выборки строк (это первая таблица в выводе EXPLAIN, в которой не используется константный, const, метод выборки строк).
Имеются различные выражения ORDER BY и GROUP BY.
Используемый индекс таблицы имеет такой тип, который не обеспечивает сортированного хранения строк (как индекс HASH в таблицах HEAP).
Столбцы индекса могут содержать значения NULL, и используется ORDER BY ... DESC. Это объясняется тем, что в SQL значения NULL всегда сортируются в первую очередь, независимо от того, используется DESC или нет.

Он там есть по умолчанию.

У меня не было. Мигрировал со второй на 3.1, потом на 3.2.

[rxu]

Я умею читать.

Сортировка ORDER BY делается по нескольким ключам

Это? Какие предложения?

[Siava]

Я бы убрал p.post_time DESC из этой строчки

Код: Выделить всё

ORDER BY p.post_time DESC, p.post_id DESC

[rxu]

Я бы убрал p.post_time

так в phpBB посты выводятся как раз по времени, а не по id, нет?

Отправлено спустя 4 минуты 34 секунды:
И потом, это вряд ли поможет, так как внутри WHERE имеются другие поля, что также помешает использованию индекса.

[istepan]

А если сделать подзапросы?

[nissin]

IMHO только если делать сложный индекс по всем полям запроса:

Код: Выделить всё

topic_id
post_visibility
post_time
post_id

Тогда MySQL сможет использовать этот индекс для поиска и сортировки.

[kak2z]

Код: Выделить всё

SELECT p.post_id
	FROM phpbb_posts p
	WHERE p.topic_id = 50479
		AND p.post_visibility = 1		
	ORDER BY p.post_time DESC, p.post_id DESC
 LIMIT 13;

непонятно зачем тут сортировка по ID если есть сортировка по времени..

[rxu]

Затем, что на загруженных форумах на один штамп времени могут прийтись несколько постов.

[rxu]

IMHO только если делать сложный индекс по всем полям запроса

Проблема в том, что запрос формируется динамически. В зависимости от поля сортировки, там могут оказаться и username_clean, и post_time, и post_subject.
Тогда в этот индекс с десяток полей надо добавлять.

[Sumanai]

Проблема в том, что запрос формируется динамически

Большинство использует стандартное отображение.

[istepan]

что на загруженных форумах на один штамп времени могут прийтись несколько постов.

Да, но у них post_id будет все равно уникальный. Там же primary key с авто инкриментом.
В таком случае данную проблем можно решить добавив общий индекс для topic_id и post_visibility, и из ORDER BY убрать post_time.

Попробую.