mysql - 在 UNION 表上进行更快的 WHERE 查询？

Question

我正在查询谁的数据用于在我们的应用程序中构建树导航器（实际上我正在我们的代码库中重新制作一个旧的慢速复杂查询）。层次结构有 5 个步骤，客户 -> 位置 -> 区域 -> 资产 -> 区域，每个表都有一个“活动”列，用于过滤目的。

所以，这是我的问题 - 使用过滤器的活动列的更好途径是什么？

现在我让桌子像这样回来

path         text        type        active
''           customer1   customer    1
customer1    location1   location    1
customer1    location2   location    0

这样，如果用户不想看到非活动位置，我会使用WHERE type='location' AND active!=0. 但是，我在想也许最好为每个级别创建一个活动列，并为不是那种特定类型的行放入 1，就像这样

path         text        customerActive    locationActive   areaActive
''           customer1        1                  1               1
customer1    location1        1                  1               1
customer1    location2        1                  0               1

因此，如果我只想要活动位置，我会这样做WHERE locationActive = 1。

这些技术中的一种比另一种更快还是更好？对这种情况是否有任何共识？

这是生成第一类表的当前 SQL 代码

(SELECT 
    '' AS `path`,
        `listcustomers`.`name` AS `text`,
        'customer' AS `type`,
        `listcustomers`.`idx` AS `id`,
        `listcustomers`.`active` AS `active`,
        IF(`listcustomers`.`active`, 'tree/CustomerActive.png', 
'tree/CustomerInactive.png') AS `icon`
FROM
    `listcustomers`) UNION (SELECT 
    `listcustomers`.`name` AS `path`,
        `listlocations`.`name` AS `text`,
        'location' AS `type`,
        `listcustomers`.`idx` AS `id`,
        `listcustomers`.`active` AS `active`,
        IF(`listlocations`.`active`, 'tree/LocationActive.png', 
'tree/LocationInactive.png') AS `icon`
FROM
    `listlocations`
JOIN `listcustomers` ON `listcustomers`.`idx` = 
`listlocations`.`parentCustomerId`) UNION (SELECT 
    CONCAT_WS('/', `listcustomers`.`name`, `listlocations`.`name`),
        `listareas`.`name` AS `text`,
        'areas' AS `type`,
        `listareas`.`idx` AS `id`,
        `listareas`.`active` AS `active`,
        IF(`listareas`.`active`, 'tree/AreaActive.png', 
'tree/AreaInactive.png') AS `icon`
FROM
    `listareas`
JOIN `listlocations` ON `listlocations`.`idx` = 
`listareas`.`parentLocationId`
JOIN `listcustomers` ON `listcustomers`.`idx` = 
`listlocations`.`parentCustomerId`) UNION (SELECT 
    CONCAT_WS('/', `listcustomers`.`name`, `listlocations`.`name`, 
`listareas`.`name`) AS `path`,
        `listassets`.`name` AS `text`,
        'assets' AS `type`,
        `listassets`.`idx` AS `id`,
        `listassets`.`active` AS `active`,
        IF(`listassets`.`active`, 'tree/assetActive.png', 
'tree/assetInactive.png') AS `icon`
FROM
    `listassets`
JOIN `listareas` ON `listareas`.`idx` = `listassets`.`parentAreaId`
JOIN `listlocations` ON `listlocations`.`idx` = 
`listareas`.`parentLocationId`
JOIN `listcustomers` ON `listcustomers`.`idx` = 
`listlocations`.`parentCustomerId`) UNION (SELECT 
    CONCAT_WS('/', `listcustomers`.`name`, `listlocations`.`name`, 
`listareas`.`name`, `listassets`.`name`) AS `path`,
        `listprojects`.`name` AS `text`,
        'projects' AS `type`,
        `listprojects`.`idx` AS `id`,
        `listprojects`.`active` AS `active`,
        NULL AS `icon`
FROM
    `listprojects`
JOIN `listassets` ON `listassets`.`idx` = `listprojects`.`parentAssetId`
JOIN `listareas` ON `listareas`.`idx` = `listassets`.`parentAreaId`
JOIN `listlocations` ON `listlocations`.`idx` = 
`listareas`.`parentLocationId`
JOIN `listcustomers` ON `listcustomers`.`idx` = 
`listlocations`.`parentCustomerId`)

标签： mysqlsql