Оптимизация стандартного запроса

[Остап]

Помогите оптимизировать такой запрос : ( misc/stats.php ) - вывод модераторов на линк администрация

$DB->query("SELECT m.id, m.name, m.email, m.hide_email, m.location, m.aim_name, m.icq_number,
   	            f.id as forum_id, f.read_perms, f.name as forum_name, c.state
   	            FROM ibf_members m, ibf_categories c
   	            LEFT JOIN ibf_moderators mod ON((m.id=mod.member_id or (mod.is_group=1 and mod.group_id=m.mgroup)))
   	            LEFT JOIN ibf_forums f ON(f.id=mod.forum_id)
   	            WHERE c.id=f.category AND c.state != 0");

 

Время выполнения 32 секунды ( 80 строк ) . Не пашет из за этого

[Balancer]

Остап, пришли EXPLAIN для этого запроса.

Т.е. EXPLAIN SELECT m.id ...

 

А, вообще, непонятно. Выполнил сейчас у себя в виде

SELECT m.id, m.name, m.email, m.hide_email, m.location, m.aim_name, m.icq_number, f.id AS forum_id, f.read_perms, f.name AS forum_name, c.state
FROM ib_members m, ib_categories c
LEFT JOIN ib_moderators `mod` ON ( (
m.id = mod.member_id OR (
mod.is_group =1 AND mod.group_id = m.mgroup
) ) 
)
LEFT JOIN ib_forums f ON ( f.id = mod.forum_id ) 
WHERE c.id = f.category AND c.state !=0

 

получил "Показывает записи ... (1095 всего, Запрос занял 0,0316 сек)"

(только префикс таблиц сменил на свой и mod взял в обратные апострофы, а то MySQL 4.1.2 ругается, это у него оператор.

[Остап]

Balancer

Чуть позже - у меня сервер в дауне, короткое у прова на Т3.

Я делал этот запрос в админе - 32 секунды !!! Причём он стандартный, я ничего не менял. Странно всё это.

Добавлено в [mergetime]1088336223[/mergetime]

Balancer Кстати, та же беда у меня и с 10 постящими сегодня челами ... Через раз ошибка - превышение времени запроса ...

[Остап]

Balancer Это дамп дома :

 

+-------+--------+---------------------+---------+---------+--------------+------+-------------+

| table | type | possible_keys | key | key_len | ref | rows | Extra |

+-------+--------+---------------------+---------+---------+--------------+------+-------------+

| c | ALL | PRIMARY,id | [NULL] | [NULL] | [NULL] | 12 | Using where |

| m | ALL | [NULL] | [NULL] | [NULL] | [NULL] | 6945 | |

| mod | ALL | group_id,member_id | [NULL] | [NULL] | [NULL] | 80 | |

| f | eq_ref | PRIMARY,category,id | PRIMARY | 2 | mod.forum_id | 1 | Using where |

+-------+--------+---------------------+---------+---------+--------------+------+-------------+

 

Кстати дома этот запрос занял 11 сек ... В чём может быть дело ? Может на серваке глючит май скл ???

 

Позже запощу с сервака Эксплэйн

Добавлено в [mergetime]1088339376[/mergetime]

Ну всё равно много, запрос тяжёлый ... Можно его как то переделать ?

Изменено 27 Июня 2004 пользователем Остап

[Остап]

Balancer

 

+-------+--------+---------------------+---------+---------+--------------+------+-------------+

| table | type | possible_keys | key | key_len | ref | rows | Extra |

+-------+--------+---------------------+---------+---------+--------------+------+-------------+

| c | ALL | PRIMARY,id | [NULL] | [NULL] | [NULL] | 12 | Using where |

| m | ALL | [NULL] | [NULL] | [NULL] | [NULL] | 6983 | |

| mod | ALL | group_id,member_id | [NULL] | [NULL] | [NULL] | 82 | |

| f | eq_ref | PRIMARY,category,id | PRIMARY | 2 | mod.forum_id | 1 | Using where |

+-------+--------+---------------------+---------+---------+--------------+------+-------------+

 

Вот с сервера ... Почти одно и то же ...

 

Есть идеи ?

[GiV]

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

[Остап]

Vanish

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

Делаю примерно раз в 2 недели

разбей на три простых да посмотри по другому нельзя "оптимизировать".

Попробую. Спасибо, Vanish

[Balancer]

На ibf_members у тебя, судя по всему, нет индексов.

Попробуй сделать индексы:

 

PRIMARY - m.id

INDEX - m.mgroup

 

Потом полезно ещё будет добавить, если нет:

INDEX mod.is_group

INDEX mod.forum_id

 

Но это уже радикального ускорения не даст.

 

Вообще, странно, что у тебя столько подзапросов без индексов...

Блин, запустил EXPLAIN у себя - там тоже индексы почти не используются.

Так что, наверное, не в них дело. Правда, у меня MySQL 4.1.2alpha, там много чего наоптимизировано.

[Остап]

Balancer

Думаешь, обновить версию и мне ? У меня ещё 4.0.16 стоит ...

[Balancer]

Остап, если тебе не нужен UTF8, то лучше 4.1.x пока не пробовать, это альфы и проблемы с ними есть, иногда - большие. Хотя у меня на 4.1.2alpha и сидит форум с 380 тыс. сообщений и до 20 тыс. хитов в сутки, но возиться с ним приходится постоянно

 

Тем более, что я не уверен, что переход на 4.1 выручит тебя в этой ситуации. Причины же тормозов не известны точно.

 

Кстати, вот. Какая у тебя машина, сколько памяти?

Как настроен /etc/my.cnf? Сколько памяти отведено под индексы, под буфера запросов?

вот кусок моего my.cnf, на машине - 512Мб:

[mysqld]
log_slow_queries=/home/airbase/html/logs/slow-queries.log
long_query_time=10
max_connections = 300
back_log        = 10
default-character-set = utf8
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 48M
thread_cache = 8
query_cache_size= 16M
thread_concurrency = 4
tmpdir          = /home/tmp/

[Остап]

Balancer

Машина слабая. Пень 3 800 МГц. Памяти 256 М. Но с другой стороны там больше ничего нет - форум и сайт. Хотя у меня тоже 20 тыс хитов и 110 тыс сообщений. Может такой машины и мало уже ...

my.cnf чёт не нашёл поиском В винде может он другой ?

есть у меня my.ini но там таких параметров нет

#This File was made using the WinMySQLAdmin 1.4 Tool
#28.06.2004 21:37:12

#Uncomment or Add only the keys that you know how works.
#Read the MySQL Manual for instructions

[mysqld]
basedir=
#bind-address=
datadir=
#language=C:
#slow query log#=
#tmpdir#=
#port=
#set-variable=key_buffer=16M
[WinMySQLadmin]

Изменено 29 Июня 2004 пользователем Остап

[Balancer]

Да, в принципе, 256Мб может быть уже маловато :-/

 

В windows - да, оно my.ini зовётся.

А так - значения по умолчанию стоят, когда не указаны. Посмотри через phpMyAdmin или по команде MySQL 'SHOW VARIABLES;'

 

Если что-то сильно маленькое - попробуй увеличить

Вообще, в дистрибутиве должны быть образцы my.cnf/my.ini для машин разной мощности:

my-huge.cnf

my-large.cnf

my-medium.cnf

my-small.cnf

 

Вот сравнивай их

[Song]

Да ничего удивительного нет, если в ibf_members много пользователей.

Ведь как в данному случае работает MySQL ?

Он отбирает всю администрацию проходя по всей ibf_members. Чтобы это сделать, он джойнит к каждой записи табличики ibf_members ibf_moderators, а потом к ним обоим ibf_forums. После этого все три таблицы по ненулевым ячейкам собираются из временных таблиц и выдаются результатом. Поэтому ИМХО никакого криминала нет.

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

[Остап]

Song

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

[GiV]

лучше в статику вынести это дело вот на forum.1tv.ru та же проблема.

 

Но на этом форуме тоже за 7000 мэмбэров и тем не менее открывается моментально ...

тут модераторы не закреплены за форумами, здесь запрос работает быстрее.

[Song]

Остап

логика простая. Смотришь запросы ёмкие, выписываешь на бумажку поля, поиск по которым идёт в WHERE. Эти поля должны быть проиндексированы.

Выводишь индекса таблицы SHOW INDEX FROM table_name. Смотришь какие.

Если не хватает, добавляешь - ALTER TABLE table_name ADD INDEX name (field_name)

name не обязательный параметр.

[Balancer]

для запросов, типа f(field1) AND/OR f(field2) ещё хорошо делать парыне индексы для нескольких полей. Правда, при большом числе сочетаний они могут много места занимать.

 

А рулить индексами (да и вообще БД) удобнее всего через phpMyAdmin

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

[Остап]

Спасибо ... Скажите, а сёрчу поможет быстрее работать если проиндексировать поле пост в таблице постс ?

[Song]

оно проиндексировано.

Ты что как же без этого-то?