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

Тема в разделе "Базы данных", создана пользователем irip, 13 янв 2017.

Модераторы: latteo
  1. irip

    irip

    Регистр.:
    12 авг 2006
    Сообщения:
    490
    Симпатии:
    44
    Включил логирование медленных запросов.

    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')))


    Какие правильно ключи сделать на этот запрос?
    Подскажите пожалуйста
     
  2. Black Hat

    Black Hat

    Регистр.:
    15 май 2015
    Сообщения:
    163
    Симпатии:
    104
    Первое - сделать
    Код:
    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);
    
     
  3. irip

    irip

    Регистр.:
    12 авг 2006
    Сообщения:
    490
    Симпатии:
    44
    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;


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

    ALTER TABLE cms_content ADD INDEX ( category_id, pubdate, enddate, is_arhive, published, is_end ) ;# Затронута 19741 строка.
    ALTER TABLE cms_category ADD INDEX ( id, NSLeft, NSRight, parent_id ) ;# Затронуто 54 строки.
     
  4. Black Hat

    Black Hat

    Регистр.:
    15 май 2015
    Сообщения:
    163
    Симпатии:
    104
    Код:
    EXPLAIN EXTENDED
    1. вывод до и после.
    2. сколько выполняется запрос до и после? это можно затестить так:
    Код:
    SELECT SQL_NO_CACHE запрос ...
    
    ну и движок я бы сменил на innodb
    Код:
    alter table ... engine=innodb;
    
     
    irip нравится это.
  5. irip

    irip

    Регистр.:
    12 авг 2006
    Сообщения:
    490
    Симпатии:
    44
    На cms_content вот такой ответ:

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

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

    Aborted clients[​IMG] 1 Количество прерванных соединений в связи с потерей связи и неверно закрытым соединением клиента.
    Aborted connects[​IMG] 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
     
    Последнее редактирование модератором: 21 янв 2017
  6. Black Hat

    Black Hat

    Регистр.:
    15 май 2015
    Сообщения:
    163
    Симпатии:
    104
    значит оставьте тот движок, раз там полнотекстовой поиск. походу это джумла. надо работать с EXPAIN EXTENDED и смотреть время выполнения запроса (без кеша)
    хз что там, нужен лог мускула, может надо увеличить max_connections и max_user_connections. какой трафик?
     
    irip нравится это.