MERGE STORAGE ENGINE Vs UNION ALL

MySQL & MariaDB Other MySQL® Questions
1

Hi,

Can anyone tell me if and how, a MERGE STORAGE ENGINE results in a better performance or a faster query than using a UNION ALL?

Let me explain my requirement in detail. I have the following tables:

TABLE PDATA( ID INT NOT NULL PRIMARY KEY,
NAME VARCHAR(100) NOT NULL,
VARIABLE VARCHAR(100) NOT NULL,
AGENT VARCHAR(100) NOT NULL)

TABLE STATS_TODAY( ID INT NOT NULL,
INSTANCE VARCHAR(100) NOT NULL,
VAL INT NOT NULL)

TABLE STATS_YESTERDAY( ID INT NOT NULL,
INSTANCE VARCHAR(100) NOT NULL,
VAL INT NOT NULL)

Now my query is currently like this :

SELECT NAME,VARIABLE,AGENT,INSTANCE,SUM(VAL) FROM
(
SELECT NAME,VARIABLE,AGENT,INSTANCE,VAL FROM STATS_TODAY,PDATA where STATS_TODAY.ID=PDATA.ID and (NAME = ‘a’ or NAME=‘b’) and (AGENT=‘c’ or AGENT='d) and (INSTANCE=‘1/1’)

UNION ALL

SELECT NAME,VARIABLE,AGENT,INSTANCE,VAL FROM STATS_YESTERDAY,PDATA where STATS_YESTERDAY.ID=PDATA.ID and (NAME = ‘a’ or NAME=‘b’) and (AGENT=‘c’ or AGENT='d) and (INSTANCE=‘1/1’)

) group by ID,INSTANCE order by NAME,AGENT limit 1,10.

PS: STATS_YESTERDAY,STATS_TODAY are just 2 of the many tables which could be used in UNION ALL query.

Given the above scenario, i want to know if a MERGE would be a better option than UNION ALL.

Thanks !