mysql Copying to tmp table
Форум — Web-development
стала жутко тормозить БД mysql. выполнил профилирование запроса и ужаснулся сам запрос:
SELECT DISTINCT(n.nid), n.title, l.comment_count
FROM node n
INNER JOIN node_comment_statistics l ON n.nid = l.nid
INNER JOIN node_access na ON na.nid = n.nid
WHERE (na.grant_view >= 1 AND
(
(na.gid = 0 AND na.realm = 'all') OR
(na.gid = 2 AND na.realm = forum_access') OR
(na.gid = 6 AND na.realm = 'forum_access')
)
) AND
( n.type = 'forum' AND n.status = 1 )
ORDER BY n.nid DESC
LIMIT 0, 11;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| Copying to tmp table | 3.865768 | -- что это??
cat /etc/mysql/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
character-set-server = utf8
default-character-set = utf8
user = mysql
port = 3306
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
log-error = /var/log/mysql/mysqld.err
basedir = /usr
datadir = /var/lib/mysql
#skip-locking
#skip-innodb
language = /usr/share/mysql/english
bind-address = 0.0.0.0
connect_timeout=30
interactive_timeout=3600
innodb_buffer_pool_size=1G
join_buffer_size=16M
key_buffer_size=92M
max_connections=300
max_heap_table_size=512M
myisam_sort_buffer_size=32M
old_passwords=1
query_cache_size=392M
query_cache_limit=4M
skip-bdb
skip-innodb
thread_cache_size=4
tmp_table_size=292M
table_cache=500
wait_timeout=7200
# logs #
log-error = /var/log/mysql/mysqld.err
set-variable=long_query_time=4
log-slow-queries=/var/log/mysql/slow.log
#log=/tmp/MySQL.log
#log-queries-not-using-indexes
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
err-log = /var/log/mysql/mysql.err
на машинке 8 Гб памяти кроме mysql ничего ресурсоемкого нет
чего поменять/посмотреть можно?