CODEWITHSUNDEEP
mysqloptimizationsubqueryunion
kon apaz
kon apaz

Reputation: 78

A Complex query into Union mysql multiple times - Optimizer

I want to use a same subquery multiple times into UNION. This subquery is time consumed and I think that using it a lot of times may will be increased the total time of execution.

For example

(SELECT * FROM (SELECT * FROM A INNER JOIN B ... AND SOME COMPLEX WHERE CONDITIONS) as T ORDER BY column1 DESC LIMIT 10)
UNION
(SELECT * FROM (SELECT * FROM A INNER JOIN B ... AND SOME COMPLEX WHERE CONDITIONS) as T ORDER BY column2 DESC LIMIT 10)
UNION
(SELECT * FROM (SELECT * FROM A INNER JOIN B ... AND SOME COMPLEX WHERE CONDITIONS) as T ORDER BY column3 DESC LIMIT 10)

Does the (SELECT * FROM A INNER JOIN B ... AND SOME COMPLEX WHERE CONDITIONS) executed 3 times ?

If mysql is smart enough the internal subquery will be executed only one so I don't need any optimization, but if not I have to use something else to optimize it (like using a temporary table, but I want to avoid it)

Do I have to optimize this query by other syntax ? Any suggestion ?

In practice I want to filter some data from huge records and get some of them in 3 group-sections, each section in different order

Upvotes: 0

Views: 127

Answers (1)

Rick James
Rick James

Reputation: 142453

Plan A:

A TEMPORARY TABLE cannot be referenced more than once. So, build a permanent table and DROP it when finished. (If you might have multiple connections doing the same thing, it will be a hassle to make sure you are not using the same table name.)

Plan B:

With MySQL 8.0, you can do

WITH T AS ( SELECT ... )
SELECT ... FROM T ORDER BY col1
UNION ...

Plan C:

If it is possible to do this:

SELECT id FROM A
     ORDER BY col1 LIMIT 10

You could use that as a 'derived' table inside 

(SELECT * FROM A INNER JOIN B ... AND SOME COMPLEX WHERE CONDITIONS)

Something like

SELECT A.*, B.*
    FROM ( SELECT id FROM A
               ORDER BY col1 LIMIT 10 ) AS x1
    JOIN A  USING(id)
    JOIN B ... AND SOME COMPLEX WHERE CONDITIONS

Similarly for the other two SELECTs, then UNION them together.

Better yet, UNION together the 3 sets of ids, then JOIN to A and B once.

This may have the advantage of dealing with fewer rows.

Upvotes: 1

Related Questions