Упрощение sql запросов к блогу. v1.10.7

Здравствуйте.
На сайте давно наблюдается проблема запросы к базе данных очень тормозят сайт, запросы связаны с блогами.
вот проблемные запросы
Как оптимизировать эти запросы? база данных около 2гб, пользователей онлайн всего до 10-15 бывает.

| 139693 | photoclu_photo | localhost | photoclu_photo | Query | 68 | Sending data | SELECT p.*,
u.nickname as author, u.login, u.is_deleted,
b.allow_who as blog_allow_who,
b.seolink as bloglink,
b.title as blog_title,
b.owner as owner, b.user_id as bloglink, up.imageurl, img.fileurl
FROM pc_blog_posts p
INNER JOIN pc_blogs b ON b.id = p.blog_id
LEFT JOIN pc_users u ON u.id = p.user_id
LEFT JOIN pc_user_profiles up ON up.user_id = u.id
LEFT JOIN pc_upload_images img ON img.target_id = p.id AND img.target = 'blog_post' AND img.component = 'blogs'

WHERE p.published = 1 AND b.owner = 'club'
AND (b.allow_who = 'all')
AND (p.allow_who = 'all')

GROUP BY p.id
ORDER BY p.rating DESC
LIMIT 0, 10 |

Данный запрос выполняется порядка минуты. Если сделать EXPLAIN данного запроса, то видно, что сервер анализирует 186 тысяч записей в базе данных при выполнении, при этом использует сортировку в файлах (это очень медленно), думаю, этот запрос и является основной причиной высокой нагрузки.

+----+-------------+-------+--------+--------------------------------------------------------+-----------+---------+--------------------------+--------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------------------------------------+-----------+---------+--------------------------+--------+---------------------------------------------------------------------+
| 1 | SIMPLE | p | ref | PRIMARY,seolink,user_id,title,blog_id,cat_id,allow_who | allow_who | 63 | const,const | 186629 | Using index condition; Using where; Using temporary; Using filesort |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | photoclu_photo.p.blog_id | 1 | Using where |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | photoclu_photo.p.user_id | 1 | NULL |
| 1 | SIMPLE | up | ref | user_id | user_id | 4 | photoclu_photo.u.id | 1 | NULL |
| 1 | SIMPLE | img | ref | target_id | target_id | 4 | photoclu_photo.p.id | 1 | Using where |
+----+-------------+-------+--------+--------------------------------------------------------+-----------+---------+--------------------------+--------+---------------------------------------------------------------------+

Проблемный запрос находится в файле core/classes/blog.class.php, смотрите функцию getPosts

На странице блогов также тормозит следующий запрос:

| 533 | photoclu_photo | localhost | photoclu_photo | Query | 6 | Sending data | SELECT 1
FROM pc_blog_posts p
INNER JOIN pc_blogs b ON b.id = p.blog_id

WHERE p.published = 1 AND b.owner = 'user'
AND (b.allow_who = 'all')
AND (p.allow_who = 'all') |

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

+----+-------------+-------+--------+-------------------+-----------+---------+--------------------------+--------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------+-----------+---------+--------------------------+--------+-----------------------+
| 1 | SIMPLE | p | ref | blog_id,allow_who | allow_who | 63 | const,const | 186318 | Using index condition |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | photoclu_photo.p.blog_id | 1 | Using where |
+----+-------------+-------+--------+-------------------+-----------+---------+--------------------------+--------+-----------------------+

Данный запрос находится в том же файле core/classes/blog.class.php, функция getPostsCount.

@XGluk,
Похоже, что дело не в большом количестве записей в таблице.
Нагенерил 80000 постов. Вот выборка из них:
Это дефолтный 1.10.7
А что у Вас там в запросе за таблицы джойнятся — не ясно.
Похоже, компонент изменен для вывода каких-то картинок. Смотрите индексы во всех таблицах, которые есть в запросе.

Ris,
Все компоненты дефолтные без изменений, обновлялись с 1.10.3 по мере выхода новых версий.
поэксперементировав выяснил что проблема вот в этой строчке файла frontend.php
///////////////////////// ПОСЛЕДНИЕ ПОСТЫ //////////////////////////////////////
//устанавливаем сортировку
$inDB->orderBy('p.pubdate', 'DESC');
дело именно в запросе 'p.pubdate'
отключив эту строку торможение пропадает полностью, но блоги выдаются не последние по дате, а первые которые были созданы.
Как изменить запрос чтобы выдавались и последние блоги и не тормозило?
Может возможно как-то выводить блоги по последним id из базы _blog_posts или ещё какой-то запрос который будет выдавать последние записи не запрашивая дату?

@XGluk,
Вот без этого $inDB->orderBy('p.pubdate', 'DESC'); — никак.
Повесьте на pubdate индекс и посмотрите на результат.

@XGluk,
Повесьте на pubdate индекс и посмотрите на результат.

ALTER TABLE cms_blog_posts ADD INDEX(`pubdate`)

Подскажите куда это вставить, или это sql запрос в phpMyAdmin выполнить?
Ещё заметил такую штуку, сделал вывод последних комментариев, модулем на главную, там тоже выводятся последние комментарии и база комментариев ещё больше чем блогов, но никакого тормоза не наблюдается.
Может можно по аналогии вывода последних комментариев как-то сделать вывод последних блогов?

sql запрос в phpMyAdmin выполнить?

Да

Ris,
С блогами сработало, всё стало летать в Опере но в Хроме почему то тормозит, хостер говорит что Добавленный индекс не используется в запросе.
Но есть такая же проблема с клубами в которых много постов, особенно тормозит при заходе в Блог клуба, в котором много постов. в Клубах где мало постов Блог клуба тормаза нет. Может возможно это решить подобным способом?

Поэксперементировав, выяснил что проблема не в отображении блогов в клубах, а в отображении рубрик и похоже не самих рубрик а счётчиков количества постов в них. Убрав рубрики всё начинает летать.
Где отключить подсчёт количества постов в рубриках?

Поэксперементировав, выяснил что проблема не в отображении блогов в клубах, а в отображении рубрик и похоже не самих рубрик а счётчиков количества постов в них. Убрав рубрики всё начинает летать.
Где отключить подсчёт количества постов в рубриках?

скорее всего github.com/instantsoft/icms1/blob/master/core/classes/blog.class.php#L64

ideasdigger,
Попробовал, эта функция убирает блок категорий полностью, а мне нужно отключить подсчёт постов в них, тоесть чтобы в базу данных не было запроса на подсчёт постов в категории.
Либо сделать что-бы, блок категорий кешировался, а то получается при каждом его выводе пересчитывается всё заново, а каждый запрос сильно грузит базу данных.

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

я лишь указан на функцию получения) измени её на это
ну а так же в файле /templates/_default_/components/com_blog_view.tpl убери визуально вывод количества
это
на это

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

более сложный вариант) но тоже реален, можно кэшировать заранее и обновлять кеш лишь при случаях, добавлении/удалении категорий, изменении добавлении/ удалении записей, или просто по таймингу раз там в 5 минут, используя функции cmsCore::isCached(), cmsCore::getCache(), cmsCore::saveCache(),

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

Благодарю всех кто помог и направил на нужный путь!
В общем методом тыка решил выше описанную задачу.
запрос выглядит так
$sql = "SELECT cat.*
FROM cms_blog_cats cat

WHERE cat.blog_id = '$blog_id'
GROUP BY cat.id";

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

* Получает количество постов по условиям
* @param bool $show_all
* @return int
*/
public function getPostsCount($show_all = false){

$pub_where = ($show_all? '1=1': 'p.published = 1');

if(isset($this->owner)) { $pub_where .= " AND b.owner = '{$this->owner}'"; }

Теперь блоги клубов стали просто летать!
В идеале сделать бы кеширование этих данных и функционал бы сохранился и проблема решилась, но моих знаний для этого пока недостаточно, может кто возьмётся?

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

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