GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MERGE STORAGE ENGINE Vs UNION ALL

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MERGE STORAGE ENGINE Vs UNION ALL

    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 !
Working...
X