CODEWITHSUNDEEP
mysql
RGS
RGS

Reputation: 4253

using union query takes 10 times longer to execute

I have a query to select the next question and if no next exists it will return to the first id find:

select id, question from (
(SELECT id, question FROM questions where id > 3 and cat_id = 1 order by id limit 1)
UNION
(SELECT id, question FROM questions where cat_id = 1 order by id limit 1)
) as result12 limit 1

This takes 0,0354

I can also run one per time and check the number of return rows programatelly:

SELECT id, question FROM questions where id > 3 and cat_id = 1 order by id limit 1

and

if($num_rows == 0) {
SELECT id, question FROM questions where cat_id = 1 order by id limit 1
}

the first select takes 0,0014 and the second the same thing 0,0014.

Should I use the first or second approach? why?

Upvotes: 0

Views: 184

Answers (1)

sticky bit
sticky bit

Reputation: 37472

Hmm, there's a couple of possibilities to rewrite the query that might perform better. The major problem might be the sorting that has to be done, twice. You can limit it to only one sort operation if you also sort by q1.id > 3 DESC. q1.id > 3 will be 1 if true or 0 otherwise. So records that meet the condition are on top.

SELECT q1.id,
       q1.question
       FROM questions q1
       WHERE q1.cat_id = 1
       ORDER BY q1.id > 3 DESC,
                q1.id ASC
       LIMIT 1;

An index on (cat_id) might also help, shouldn't there be one already.

Another approach might be to check if records with id > 3 do not exist. Then we're good. Only otherwise we need the id > 3 check.

SELECT q1.id,
       q1.question
       FROM questions q1
       WHERE q1.cat_id = 1
             AND (NOT EXISTS (SELECT *
                                     FROM questions q2
                                     WHERE q2.cat_id = 1
                                           AND q2.id > 3)
                   OR q1.id > 3)
       ORDER BY q1.id ASC
       LIMIT 1;

Here an index on (cat_id, id) might help. Note that the order of the columns is reversed from the index you already have.

Upvotes: 3

Related Questions