目前手头有个查询：
SELECT LPP.learning_project_pupilID, SL.serviceID, MAX(LPPO.start_date), SUM(LPPOT.license_mode_value) totalAssignedLicenses
FROM t_services_licenses SL
INNER JOIN t_pupils_offers_services POS ON POS.service_licenseID = SL.service_licenseID
INNER JOIN j_learning_projects_pupils_offers LPPO ON LPPO.learning_project_pupil_offerID = POS.learning_project_pupil_offerID
INNER JOIN j_learning_projects_pupils LPP ON LPPO.learning_project_pupilID = LPP.learning_project_pupilID
INNER JOIN j_learning_projects_pupils_offers_tracking LPPOT ON LPPOT.pupil_offer_serviceID = POS.pupil_offer_serviceID
INNER JOIN t_filters_items FI ON FI.itemID = LPP.learning_project_pupilID_for_filter_join
WHERE FI.filterID = '4dce2235-aafd-4ba2-b248-c137ad6ce8ca'
AND SL.serviceID IN ('OnlineConversationClasses', 'TwentyFourSeven')
GROUP BY LPP.learning_project_pupilID, SL.serviceID
查询非常慢，需要耗时半个多小时之多。
下面是表的一些详细信息：
t_filters_items表：
j_learning_projects_pupils_offers_tracking表：
j_learning_projects_pupils表：
j_learning_projects_pupils_offers表：
t_pupils_offers_services表：
t_services_licenses表：
执行计划如下：
sql脚本如下：
DROP TABLE IF EXISTS t_services_licenses;
CREATE TABLE t_services_licenses (
service_licenseID varchar(36) NOT NULL,
serviceID varchar(36) NOT NULL,
disciplineID varchar(36) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
serial_key varchar(50) DEFAULT NULL,
deleted tinyint(3) unsigned NOT NULL DEFAULT '',
PRIMARY KEY (service_licenseID),
KEY FK_t_services_licenses_serviceID (serviceID),
KEY IDX_disciplineID (disciplineID),
KEY IDX_deleted (deleted),
CONSTRAINT FK_t_services_licenses_serviceID FOREIGN KEY (serviceID) REFERENCES p_services (serviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS t_pupils_offers_services;
CREATE TABLE t_pupils_offers_services (
pupil_offer_serviceID varchar(36) NOT NULL,
learning_project_pupil_offerID varchar(36) NOT NULL,
service_licenseID varchar(36) NOT NULL,
triggered_pupil_offer_serviceID varchar(36) DEFAULT NULL,
triggered_right_of_use_typeID int(10) unsigned DEFAULT NULL,
triggered_right_of_use_value bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (pupil_offer_serviceID),
KEY FK_t_pupils_offers_services_offer_serviceID (service_licenseID),
KEY IDX_ID_SERVICE (learning_project_pupil_offerID,service_licenseID),
CONSTRAINT FK_t_pupils_offers_services_lppoID FOREIGN KEY (learning_project_pupil_offerID) REFERENCES j_learning_projects_pupils_offers (learning_project_pupil_offerID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FK_t_pupils_offers_services_slID FOREIGN KEY (service_licenseID) REFERENCES t_services_licenses (service_licenseID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS j_learning_projects_pupils_offers;
CREATE TABLE j_learning_projects_pupils_offers (
learning_project_pupil_offerID varchar(36) NOT NULL,
learning_project_pupilID bigint(20) unsigned NOT NULL,
offerID varchar(36) NOT NULL,
start_date datetime NOT NULL,
end_date datetime NOT NULL,
deleted tinyint(3) unsigned NOT NULL DEFAULT '',
interruption_count int(10) unsigned NOT NULL DEFAULT '',
PRIMARY KEY (learning_project_pupil_offerID),
KEY FK_j_learning_projects_pupils_offers_projID (learning_project_pupilID),
KEY FK_j_learning_projects_pupils_offers_offerID (offerID),
KEY IDX_start_date (start_date) USING BTREE,
KEY IDX_end_date (end_date) USING BTREE,
KEY IDX_deleted (deleted),
CONSTRAINT FK_LPPO_LP FOREIGN KEY (learning_project_pupilID) REFERENCES j_learning_projects_pupils (learning_project_pupilID) ON DELETE CASCADE,
CONSTRAINT FK_LPPO_O FOREIGN KEY (offerID) REFERENCES t_offers (offerID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS j_learning_projects_pupils;
CREATE TABLE j_learning_projects_pupils (
learning_project_pupilID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_actorID varchar(36) NOT NULL,
active tinyint(3) unsigned DEFAULT '' COMMENT '',
disciplineID varchar(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
deleted tinyint(3) unsigned NOT NULL DEFAULT '',
active_work_reminder tinyint(3) unsigned NOT NULL DEFAULT '',
enable_change_of_reminder_settings tinyint(3) unsigned NOT NULL DEFAULT '',
enable_objective_redefinition tinyint(3) unsigned NOT NULL DEFAULT '',
enable_sequence_removing tinyint(3) unsigned NOT NULL DEFAULT '',
send_detailed_report tinyint(3) unsigned NOT NULL DEFAULT '',
send_learning_path tinyint(3) unsigned NOT NULL DEFAULT '',
send_test_result tinyint(3) unsigned NOT NULL DEFAULT '',
weekly_availability_time bigint(20) unsigned NOT NULL DEFAULT '',
working_reminder_periodicityID tinyint(3) unsigned NOT NULL,
working_reminder_periodicity_value bigint(20) unsigned NOT NULL DEFAULT '',
registration_date datetime NOT NULL,
current_free_mode_learning_levelID bigint(20) unsigned DEFAULT NULL,
enable_change_of_difficulty_settings tinyint(3) unsigned NOT NULL DEFAULT '',
was_activated tinyint(3) unsigned NOT NULL DEFAULT '',
was_connected tinyint(3) unsigned NOT NULL DEFAULT '',
snapshot_running tinyint(3) unsigned NOT NULL DEFAULT '',
visible tinyint(3) unsigned NOT NULL DEFAULT '',
tempID varchar(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
learning_project_pupilID_for_filter_join varchar(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
objectives_expected_time_on_formation smallint(5) unsigned NOT NULL DEFAULT '',
objectives_expected_time_enabled tinyint(3) unsigned NOT NULL DEFAULT '',
cecr_level tinyint(3) unsigned NOT NULL DEFAULT '',
enable_show_popup_phone_lesson_and_live_tutoring TINYINT(3) UNSIGNED NOT NULL DEFAULT '',
PRIMARY KEY (learning_project_pupilID),
KEY user_actorID (user_actorID),
KEY FK_j_learning_projects_pupils_level (current_free_mode_learning_levelID),
KEY IDX_statuses (was_activated,was_connected,deleted,active),
KEY IDX_LPP_TEMPID (tempID),
KEY IDX_LPP_TEMPFILTERID (learning_project_pupilID_for_filter_join),
KEY IDX_DAVA (deleted,active,visible,active_work_reminder),
KEY IDX_discipline_v (disciplineID,visible) USING BTREE,
CONSTRAINT FK_j_learning_projects_pupils_discipline FOREIGN KEY (disciplineID) REFERENCES p_disciplines (disciplineID),
CONSTRAINT FK_j_learning_projects_pupils_j_users_actors FOREIGN KEY (user_actorID) REFERENCES j_users_actors (user_actorID),
CONSTRAINT FK_j_learning_projects_pupils_level FOREIGN KEY (current_free_mode_learning_levelID) REFERENCES p_learning_levels (learning_levelID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS j_learning_projects_pupils_offers_tracking;
CREATE TABLE j_learning_projects_pupils_offers_tracking (
learning_project_pupil_offer_trackingID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
pupil_offer_serviceID varchar(36) NOT NULL,
license_mode_typeID int(10) unsigned NOT NULL,
license_mode_value bigint(20) unsigned NOT NULL DEFAULT '',
effective_consumption_value bigint(20) unsigned NOT NULL DEFAULT '',
PRIMARY KEY (learning_project_pupil_offer_trackingID),
KEY FK_j_learning_projects_pupils_offers_tracking_posID (pupil_offer_serviceID),
CONSTRAINT FK_j_learning_projects_pupils_offers_tracking_posID FOREIGN KEY (pupil_offer_serviceID) REFERENCES t_pupils_offers_services (pupil_offer_serviceID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS t_filters_items;
CREATE TABLE t_filters_items (
filterID varchar(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
itemID varchar(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
row_order bigint(20) unsigned NOT NULL DEFAULT '',
selected tinyint(3) unsigned NOT NULL DEFAULT '',
PRIMARY KEY (filterID,itemID),
CONSTRAINT FK_t_filters_items_ID FOREIGN KEY (filterID) REFERENCES t_filters (filterID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;