Need help for optimization

Question

I have a MySQL query that I'd like help optimizing

(
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
)

So, I have two tables ARTICLE and RUBRIC, a first select return one ARTICLE for each RUBRIC where value of RUBRIC.VIEW is '1'. Second select return one result and i definite manually the last two columns by "BOOK".

please provide the explain for the query also There are better ways to get random records than order by rand() http://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast — Sam, Sep 05 '14 at 15:00
Is the query running slow? Or are you just looking for a way to better organize it? — Travis, Sep 05 '14 at 15:00

score 0 · Accepted Answer · answered Sep 05 '14 at 15:18

You can simplify the query by using variables, doing all the work in one pass. The idea is to keep two variables with the count for each of the different values:

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;

Need help for optimization

1 Answers1