Правильные индексы для запроса

Question

Правильные индексы для запроса

У меня есть этот запрос, который занимает 1,7 секунды для выполнения:

SELECT
    i.id_items AS id, 
    i.names, 
    GROUP_CONCAT(DISTINCT iafv.face_value ORDER BY iafv.face_value ASC SEPARATOR ', ') AS facesValues, 
    GROUP_CONCAT(DISTINCT iacut.translation ORDER BY iacut.translation ASC SEPARATOR ', ') AS currencies, 
    GROUP_CONCAT(DISTINCT cout.translation ORDER BY cout.translation ASC SEPARATOR ', ') AS countries, 
    GROUP_CONCAT(DISTINCT iacot.translation ORDER BY iacot.translation ASC SEPARATOR ', ') AS compositions, 
    GROUP_CONCAT(DISTINCT iacc.catalog_code ORDER BY iacc.catalog_code ASC SEPARATOR ', ') AS catalogCodes, 
    GROUP_CONCAT(DISTINCT iaio.issues_on ORDER BY iaio.issues_on ASC SEPARATOR ', ') AS issuesOn, 
    GROUP_CONCAT(DISTINCT iali.last_issues ORDER BY iali.last_issues ASC SEPARATOR ', ') AS latestIssues, 
    MIN(ucfs.price) AS minPrice, 
    SUM(ucfs.units) AS totalUnits           
FROM
    (SELECT
        i.id_items, 
        GROUP_CONCAT(DISTINCT iant.translation ORDER BY iant.translation asc SEPARATOR ', ') AS names   
    FROM
       items i 
    INNER JOIN languages AS l ON l.language_code = "en"
    LEFT JOIN items_atr_names_match AS ianm ON ianm.id_items = i.id_items
    LEFT JOIN items_atr_names_translations AS iant ON iant.id_items_atr_names = ianm.id_items_atr_names AND iant.id_language_code = l.id_languages 
    LEFT JOIN items_atr_names AS ian ON ian.id_items_atr_names = ianm.id_items_atr_names 

    GROUP BY id_items
    ORDER BY names asc

    LIMIT 0, 20) AS i

INNER JOIN languages AS l ON l.language_code = "en"

LEFT JOIN items_atr_faces_values_match AS iafvm ON iafvm.id_items = i.id_items 
LEFT JOIN items_atr_faces_values AS iafv ON iafv.id_items_atr_faces_values = iafvm.id_items_atr_faces_values

LEFT JOIN items_atr_currencies_match AS iacum ON iacum.id_items = i.id_items 
LEFT JOIN items_atr_currencies_translations AS iacut ON iacut.id_items_atr_currencies = iacum.id_items_atr_currencies AND iacut.id_language_code = l.id_languages 
LEFT JOIN items_atr_currencies AS iacu ON iacu.id_items_atr_currencies = iacum.id_items_atr_currencies

LEFT JOIN items_atr_countries_match AS iacoum ON iacoum.id_items = i.id_items 
LEFT JOIN items_atr_countries_translations AS cout ON cout.id_items_atr_countries = iacoum.id_items_atr_countries AND cout.id_language_code = l.id_languages 
LEFT JOIN items_atr_countries AS cou ON cou.id_items_atr_countries = iacoum.id_items_atr_countries

LEFT JOIN items_atr_compositions_match AS iacom ON iacom.id_items = i.id_items 
LEFT JOIN items_atr_compositions_translations AS iacot ON iacot.id_items_atr_compositions = iacom.id_items_atr_compositions AND iacot.id_language_code = l.id_languages 
LEFT JOIN items_atr_compositions AS iaco ON iaco.id_items_atr_compositions = iacom.id_items_atr_compositions

LEFT JOIN items_atr_catalog_codes_match AS iaccm ON iaccm.id_items = i.id_items
LEFT JOIN items_atr_catalog_codes AS iacc ON iacc.id_items_atr_catalog_codes = iaccm.id_items_atr_catalog_codes  

LEFT JOIN items_atr_issues_on_match AS iaiom ON iaiom.id_items = i.id_items
LEFT JOIN items_atr_issues_on AS iaio ON iaio.id_items_atr_issues_on = iaiom.id_items_atr_issues_on

LEFT JOIN items_atr_last_issues_match AS ialim ON ialim.id_items = i.id_items
LEFT JOIN items_atr_last_issues AS iali ON iali.id_items_atr_last_issues = ialim.id_items_atr_last_issues

LEFT JOIN users_coins_for_sale AS ucfs ON ucfs.id_items = i.id_items
LEFT JOIN users AS u ON u.id_users = ucfs.id_users AND u.status = 'active'

GROUP BY id

ORDER BY names asc

Это создание sql для таблиц с информацией индекса:

CREATE TABLE IF NOT EXISTS `items` (
    `id_items` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_users` int(11) unsigned NOT NULL DEFAULT '0',
    `date` date NOT NULL,
    `views` bigint(20) NOT NULL DEFAULT '0',
    `status` enum('draft','published') NOT NULL DEFAULT 'draft',
    PRIMARY KEY (`id_items`),
    UNIQUE KEY `id_items` (`id_items`),
    KEY `id_users` (`id_users`),
    CONSTRAINT `FK_items_users` FOREIGN KEY (`id_users`) REFERENCES `users` (`id_users`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_catalog_codes` (
    `id_items_atr_catalog_codes` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `catalog_code` varchar(100) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_catalog_codes`),
    UNIQUE KEY `id_items_atr_catalog_codes` (`id_items_atr_catalog_codes`),
    FULLTEXT KEY `catalog_code` (`catalog_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_catalog_codes_match` (
    `id_items_atr_catalog_codes_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_catalog_codes` int(11) unsigned NOT NULL,
    `id_items` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id_items_atr_catalog_codes_match`),
    UNIQUE KEY `id_items_atr_catalog_codes_match` (`id_items_atr_catalog_codes_match`),
    KEY `id_items` (`id_items`),
    KEY `id_items_atr_catalog_codes` (`id_items_atr_catalog_codes`),
    CONSTRAINT `FK_catalog_codes_items_match_catalog_codes` FOREIGN KEY (`id_items_atr_catalog_codes`) REFERENCES `items_atr_catalog_codes` (`id_items_atr_catalog_codes`),
    CONSTRAINT `FK_catalog_codes_items_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_compositions` (
    `id_items_atr_compositions` int(11) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id_items_atr_compositions`),
    UNIQUE KEY `id_items_atr_compositions` (`id_items_atr_compositions`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_compositions_match` (
    `id_items_atr_compositions_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_compositions` int(11) unsigned NOT NULL DEFAULT '0',
    `id_items` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_compositions_match`),
    UNIQUE KEY `id_items_atr_compositions_match` (`id_items_atr_compositions_match`),
    KEY `id_items` (`id_items`),
    KEY `id_items_atr_compositions` (`id_items_atr_compositions`),
    CONSTRAINT `FK_compositions_items_match_compositions` FOREIGN KEY (`id_items_atr_compositions`) REFERENCES `items_atr_compositions` (`id_items_atr_compositions`),
    CONSTRAINT `FK_compositions_items_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_compositions_translations` (
    `id_items_atr_compositions_translations` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_compositions` int(11) unsigned NOT NULL DEFAULT '0',
    `id_language_code` int(11) unsigned NOT NULL DEFAULT '0',
    `translation` varchar(250) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_compositions_translations`),
    UNIQUE KEY `id_items_atr_compositions_translations` (`id_items_atr_compositions_translations`),
    KEY `id_language_code` (`id_language_code`),
    KEY `id_items_atr_compositions` (`id_items_atr_compositions`),
    FULLTEXT KEY `translation` (`translation`),
    CONSTRAINT `FK_compositions_translations_compositions` FOREIGN KEY (`id_items_atr_compositions`) REFERENCES `items_atr_compositions` (`id_items_atr_compositions`),
    CONSTRAINT `FK_compositions_translations_languages` FOREIGN KEY (`id_language_code`) REFERENCES `languages` (`id_languages`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_countries` (
    `id_items_atr_countries` int(11) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id_items_atr_countries`),
    UNIQUE KEY `id_items_atr_countries` (`id_items_atr_countries`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `items_atr_countries_match` (
    `id_items_atr_countries_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_countries` int(11) unsigned NOT NULL,
    `id_items` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id_items_atr_countries_match`),
    UNIQUE KEY `items_atr_countries_match` (`id_items_atr_countries_match`),
    KEY `id_items` (`id_items`),
    KEY `id_items_atr_countries` (`id_items_atr_countries`),
    CONSTRAINT `FK__countries` FOREIGN KEY (`id_items_atr_countries`) REFERENCES `items_atr_countries` (`id_items_atr_countries`),
    CONSTRAINT `FK__items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_countries_translations` (
    `id_items_atr_countries_translations` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_countries` int(11) unsigned NOT NULL DEFAULT '0',
    `id_language_code` int(11) unsigned NOT NULL DEFAULT '0',
    `translation` varchar(250) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_countries_translations`),
    UNIQUE KEY `id_items_atr_countries_translations` (`id_items_atr_countries_translations`),
    KEY `id_items_atr_countries` (`id_items_atr_countries`),
    KEY `id_language_code` (`id_language_code`),
    FULLTEXT KEY `translation` (`translation`),
    CONSTRAINT `FK__items_atr_countries` FOREIGN KEY (`id_items_atr_countries`) REFERENCES `items_atr_countries` (`id_items_atr_countries`),
    CONSTRAINT `FK_items_atr_countries_translations_languages` FOREIGN KEY (`id_language_code`) REFERENCES `languages` (`id_languages`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_currencies` (
    `id_items_atr_currencies` int(11) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id_items_atr_currencies`),
    UNIQUE KEY `id_items_atr_currencies` (`id_items_atr_currencies`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_currencies_match` (
    `id_items_atr_currencies_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_currencies` int(11) unsigned NOT NULL DEFAULT '0',
    `id_items` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_currencies_match`),
    UNIQUE KEY `id_items_atr_currencies_match` (`id_items_atr_currencies_match`),
    KEY `id_items_atr_currencies` (`id_items_atr_currencies`),
    KEY `id_items` (`id_items`),
    CONSTRAINT `FK_items_atr_currencies_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_items_atr_currencies_match_items_atr_currencies` FOREIGN KEY (`id_items_atr_currencies`) REFERENCES `items_atr_currencies` (`id_items_atr_currencies`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_currencies_translations` (
    `id_items_atr_currencies_translations` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_currencies` int(11) unsigned NOT NULL DEFAULT '0',
    `id_language_code` int(11) unsigned NOT NULL DEFAULT '0',
    `translation` varchar(255) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_currencies_translations`),
    UNIQUE KEY `id_items_atr_currencies_translations` (`id_items_atr_currencies_translations`),
    KEY `id_items_atr_currencies` (`id_items_atr_currencies`),
    KEY `id_language_code` (`id_language_code`),
    FULLTEXT KEY `translation` (`translation`),
    CONSTRAINT `FK_items_atr_currencies_translations_items_atr_currencies` FOREIGN KEY (`id_items_atr_currencies`) REFERENCES `items_atr_currencies` (`id_items_atr_currencies`),
    CONSTRAINT `FK_items_atr_currencies_translations_languages` FOREIGN KEY (`id_language_code`) REFERENCES `languages` (`id_languages`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_faces_values` (
    `id_items_atr_faces_values` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `face_value` decimal(11,2) unsigned NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`id_items_atr_faces_values`),
    KEY `face_value` (`face_value`),
    KEY `id-fv` (`id_items_atr_faces_values`,`face_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_faces_values_match` (
    `id_items_atr_faces_values_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_faces_values` int(11) unsigned NOT NULL DEFAULT '0',
    `id_items` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_faces_values_match`),
    UNIQUE KEY `id_items_atr_faces_values_match` (`id_items_atr_faces_values_match`),
    KEY `id_items_atr_faces_values` (`id_items_atr_faces_values`),
    KEY `id_items` (`id_items`),
    KEY `idItems-IdFaceValue` (`id_items`,`id_items_atr_faces_values`),
    CONSTRAINT `FK_items_atr_faces_values_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_items_atr_faces_values_match_items_atr_faces_values` FOREIGN KEY (`id_items_atr_faces_values`) REFERENCES `items_atr_faces_values` (`id_items_atr_faces_values`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_issues_on` (
    `id_items_atr_issues_on` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `issues_on` varchar(6) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_issues_on`),
    UNIQUE KEY `id_items_atr_issues_on` (`id_items_atr_issues_on`),
    FULLTEXT KEY `issues_on` (`issues_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_issues_on_match` (
    `id_items_atr_issues_on_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_issues_on` int(11) unsigned NOT NULL,
    `id_items` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id_items_atr_issues_on_match`),
    UNIQUE KEY `id_items_atr_issues_on_match` (`id_items_atr_issues_on_match`),
    KEY `id_items_atr_issues_on` (`id_items_atr_issues_on`),
    KEY `id_items` (`id_items`),
    CONSTRAINT `FK_items_atr_issues_on_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_items_atr_issues_on_match_items_atr_issues_on` FOREIGN KEY (`id_items_atr_issues_on`) REFERENCES `items_atr_issues_on` (`id_items_atr_issues_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_last_issues` (
    `id_items_atr_last_issues` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `last_issues` varchar(6) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_last_issues`),
    UNIQUE KEY `id_items_atr_last_issues` (`id_items_atr_last_issues`),
    FULLTEXT KEY `last_issues` (`last_issues`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_last_issues_match` (
    `id_items_atr_last_issues_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_last_issues` int(11) unsigned NOT NULL DEFAULT '0',
    `id_items` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_last_issues_match`),
    UNIQUE KEY `id_items_atr_last_issues_match` (`id_items_atr_last_issues_match`),
    KEY `id_items_atr_last_issues` (`id_items_atr_last_issues`),
    KEY `id_items` (`id_items`),
    CONSTRAINT `FK_items_atr_last_issues_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_items_atr_last_issues_match_items_atr_last_issues` FOREIGN KEY (`id_items_atr_last_issues`) REFERENCES `items_atr_last_issues` (`id_items_atr_last_issues`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_names` (
    `id_items_atr_names` int(11) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id_items_atr_names`),
    UNIQUE KEY `id_items_atr_names` (`id_items_atr_names`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_names_match` (
    `id_items_atr_names_match` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_names` int(11) unsigned NOT NULL DEFAULT '0',
    `id_items` int(11) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_names_match`),
    UNIQUE KEY `id_items_atr_names_match` (`id_items_atr_names_match`),
    KEY `id_items_atr_names` (`id_items_atr_names`),
    KEY `id_items` (`id_items`),
    CONSTRAINT `FK_items_atr_names_match_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_items_atr_names_match_items_atr_names` FOREIGN KEY (`id_items_atr_names`) REFERENCES `items_atr_names` (`id_items_atr_names`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `items_atr_names_translations` (
    `id_items_atr_names_translations` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items_atr_names` int(11) unsigned NOT NULL DEFAULT '0',
    `id_language_code` int(11) unsigned NOT NULL DEFAULT '0',
    `translation` varchar(250) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id_items_atr_names_translations`),
    UNIQUE KEY `id_items_atr_names_translations` (`id_items_atr_names_translations`),
    KEY `id_language_code` (`id_language_code`),
    KEY `id_items_atr_names` (`id_items_atr_names`),
    KEY `translation_indx` (`translation`),
    FULLTEXT KEY `translation` (`translation`),
    CONSTRAINT `FK_items_atr_names_translations_items_atr_names` FOREIGN KEY (`id_items_atr_names`) REFERENCES `items_atr_names` (`id_items_atr_names`),
    CONSTRAINT `FK_items_atr_names_translations_languages` FOREIGN KEY (`id_language_code`) REFERENCES `languages` (`id_languages`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `languages` (
    `id_languages` int(11) unsigned NOT NULL,
    `language_code` char(2) NOT NULL,
    PRIMARY KEY (`id_languages`),
    UNIQUE KEY `language_code` (`language_code`),
    KEY `id-language_code` (`id_languages`,`language_code`),
    KEY `language-code` (`language_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `languages_translations` (
    `id_languages_translations` int(11) unsigned NOT NULL,
    `id_languages` int(11) unsigned NOT NULL,
    `id_language_code` int(11) unsigned NOT NULL,
    `translation` varchar(255) NOT NULL,
    PRIMARY KEY (`id_languages_translations`),
    UNIQUE KEY `id_languages_translations` (`id_languages_translations`),
    KEY `id_languages` (`id_languages`),
    KEY `language_code` (`id_language_code`),
    FULLTEXT KEY `translation` (`translation`),
    CONSTRAINT `FK_languages_translations_languages` FOREIGN KEY (`id_languages`) REFERENCES `languages` (`id_languages`),
    CONSTRAINT `FK_languages_translations_languages_2` FOREIGN KEY (`id_language_code`) REFERENCES `languages` (`id_languages`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `users_coins_for_sale` (
    `id_users_coins_for_sale` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `id_items` int(11) unsigned NOT NULL,
    `id_users` int(11) unsigned NOT NULL,
    `units` int(5) unsigned NOT NULL,
    `year` varchar(5) NOT NULL,
    `grade` enum('good','very_good','fine','very_fine','extra_fine','about_uncirculated','uncirculated','brilliant_uncirculated','proof') NOT NULL,
    `price` decimal(8,2) unsigned NOT NULL,
    `picture` varchar(500) DEFAULT NULL,
    `comment` varchar(500) DEFAULT NULL,
    `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id_users_coins_for_sale`),
    UNIQUE KEY `id_users_coins_for_sale_unique` (`id_users_coins_for_sale`),
    KEY `id_items` (`id_items`),
    KEY `id_users` (`id_users`),
    KEY `year` (`year`),
    KEY `grade` (`grade`),
    KEY `id_users_coins_for_sale` (`id_users_coins_for_sale`),
    KEY `idItems-idUsers` (`id_items`,`id_users`),
    CONSTRAINT `FK_users_coins_for_sale_items` FOREIGN KEY (`id_items`) REFERENCES `items` (`id_items`),
    CONSTRAINT `FK_users_coins_for_sale_users` FOREIGN KEY (`id_users`) REFERENCES `users` (`id_users`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Вот объяснение запроса:

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

Таблица предметов содержит 90 тысяч записей, но я думаю, что она недостаточно велика, чтобы занять 1,7 секунды...

Я перешел по этой ссылке, чтобы узнать лучшие практики: /questions/21031090/indeksyi-mysql-kakovyi-luchshie-praktiki/21031110#21031110

Есть ли у вас какие-либо советы о том, как я могу улучшить производительность?

заранее спасибо

-1

mysql performance indexing database-indexes

Источник

user4402808 12 июн '17 в 20:43

1 ответ

Другие вопросы по тегам mysql performance indexing database-indexes

user1766831 28 июн '17 в 06:21 2017-06-28 06:21 · Answer 1 · 2017-06-28 06:21

Если JOINs не 1:1, SUM() может быть раздутым Проверьте значение!
Когда GROUP BY а также ORDER BY отличаются, ни один индекс не поможет избежать сбора предварительных результатов до LIMIT, В противном случае, некоторый индекс может доходить до LIMIT, тем самым избегая полного сканирования.
PRIMARY KEY это UNIQUE KEYтак что последнее является излишним; DROP Это. (Я вижу этот шаблон во всех (?) Таблицах.)
Здесь обсуждаются оптимальные индексы для многих: много таблиц сопоставления.
Это пахнет как схема "ключ-значение" или "EAV", которая отстой.
Иногда это лучше изменить

этот

GROUP_CONCAT(DISTINCT ... )
...
LEFT JOIN ...

в

( SELECT GROUP_CONCAT( ... ) FROM ... )

Приведенное выше предложение особенно важно в производном запросе, поскольку, вероятно, это самая медленная часть. Рекомендую вытащить этот подзапрос, рассчитать время и поработать над его оптимизацией. Обратите внимание, что он может сканировать много строк, но выдает не более 20. Тогда эти 20 не должны быть слишком обременительными для остальных запросов, при условии, что у вас есть хотя бы минимальные индексы.
Увы, это только наполовину полезно - это может избавить от GROUP BY, но он не может избавиться от полного сканирования. Это потому что ORDER BY зависит от вычисленного значения (names). Тем не менее, он должен избавиться от "раздувать-сдуть", вызванные JOINs + GROUP BY,
Избавиться от стола languages, Все, что он делает, это отображает 2-байтовую подобную language_like 'en' в 4 байта INT, Это пустая трата (1) пространства и (2) JOINs,

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