Медленный запрос mysql с несколькими объединениями и порядком

ProgramBox

Медленный запрос mysql с несколькими объединениями и порядком

Post author:admin
Запись опубликована:17 февраля, 2023
Post category:Вопросы по программированию

#mysql #sql #performance

#mysql #sql #Производительность

Вопрос:

Я не знаю, почему этот запрос выполняется так медленно с «ПОРЯДКОМ ПО» (около 15 секунд). Все поля в предложениях ON и ORDER BY имеют индексы.

 SELECT
    t_prediction.*,
    t_column.*,
    t_user.*,
    t_match.*,
    t_label.*,
    t_competition.*,

    -- COMPETITION_COUNTRY.t_country_id AS t_competition_country_id,
    COMPETITION_COUNTRY.t_country_confederation_id AS t_competition_country_confederation_id,
    COMPETITION_COUNTRY.t_country_name AS t_competition_country_name,
    COMPETITION_COUNTRY.t_country_flag AS t_competition_country_flag,
    COMPETITION_COUNTRY.t_country_flag_thumbnail AS t_competition_country_flag_thumbnail,

    -- COMPETITION_COUNTRY_CONFEDERATION.t_confederation_id AS t_competition_country_confederation_id,
    COMPETITION_COUNTRY_CONFEDERATION.t_confederation_name AS t_competition_country_confederation_name,
    COMPETITION_COUNTRY_CONFEDERATION.t_confederation_acronym AS t_competition_country_confederation_acronym,
    COMPETITION_COUNTRY_CONFEDERATION.t_confederation_logo AS t_competition_country_confederation_logo,
    COMPETITION_COUNTRY_CONFEDERATION.t_confederation_logo_medium AS t_competition_country_confederation_logo_medium,
    COMPETITION_COUNTRY_CONFEDERATION.t_confederation_logo_thumbnail AS t_competition_country_confederation_logo_thumbnail,

    HOST_TEAM.t_team_id AS t_host_team_id,
    HOST_TEAM.t_team_logo AS t_host_team_logo,
    HOST_TEAM.t_team_logo_medium AS t_host_team_logo_medium,
    HOST_TEAM.t_team_logo_thumbnail AS t_host_team_logo_thumbnail,
    HOST_TEAM.t_team_name AS t_host_team_name,
    HOST_TEAM.t_team_abbr AS t_host_team_abbr,

    HOST_TEAM_COUNTRY.t_country_id AS t_host_team_country_id,
    HOST_TEAM_COUNTRY.t_country_confederation_id AS t_host_team_country_confederation_id,
    HOST_TEAM_COUNTRY.t_country_name AS t_host_team_country_name,
    HOST_TEAM_COUNTRY.t_country_flag AS t_host_team_country_flag,
    HOST_TEAM_COUNTRY.t_country_flag_thumbnail AS t_host_team_country_flag_thumbnail,

    -- HOST_TEAM_COUNTRY_CONFEDERATION.t_confederation_id AS t_host_team_country_confederation_id,
    HOST_TEAM_COUNTRY_CONFEDERATION.t_confederation_name AS t_host_team_country_confederation_name,
    HOST_TEAM_COUNTRY_CONFEDERATION.t_confederation_acronym AS t_host_team_country_confederation_acronym,
    HOST_TEAM_COUNTRY_CONFEDERATION.t_confederation_logo AS t_host_team_country_confederation_logo,
    HOST_TEAM_COUNTRY_CONFEDERATION.t_confederation_logo_medium AS t_host_team_country_confederation_logo_medium,
    HOST_TEAM_COUNTRY_CONFEDERATION.t_confederation_logo_thumbnail AS t_host_team_country_confederation_logo_thumbnail,

    GUEST_TEAM.t_team_id AS t_guest_team_id,
    GUEST_TEAM.t_team_logo AS t_guest_team_logo,
    GUEST_TEAM.t_team_logo_medium AS t_guest_team_logo_medium,
    GUEST_TEAM.t_team_logo_thumbnail AS t_guest_team_logo_thumbnail,
    GUEST_TEAM.t_team_name AS t_guest_team_name,
    GUEST_TEAM.t_team_abbr AS t_guest_team_abbr,

    GUEST_TEAM_COUNTRY.t_country_id AS t_guest_team_country_id,
    GUEST_TEAM_COUNTRY.t_country_confederation_id AS t_guest_team_country_confederation_id,
    GUEST_TEAM_COUNTRY.t_country_name AS t_guest_team_country_name,
    GUEST_TEAM_COUNTRY.t_country_flag AS t_guest_team_country_flag,
    GUEST_TEAM_COUNTRY.t_country_flag_thumbnail AS t_guest_team_country_flag_thumbnail,

    -- GUEST_TEAM_COUNTRY_CONFEDERATION.t_confederation_id AS t_guest_team_country_confederation_id,
    GUEST_TEAM_COUNTRY_CONFEDERATION.t_confederation_name AS t_guest_team_country_confederation_name,
    GUEST_TEAM_COUNTRY_CONFEDERATION.t_confederation_acronym AS t_guest_team_country_confederation_acronym,
    GUEST_TEAM_COUNTRY_CONFEDERATION.t_confederation_logo AS t_guest_team_country_confederation_logo,
    GUEST_TEAM_COUNTRY_CONFEDERATION.t_confederation_logo_medium AS t_guest_team_country_confederation_logo_medium,
    GUEST_TEAM_COUNTRY_CONFEDERATION.t_confederation_logo_thumbnail AS t_guest_team_country_confederation_logo_thumbnail

FROM t_prediction
JOIN t_column ON t_column_id  = t_prediction_column_id
JOIN t_event ON t_event_id  = t_column_event_id
JOIN t_user ON t_user_id = t_column_user_id
JOIN t_match ON t_match_id = t_prediction_match_id
LEFT JOIN t_label ON t_match_label_id = t_label_id
JOIN t_competition ON t_match_competition_id = t_competition_id

JOIN  t_country COMPETITION_COUNTRY
    ON COMPETITION_COUNTRY.t_country_id = t_competition_country_id
LEFT JOIN t_confederation COMPETITION_COUNTRY_CONFEDERATION
    ON t_confederation_id = COMPETITION_COUNTRY.t_country_confederation_id

JOIN t_team HOST_TEAM
    ON HOST_TEAM.t_team_id = t_match_host_id
JOIN t_country HOST_TEAM_COUNTRY
    ON HOST_TEAM_COUNTRY.t_country_id = HOST_TEAM.t_team_country_id
LEFT JOIN t_confederation HOST_TEAM_COUNTRY_CONFEDERATION
    ON HOST_TEAM_COUNTRY_CONFEDERATION.t_confederation_id = HOST_TEAM_COUNTRY.t_country_confederation_id

JOIN t_team GUEST_TEAM
    ON GUEST_TEAM.t_team_id = t_match_guest_id
JOIN t_country GUEST_TEAM_COUNTRY
    ON GUEST_TEAM_COUNTRY.t_country_id = GUEST_TEAM.t_team_country_id
LEFT JOIN t_confederation GUEST_TEAM_COUNTRY_CONFEDERATION
    ON GUEST_TEAM_COUNTRY_CONFEDERATION.t_confederation_id = GUEST_TEAM_COUNTRY.t_country_confederation_id

ORDER BY t_prediction_datetime DESC

Без порядка ПО запрос выполняется примерно за 0,005 секунды

Есть идеи, как я могу решить эту проблему?

РЕДАКТИРОВАТЬ: (определение таблиц)

 CREATE TABLE `t_column` (
  `t_column_id` int(10) NOT NULL AUTO_INCREMENT,
  `t_column_event_id` int(10) NOT NULL,
  `t_column_user_id` int(10) DEFAULT NULL,
  `t_column_redeem_datetime` datetime DEFAULT NULL,
  `t_column_code` varchar(16) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `t_column_status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`t_column_id`),
  KEY `t_column_event_id` (`t_column_event_id`),
  KEY `t_column_user_id` (`t_column_user_id`),
  KEY `t_column_redeem_datetime` (`t_column_redeem_datetime`),
  KEY `t_column_code` (`t_column_code`),
  KEY `t_column_status` (`t_column_status`),
  KEY `t_column_id` (`t_column_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Colonne';



CREATE TABLE `t_competition` (
  `t_competition_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t_competition_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `t_competition_description` text COLLATE utf8_unicode_ci NOT NULL,
  `t_competition_country_id` int(10) NOT NULL,
  `t_competition_logo` varchar(2083) COLLATE utf8_unicode_ci NOT NULL,
  `t_competition_logo_medium` varchar(2083) COLLATE utf8_unicode_ci NOT NULL,
  `t_competition_logo_thumbnail` varchar(2083) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`t_competition_id`),
  KEY `t_competition_id` (`t_competition_id`),
  KEY `t_competition_country_id` (`t_competition_country_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Competizione (campionato, torneo)';

CREATE TABLE `t_confederation` (
  `t_confederation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t_confederation_name` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
  `t_confederation_acronym` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `t_confederation_logo` varchar(2083) COLLATE utf8_unicode_ci NOT NULL,
  `t_confederation_logo_medium` varchar(2083) COLLATE utf8_unicode_ci NOT NULL,
  `t_confederation_logo_thumbnail` varchar(2083) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`t_confederation_id`),
  KEY `t_confederation_id` (`t_confederation_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Football Confederations (ie UEFA)';

CREATE TABLE `t_country` (
  `t_country_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t_country_confederation_id` int(10) NOT NULL,
  `t_country_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `t_country_flag` varchar(2083) COLLATE utf8_unicode_ci DEFAULT NULL,
  `t_country_flag_thumbnail` varchar(2083) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`t_country_id`),
  KEY `t_country_confederation_id` (`t_country_confederation_id`),
  KEY `t_country_id` (`t_country_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Paesi';

CREATE TABLE `t_event` (
  `t_event_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t_event_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `t_event_description` text COLLATE utf8_unicode_ci NOT NULL,
  `t_event_start_datetime` datetime NOT NULL,
  `t_event_end_datetime` datetime NOT NULL,
  `t_event_logo` varchar(2083) COLLATE utf8_unicode_ci NOT NULL,
  `t_event_logo_medium` varchar(2083) COLLATE utf8_unicode_ci NOT NULL,
  `t_event_logo_thumbnail` varchar(2083) COLLATE utf8_unicode_ci NOT NULL,
  `t_event_status` tinyint(2) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`t_event_id`),
  KEY `t_event_id` (`t_event_id`),
  KEY `t_event_start_datetime` (`t_event_start_datetime`),
  KEY `t_event_end_datetime` (`t_event_end_datetime`),
  KEY `t_event_status` (`t_event_status`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Eventi (totoamici mondiali, totoamici campionato, totoamici 2015)';

CREATE TABLE `t_label` (
  `t_label_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t_label_event_id` int(10) unsigned NOT NULL,
  `t_label_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`t_label_id`),
  KEY `t_label_event_id` (`t_label_event_id`),
  KEY `t_label_id` (`t_label_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `t_match` (
  `t_match_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t_match_event_id` int(10) NOT NULL,
  `t_match_competition_id` int(10) NOT NULL,
  `t_match_host_id` int(10) NOT NULL,
  `t_match_guest_id` int(10) NOT NULL,
  `t_match_host_score` int(2) DEFAULT NULL,
  `t_match_guest_score` int(2) DEFAULT NULL,
  `t_match_datetime` datetime NOT NULL,
  `t_match_status` int(2) NOT NULL,
  `t_match_label_id` int(10) NOT NULL,
  PRIMARY KEY (`t_match_id`),
  KEY `t_match_event_id` (`t_match_event_id`),
  KEY `t_match_competition_id` (`t_match_competition_id`),
  KEY `t_match_host_id` (`t_match_host_id`),
  KEY `t_match_guest_id` (`t_match_guest_id`),
  KEY `t_match_datetime` (`t_match_datetime`),
  KEY `t_match_label_id` (`t_match_label_id`),
  KEY `t_match_status` (`t_match_status`),
  KEY `t_match_id` (`t_match_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Incontri';

CREATE TABLE `t_prediction` (
  `t_prediction_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t_prediction_match_id` int(10) unsigned NOT NULL,
  `t_prediction_column_id` int(10) NOT NULL,
  `t_prediction_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `t_prediction_value` varchar(1) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`t_prediction_id`),
  KEY `t_prediction_id` (`t_prediction_id`),
  KEY `t_prediction_match_id` (`t_prediction_match_id`),
  KEY `t_prediction_column_id` (`t_prediction_column_id`),
  KEY `t_prediction_datetime` (`t_prediction_datetime`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='1, X, 2';

CREATE TABLE `t_team` (
  `t_team_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t_team_logo` varchar(2083) COLLATE utf8_unicode_ci NOT NULL,
  `t_team_logo_medium` varchar(2083) COLLATE utf8_unicode_ci NOT NULL,
  `t_team_logo_thumbnail` varchar(2083) COLLATE utf8_unicode_ci NOT NULL,
  `t_team_country_id` int(10) NOT NULL,
  `t_team_name` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `t_team_abbr` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`t_team_id`),
  KEY `t_team_id` (`t_team_id`),
  KEY `t_team_country_id` (`t_team_country_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Squadre';

CREATE TABLE `t_user` (
  `t_user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t_user_confirmed` tinyint(1) NOT NULL DEFAULT '0',
  `t_user_first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `t_user_last_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `t_user_email` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
  `t_user_password` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
  `t_user_salt` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
  `t_user_team_id` int(10) NOT NULL,
  `t_user_photo` varchar(2083) COLLATE utf8_unicode_ci DEFAULT NULL,
  `t_user_photo_medium` varchar(2083) COLLATE utf8_unicode_ci DEFAULT NULL,
  `t_user_photo_thumbnail` varchar(2083) COLLATE utf8_unicode_ci DEFAULT NULL,
  `t_user_confirmation_code` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `t_user_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`t_user_id`),
  UNIQUE KEY `t_user_email_2` (`t_user_email`),
  KEY `t_user_id` (`t_user_id`),
  KEY `t_user_email` (`t_user_email`),
  KEY `t_user_team_id` (`t_user_team_id`),
  KEY `t_user_datetime` (`t_user_datetime`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Utenti';
DROP TABLE IF EXISTS `t_prediction_full`;

Ответ №1:

То, как вы используете подзапросы, — плохая идея. Начните с удаления всех подзапросов, которые ссылаются только на одну таблицу.

Итак, вместо:

 JOIN (
SELECT
    t_team_id AS t_guest_team_id,
    t_team_logo AS t_guest_team_logo,
    t_team_logo_medium AS t_guest_team_logo_medium,
    t_team_logo_thumbnail AS t_guest_team_logo_thumbnail,
    t_team_country_id AS t_guest_team_country_id,
    t_team_name AS t_guest_team_name,
    t_team_abbr AS t_guest_team_abbr
FROM
    t_team
) G

Просто сделайте:

 JOIN t_team G

Затем обратитесь к столбцам, используя G .

MySQL фактически создает производные таблицы, когда вы используете подзапрос. Это ненужные накладные расходы. Плюс MySQL теряет возможность использовать индексы для результата.

1. причина, по которой я использую подзапросы, заключается в том, что в противном случае я больше не могу ссылаться на поля, поскольку их имена дублируются в результатах (php PDO fetch_assoc).

2. я не знаю, о чем вы говорите. не могли бы вы объяснить это, разместив полный ответ? заранее благодарю вас

3. @razorxan . , , Ваш запрос уже используется as в подзапросах. Вместо этого просто используйте это во внешнем запросе.

4. в моем запросе нет такого «внешнего» запроса. Вы предлагаете обернуть весь запрос в select со всеми нужными мне псевдонимами?

5. @razorxan Не используйте SELECT * во внешнем запросе, явно перечислите все столбцы. Используйте SELECT ..., G.team_id AS t_guest_team_id, ... .