Пара вопросов спецам по мускулу

через пхп, но красивее )

меняю having на and

Варианты с лимитом без старта и условию по стартовому ID, для обоих запросов выгоды в скорости не приносит )

дали бы explain не через php, нагляднее было бы. У вас там filesort из-за order by, надо индексы смотреть, структуры таблиц (show create table ...) - тогда можно и вывод сделать.

Dimanych, а если еще и left убирать? думаю, просто много данных в f и остается только тройной индекс создать

RAS,

я описывал структуру чуть ранее, индексы только на (id,foto - таблица n) и на (id,user - таблица f), другие индексы нехочу прописывать так как это становится не целесообразно.

Это единственный такой замороченный запрос, другие в основном по индексам и с одной таблицей..

netwind,

Как это left убрать. Ну убрал я его и получилось что и с having и без него теперь более 0.7сек..

Изменилась эта строчка для таблицы n: [Extra] => Using temporary; Using filesort (пропал where)

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

Всё таки оптимизация запросов не лёгкая задача ;)

Dimanych, честно говоря, не понимаю почему с HAVING это быстрее работает. Having без группировки в других субд вообще получается синтаксически неправильным.

может просто погрешности кеширования? вы в курсе про SQL_NO_CACHE ?

А если показать show create table будет просто удобнее воссоздать у себя и потестировать.

Нет, не погрешность кеширования, не 1 раз проверял..

Хотите попробовать, вот таблички, но их нужно забить чем то:

Сколько они занимают у меня

tb_notes 85,712 записей MyISAM cp1251_general_ci 3.9 МБ

tb_fotos 12,654 записей MyISAM cp1251_general_ci 1.0 МБ

в тот раз что то я напутал на счёт того что 20к оценок, оказывается 85к )

Вот насчет фултекстсерча - нет.

А все остальное у него на ура. Заменять MySQL на Oracle или MSSQL (при условии того, что оптимизация на любой СУБД проводится максимальная) смысла нет.

sokol_jack добавил 10.12.2008 в 21:23

Так не сработает?

у меня есть подобный сайт там 110 тыс комментов и 8 тыс фото c похожей структурой БД.

ну нету там никаких сортировок и временных таблиц.

Вот запрос примерно по-вашей методике :

explain select f.*,username as noteuser from `photo_comments` n left join `photo_photos` f on (f.id=n.photo) where n.rating=10.0 having f.approved=1 order by n.id desc limit 500,2;

+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+-----------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref             | rows   | Extra                       |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+-----------------------------+
|  1 | SIMPLE      | n     | ALL    | NULL          | NULL    | NULL    | NULL            | 110103 | Using where; Using filesort |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY       | PRIMARY | 4       | xxxxxxx.n.photo |      1 |                             |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+-----------------------------+

а это до использования травы (having и left join) :

explain select f.*,username as noteuser from `photo_comments` n ,`photo_photos` f where f.id=n.photo and n.rating=10.0 and n.approved=1 order by n.id desc limit 500,2;

+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref             | rows   | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+-------------+
|  1 | SIMPLE      | n     | index  | photo         | PRIMARY | 4       | NULL            | 110103 | Using where |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY       | PRIMARY | 4       | xxxxxxx.n.photo |      1 |             |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+-------------+

Сортировки нет!

Только вот точно оценить время я не могу. Получается примерно одинаково в обоих случаях от 0 до 1 сек.

Дисковый кеш и другие процессы сказываются. Да и 8мб данных это смех. Однако, я считаю, стоит верить explain-у.

там не VPS ?

Может вам mysql "разогнать" ? увеличить sort_buffer_size, join_buffer_size и тд?

netwind, сделал запрос идентичный вашему, всё равно для f => Using temporary; Using filesort

И хочу уточнить, у меня другой запрос, у вас не хватает where для таблицы f

Это у вас

where f.id=n.photo and n.rating=10.0 and n.approved=1

тут участвует только индекс от f.id и таблица n

Это мой

where f.id=n.foto and n.note=10 and f.ok=1 and f.view=0 and f.ero=0

тут участвуют обе таблицы, это и приводит у такому времени запроса 0.7 сек

Кстати что за феномен, убираю "order by n.id desc"

сразу пропадает Using temporary; Using filesort для f

У меня сервер... с оптимизацией самого mysql не заморачивался.. так как пока не прижало ))

Всё по умолчанию, ничего не менял

join buffer size 131,072

sort buffer size 2,097,144

из переменных mysql

а если из order by убрать только desc, сортировка исчезнет?

похоже обратный порядок сортировки order by id desc и какие-то маленькие буферы не позволяет вашему серверу выполнить операцию без сортировки. А у меня позволяют.

а оно вам надо? похоже устранение этой сортировки погоды не сделает. все равно у вас всего 5мб данных. Если не заниматься корявыми левым объединениями, они не умножатся.

вот у меня к ночи нагрузка спала и любой вариант запросов к галерее выполняется за время близкое к нулю, а первичных данных для выборки в n у меня больше - 8 мб.

остаются индексы.