Как правильно сделать индекс в таблице?

irip

irip

Гуру форума
Регистрация
12 Авг 2006
Сообщения
542
Реакции
55
Включил логирование медленных запросов.

use 24_basejob;
SET timestamp=1484273718;
SELECT con.*,
cat.title as cat_title, cat.seolink as catseolink,
cat.showdesc,
u.nickname as author,
u.login as user_login
FROM cms_content con
INNER JOIN cms_category cat ON cat.id = con.category_id
LEFT JOIN cms_users u ON u.id = con.user_id
WHERE con.is_arhive = 0
AND (cat.NSLeft >= '1' AND cat.NSRight <= '108' AND cat.parent_id > 0)
AND (con.published = 1 AND con.pubdate <= '2017-01-13 05:15:02' AND (con.is_end=0 OR (con.is_end=1 AND con.enddate >= '2017-01-13 05:15:02')))


Какие правильно ключи сделать на этот запрос?
Подскажите пожалуйста
 
Первое - сделать
Код: 
EXPLAIN EXTENDED сам-запрос-начиная-с-SELECT
Второе - показать все таблицы
Код: 
SHOW CREATE TABLE cms_content;
SHOW CREATE TABLE cms_category;
SHOW CREATE TABLE cms_users;
Весь вывод запостить в тред.
А так, на вскидку,
Код: 
ALTER TABLE cms_content ADD INDEX(category_id, pubdate, enddate, is_arhive, published, is_end);
ALTER TABLE cms_category ADD INDEX(id, NSLeft, NSRight, parent_id);
И если нет, то
Код: 
ALTER TABLE cms_users ADD INDEX(id);
 
CREATE TABLE `#__content` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL DEFAULT '1',
`pubdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`enddate` date NOT NULL,
`is_end` tinyint(1) NOT NULL,
`title` varchar(200) NOT NULL,
`description` text NOT NULL,
`content` mediumtext,
`published` tinyint(1) NOT NULL DEFAULT '1',
`hits` int(11) NOT NULL DEFAULT '0',
`rating` int(11) NOT NULL default '0',
`meta_desc` text NOT NULL,
`meta_keys` text NOT NULL,
`showtitle` tinyint(1) NOT NULL DEFAULT '1',
`showdate` tinyint(1) NOT NULL DEFAULT '1',
`showlatest` tinyint(1) NOT NULL DEFAULT '1',
`showpath` tinyint(1) NOT NULL DEFAULT '1',
`ordering` int(11) NOT NULL,
`comments` tinyint(1) NOT NULL DEFAULT '1',
`is_arhive` tinyint(1) NOT NULL,
`seolink` varchar(200) NOT NULL,
`canrate` tinyint(1) NOT NULL DEFAULT '1',
`pagetitle` varchar(255) NOT NULL,
`url` varchar(100) NOT NULL,
`tpl` varchar(50) NOT NULL DEFAULT 'com_content_read.tpl',
PRIMARY KEY (`id`),
KEY `seolink` (`seolink`),
KEY `category_id` (`category_id`),
KEY `user_id` (`user_id`),
FULLTEXT KEY `title` (`title`),
FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE `#__category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`title` varchar(200) NOT NULL,
`description` text NOT NULL,
`published` tinyint(1) NOT NULL,
`showdate` tinyint(1) NOT NULL DEFAULT '1',
`showcomm` tinyint(1) NOT NULL DEFAULT '1',
`orderby` varchar(30) NOT NULL DEFAULT 'date',
`orderto` varchar(4) NOT NULL DEFAULT 'asc',
`modgrp_id` int(11) NOT NULL,
`NSLeft` int(11) NOT NULL,
`NSRight` int(11) NOT NULL,
`NSLevel` int(11) NOT NULL,
`NSDiffer` varchar(11) NOT NULL,
`NSIgnore` int(11) NOT NULL,
`ordering` int(11) NOT NULL,
`maxcols` int(11) NOT NULL DEFAULT '1',
`showtags` tinyint(1) NOT NULL DEFAULT '1',
`showrss` tinyint(1) NOT NULL DEFAULT '1',
`showdesc` tinyint(1) NOT NULL,
`is_public` tinyint(1) NOT NULL,
`photoalbum` text NOT NULL,
`seolink` varchar(200) NOT NULL,
`url` varchar(100) NOT NULL,
`tpl` varchar(50) NOT NULL DEFAULT 'com_content_view.tpl',
`cost` varchar(5) NOT NULL,
`pagetitle` varchar(200) NOT NULL DEFAULT '',
`meta_keys` varchar(250) NOT NULL DEFAULT '',
`meta_desc` varchar(250) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `seolink` (`seolink`),
KEY `parent_id` (`parent_id`),
KEY `NSLeft` (`NSLeft`,`NSRight`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE `#__users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`group_id` int(11) NOT NULL DEFAULT '1',
`login` varchar(100) NOT NULL,
`nickname` varchar(100) NOT NULL,
`password` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`phone` varchar(12) NOT NULL DEFAULT '',
`icq` varchar(15) NOT NULL,
`regdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`logdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`birthdate` date NOT NULL DEFAULT '0000-00-00',
`is_locked` tinyint(1) NOT NULL,
`is_deleted` tinyint(1) NOT NULL,
`is_logged_once` tinyint(1) NOT NULL DEFAULT '0',
`rating` int(11) NOT NULL,
`points` int(11) NOT NULL,
`last_ip` varchar(15) NOT NULL,
`status` varchar(255) NOT NULL,
`status_date` datetime NOT NULL,
`invited_by` int(11) DEFAULT NULL,
`invdate` datetime DEFAULT NULL,
`openid` varchar(250) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `login` (`login`),
KEY `email` (`email`),
KEY `birthdate` (`birthdate`),
KEY `group_id` (`group_id`),
KEY `invited_by` (`invited_by`),
KEY `openid` (`openid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;


Выполнил рекомендованные запросы

Для просмотра ссылки Войди или Зарегистрируйся cms_content ADD INDEX ( category_id, pubdate, enddate, is_arhive, published, is_end ) ;# Затронута 19741 строка.
Для просмотра ссылки Войди или Зарегистрируйся cms_category ADD INDEX ( id, NSLeft, NSRight, parent_id ) ;# Затронуто 54 строки.
 
Код: 
EXPLAIN EXTENDED
1. вывод до и после.
2. сколько выполняется запрос до и после? это можно затестить так:
Код: 
SELECT SQL_NO_CACHE запрос ...
ну и движок я бы сменил на innodb
Код: 
alter table ... engine=innodb;
 
  • Нравится
Реакции: irip
На cms_content вот такой ответ:

#1214 - The used table type doesn't support FULLTEXT indexes

Да, и еще, по самой базе данных

Aborted clientsДля просмотра ссылки Войди или Зарегистрируйся 1 Количество прерванных соединений в связи с потерей связи и неверно закрытым соединением клиента.
Aborted connectsДля просмотра ссылки Войди или Зарегистрируйся 128 Количество неудавшихся попыток соединения к серверу MySQL.

и это за 12 часов работы!

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

bind-address = 127.0.0.1
default_storage_engine = InnoDB
innodb=on
#default-storage-engine = MyISAM

skip-networking
skip-name-resolve
skip-ndbcluster
key_buffer_size = 256M
max_allowed_packet = 512K
max-heap-table-size = 32M
sort_buffer_size = 256K
net_buffer_length = 8K
read_buffer_size = 32M
write_buffer_size = 48M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 8M

memlock
table_cache = 4096
thread_concurrency = 2

#innodb_use_native_aio = 0
max_heap_table_size = 96M
tmp_table_size = 76M
thread_concurrency = 4
table_definition_cache = 400
max_connections = 50
max_user_connections=80
log-queries-not-using-indexes
long_query_time = 1 # Log anything over 1 second..
slow_query_log_file=/var/log/mysql/log-slow-queries.log

thread_cache_size = 64
open_files_limit = 96M
table_definition_cache = 1024K
table_open_cache = 256
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_file_per_table = 1
innodb_buffer_pool = 32M
innodb_buffer_pool_size = 256M
innodb_log_group_home_dir = /var/lib/mysql/

transaction-isolation = READ-COMMITTED
innodb_lock_wait_timeout=5
innodb_rollback_on_timeout=1
#binlog-format = MIXED
innodb_log_file_size = 200M
innodb_flush_log_at_trx_commit = 0
wait_timeout = 90
interactive_timeout = 50

query_cache_size = 20M
query_cache_type = 2
query_cache_limit= 10M
join_buffer_size = 128M
slow_query_log = 1
log_slow_queries=ON
long_query_time = 10;
log_slow_queries=/var/log/mysql/log-slow-queries.log
#slow_query_log_file=/var/log/mysql-slow-queries.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf
 
Последнее редактирование модератором:
irip написал(а):
На cms_content вот такой ответ:

#1214 - The used table type doesn't support FULLTEXT indexes
Нажмите для раскрытия...
значит оставьте тот движок, раз там полнотекстовой поиск. походу это джумла. надо работать с EXPAIN EXTENDED и смотреть время выполнения запроса (без кеша)
irip написал(а):
Aborted connectsДля просмотра ссылки Войди или Зарегистрируйся
Нажмите для раскрытия...
хз что там, нужен лог мускула, может надо увеличить max_connections и max_user_connections. какой трафик?
 
  • Нравится
Реакции: irip
Войдите или зарегистрируйтесь для ответа.
Назад
Сверху