时间戳

首页 > 解决方案 > 如何删除多个连接，执行需要更多时间。如何改善查询结果

php - 如何删除多个连接，执行需要更多时间。如何改善查询结果

问题描述

我在 MySQL 数据库中执行以下查询，查询工作正常，但执行需要更多时间。我正在尝试优化查询，但没有找到任何优化查询的解决方案。请帮我优化查询。

SELECT count( distinct login_history.user_id) as total_count
FROM `login_history
JOIN (SELECT *
FROM `login_details` UNION SELECT *
FROM `deleted_login_details`) as login_details ON `login_details`.`l_id` = `login_history`.`user_id`
LEFT JOIN `group_learners` ON `group_learners`.`user_id`=`login_details`.`l_id` AND `group_learners`.`status` = "0"
LEFT JOIN `learner_custom_field_details` as `l14` ON `l14`.`learner_id` = `login_history`.`user_id`
LEFT JOIN `learner_custom_field_details` as `l16` ON `l16`.`learner_id` = `login_history`.`user_id`
LEFT JOIN `learner_custom_field_details` as `l17` ON `l17`.`learner_id` = `login_history`.`user_id`
LEFT JOIN `learner_custom_field_details` as `l18` ON `l18`.`learner_id` = `login_history`.`user_id`
LEFT JOIN `learner_custom_field_details` as `l19` ON `l19`.`learner_id` = `login_history`.`user_id`
LEFT JOIN `learner_custom_field_details` as `l20` ON `l20`.`learner_id` = `login_history`.`user_id`
LEFT JOIN `learner_custom_field_details` as `l21` ON `l21`.`learner_id` = `login_history`.`user_id`
LEFT JOIN `learner_custom_field_details` ON `learner_custom_field_details`.`learner_id` = `login_history`.`user_id`
WHERE `login_details`.`user_type` NOT IN('1', '2', '4')
AND ( 1=1
AND `l14`.`custom_field_id` = 14
AND `l14`.`custom_value` IN('1', '2')
AND  1=1)
AND ( 1=1
AND `l16`.`custom_field_id` = 16
AND `l16`.`custom_value` IN('3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '283')
AND  1=1)
AND ( 1=1
AND `l17`.`custom_field_id` = 17
AND `l17`.`custom_value` IN('25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51')
AND  1=1)
AND ( 1=1
AND `l18`.`custom_field_id` = 18
AND `l18`.`custom_value` IN('52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78')
AND  1=1)
AND ( 1=1
AND `l19`.`custom_field_id` = 19
AND `l19`.`custom_value` IN('79', '80', '81', '82', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93', '94', '95', '96')
AND  1=1)
AND ( 1=1
AND `l20`.`custom_field_id` = 20
AND `l20`.`custom_value` IN('97', '98', '99', '100', '101', '102', '103', '104', '105', '106', '107', '108', '109', '110', '111', '112', '113', '114', '115', '116', '117', '118', '119', '120', '121', '122', '123', '124', '125', '126', '127', '128', '129', '130', '131', '132', '133', '134', '135', '136', '137', '138', '139', '140', '142', '143', '144', '145', '146', '147', '148', '149', '150', '151', '152', '153', '154', '155', '156', '157', '158', '159', '160', '161', '162', '163', '164', '165', '166', '167', '168', '169', '170', '171', '172', '173', '174', '175', '176', '177', '178', '179', '180', '181', '182', '183', '184', '280')
AND  1=1)
AND ( 1=1
AND `l21`.`custom_field_id` = 21
AND `l21`.`custom_value` IN('185', '186', '187', '188', '189', '190', '191', '192', '193', '194', '195', '196', '197', '198', '199', '200', '201', '202', '203', '204', '205', '206', '207', '208', '209', '210', '211', '212', '213', '214', '215', '216', '217', '218', '219', '220', '221', '222', '223', '224', '225', '226', '227', '228', '229', '230', '231', '232', '233', '234', '235', '236', '237', '238', '239', '240', '241', '242', '243', '244', '245', '246', '247', '248', '249', '250', '251', '252', '253', '254', '255', '256', '257', '258', '259', '260', '261', '262', '263', '264', '265', '266', '267', '268', '269', '270', '272', '273', '274', '281', '282')
AND  1=1)
AND `login_details`.`l_branch` IN('1')

标签： phpmysqlsqlcodeigniter-3

解决方案

我很想尝试以下

SELECT
    COUNT( user_id ) AS total_count
FROM (
    SELECT
        login_history.user_id
    FROM login_history
    INNER JOIN group_learners ON group_learners.user_id = login_history.user_id
        AND group_learners.status = "0"
    UNION
    SELECT
        login_history.user_id
    FROM login_history
    INNER JOIN learner_custom_field_details AS l14 ON login_history.user_id = login_history.user_id
        AND l14.custom_field_id = 14
        AND l14.custom_value IN ('1', '2')
    UNION
    SELECT
        login_history.user_id
    FROM login_history
    INNER JOIN learner_custom_field_details AS l16 ON l16.learner_id = login_history.user_id
        AND l16.custom_field_id = 16
        AND l16.custom_value IN ('3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '283')
    UNION
    SELECT
        login_history.user_id
    FROM login_history
    INNER JOIN learner_custom_field_details AS l17 ON l17.learner_id = login_history.user_id
        AND l17.custom_field_id = 17
        AND l17.custom_value IN ('25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51')
    UNION
    SELECT
        login_history.user_id
    FROM login_history
    INNER JOIN learner_custom_field_details AS l18 ON l18.learner_id = login_history.user_id
        AND l18.custom_field_id = 18
        AND l18.custom_value IN ('52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78')
    UNION
    SELECT
        login_history.user_id
    FROM login_history
    INNER JOIN learner_custom_field_details AS l19 ON l19.learner_id = login_history.user_id
        AND l19.custom_field_id = 19
        AND l19.custom_value IN ('79', '80', '81', '82', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93', '94', '95', '96')
    UNION
    SELECT
        login_history.user_id
    FROM login_history
    INNER JOIN learner_custom_field_details AS l20 ON l20.learner_id = login_history.user_id
        AND l20.custom_field_id = 20
        AND l20.custom_value IN ('97', '98', '99', '100', '101', '102', '103', '104', '105', '106', '107', '108', '109', '110', '111', '112', '113', '114', '115', '116', '117', '118', '119', '120', '121', '122', '123', '124', '125', '126', '127', '128', '129', '130', '131', '132', '133', '134', '135', '136', '137', '138', '139', '140', '142', '143', '144', '145', '146', '147', '148', '149', '150', '151', '152', '153', '154', '155', '156', '157', '158', '159', '160', '161', '162', '163', '164', '165', '166', '167', '168', '169', '170', '171', '172', '173', '174', '175', '176', '177', '178', '179', '180', '181', '182', '183', '184', '280')
    UNION
    SELECT
        login_history.user_id
    FROM login_history
    INNER JOIN learner_custom_field_details AS l21 ON l21.learner_id = login_history.user_id
        AND l21.custom_field_id = 21
        AND l21.custom_value IN ('185', '186', '187', '188', '189', '190', '191', '192', '193', '194', '195', '196', '197', '198', '199', '200', '201', '202', '203', '204', '205', '206', '207', '208', '209', '210', '211', '212', '213', '214', '215', '216', '217', '218', '219', '220', '221', '222', '223', '224', '225', '226', '227', '228', '229', '230', '231', '232', '233', '234', '235', '236', '237', '238', '239', '240', '241', '242', '243', '244', '245', '246', '247', '248', '249', '250', '251', '252', '253', '254', '255', '256', '257', '258', '259', '260', '261', '262', '263', '264', '265', '266', '267', '268', '269', '270', '272', '273', '274', '281', '282')
    UNION
    SELECT
        login_history.user_id
    FROM login_history
    INNER JOIN learner_custom_field_details ON learner_custom_field_details.learner_id = login_history.user_id
) u
WHERE (
EXISTS (
    SELECT
        NULL
    FROM login_details
    WHERE user_type NOT IN ('1', '2', '4')
    AND l_branch IN ('1')
    AND user_id = u.user_id
    )
OR EXISTS (
    SELECT
        NULL
    FROM deleted_login_details
    WHERE user_type NOT IN ('1', '2', '4')
    AND l_branch IN ('1')
    AND user_id = u.user_id
    )
)

推荐阅读