需要帮助进行优化

debugcn 发表于 Dev

用户名

我有一个MySQL查询，需要帮助进行优化

(
SELECT
    a.`CODE`, 
    a.`TITLE`,  
    a.`TEXT`, 
    a.`CODE_RUBRIC`,  
    r.`NAME` 
FROM 
    `ARTICLE` a,
    `RUBRIC` r
WHERE
    a.`CODE_TYPE` = '2'
    AND a.`CODE_RUBRIC` =  r.`CODE`
    AND r.`VIEW` = '1'
    AND DATE_ADD(NOW(), INTERVAL -31 DAY) <  `DATE` 
    GROUP BY r.`CODE` 
    ORDER BY RAND()
) UNION (
SELECT
    a.`CODE`, 
    a.`TITLE`,  
    a.`TEXT`, 
    "BOOK",  
    "BOOK" 
FROM
    `RUBRIC` r,
    `ARTICLE` a
WHERE
    a.`CODE_TYPE` =  '2'
    AND a.`CODE_RUBRIC` = r.`CODE`
    AND `JOURNAL` = '1'
    AND DATE_ADD(NOW(), INTERVAL -31 DAY) < a.`DATE`
    GROUP BY r.`CODE`
    ORDER BY RAND() 
    LIMIT 1
)

因此，我有两个表ARTICLE和RUBRIC，第一个选择为每个RUBRIC返回一个ARTICLE，其中RUBRIC.VIEW的值为'1'。第二选择返回一个结果，我通过“ BOOK”手动确定最后两列。

戈登·利诺夫（Gordon Linoff）

您可以使用变量简化查询，一次完成所有工作。想法是保留两个变量，每个不同值的计数：

SELECT ar.*
FROM (SELECT ar.*,
             if(`view` = 1, @rnv := @rnv + 1, NULL) as rnv,
             if(`journal` = 1, @rnj := @rnj + 1, NULL) as rnj
      FROM (SELECT a.`CODE`, a.`TITLE`, a.`TEXT`, a.`CODE_RUBRIC`, r.`NAME` 
            FROM `ARTICLE` a JOIN
                 `RUBRIC` r
                 ON a.`CODE_RUBRIC` =  r.`CODE`
            WHERE a.`CODE_TYPE` = '2' AND
                  (r.`VIEW` = '1' OR r.JOURNAL = 1) AND
                  DATE_ADD(NOW(), INTERVAL -31 DAY) <  `DATE` 
            GROUP BY r.`CODE` 
           ) ar CROSS JOIN
           (select @rnv := 0, @rnj := 0) vars
      ORDER BY RAND()
     ) ar
WHERE rnv = 1 or rnj = 1;