如何删除多个连接，执行起来要花更多的时间如何改进查询结果

w8f9ii69  于 2021-06-19  发布在  Mysql
关注(0)|答案(2)|浏览(259)

我在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')
sqlmysqlphpcodeigniter-3

2条答案

按热度按时间
nhaq1z21

nhaq1z211#

这是一种方法，但我相信你可以从最后一个连接部分，其中有不同的l18，l19，l20。。。然后让你的加入变得简单。。。

with cteUnion as (
SELECT *
 FROM `login_details` 
  WHERE `login_details`.`user_type` NOT IN('1', '2', '4') AND `login_details`.`l_branch` IN('1')
  UNION 
SELECT *
FROM 
`deleted_login_details`
),
group_learnersStatus as (
select * 
from `group_learners`
where `status` = "0"

),

SELECT count( distinct login_history.user_id), login_history.user_id as total_count
FROM `login_history
JOIN cteUnion as login_details ON `login_details`.`l_id` = `login_history`.`user_id`
LEFT JOIN group_learnersStatus ON `group_learners`.`user_id`=`login_details`.`l_id` 
LEFT JOIN `learner_custom_field_details` as `l14` ON `l14`.`learner_id` = `login_history`.`user_id` AND `l14`.`custom_field_id` = 14
AND `l14`.`custom_value` IN('1', '2')
LEFT 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')
LEFT 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')
LEFT 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')
LEFT 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')
LEFT JOIN `learner_custom_field_details` as `l20` ON `l20`.`learner_id` = `login_history`.`user_id` 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')
LEFT 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')
LEFT JOIN `learner_custom_field_details` ON `learner_custom_field_details`.`learner_id` = `login_history`.`user_id`
赞(0）分享  回复(0）举报  2021-06-19
4nkexdtk

4nkexdtk2#

我很想试试下面的方法

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
    )
)
赞(0）分享  回复(0）举报  2021-06-19
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com