Predefined view vs query at runtime - mysql

Question

i need to know which one will be more efficient way of doing.

method 1: create predefined view in mysql as follows.

create view TestView As
select * from Table1
union all
select * from Table2

after creating this view, im quering as

select * from TestView where col_value = 5

method 2: im running following query at runtime

select * from Table1 where col_value = 5
union all
select * from Table2 where col_value = 5

in first method the view can have more than 100,000 records. from which it has to find records "col_value = 5".

Both will give me same result but i wanted to know which one is more good in performance wise.

Answer 1

In your "Method 1", your query

SELECT * FROM TestView WHERE col_value = 5

is actually equivalent to:

SELECT * FROM (
    SELECT * FROM Table1
    UNION ALL
    SELECT * FROM Table2
) AS v
WHERE col_value = 5

This basically means: "select everything from each table, merge the results and then filter out". All records from both tables will be scanned and copied to a temporary table, possibly on disk. Besides, this query is unable to use an index on col_value if such an index exists.

Method 2 is preferable.

Note this is not always true. In some (many) cases, MySQL is able to do a smart merge of the view definition with the query using the view (this is called the MERGE algorithm). In such cases, the performance would be almost exactly the same. In your example, the presence of an UNIONclause prevents this optimisation.

P.S: check out EXPLAIN