| MERGE STORAGE ENGINE Vs UNION ALL [message #3076] |
Tue, 13 May 2008 07:47 |
 dbnewbie
Messages: 1
Registered: May 2008 |
Junior Member |
|
|
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 !
|
|
|
Members
Search
Help
Register
Login
Home
