CODEWITHSUNDEEP
mysqlsql
Jacek Francuz
Jacek Francuz

Reputation: 2498

Create View and performance

I have the following question. I have two tables and I want to store them separately. However, in my application I need to regularly perform UNION operation on them, so I effectively need to treat them as one. I think this is not good in terms of performance. I was thinking about creating the following view:

CREATE VIEW merged_tables(fields from both tables) AS
  SELECT * FROM table_a
UNION
  SELECT * FROM table_b

How does it impact on the performance of SELECT query? Does it have any real impact (inner representation is different) or it is just a matter of using a simpler query to select?

Upvotes: 1

Views: 210

Answers (2)

Larry Lustig
Larry Lustig

Reputation: 50970

It is just a matter of using a simpler query to select. There will be no speed difference, however the cost of querying the union should not be much worse (in most cases) than if you kept all the data in a single table.

If the tables are really the same structure, you could also consider another design in which you used a single table for storage and two views to logically separate the records:

 CREATE VIEW table_a AS SELECT * FROM table_all WHERE rec_type = 'A'
 CREATE VIEW table_b AS SELECT * FROM table_all WHERE rec_type = 'B'

Because these are single-table, non-aggregated VIEWs you can use them like tables in INSERT, UPDATE, DELETE, and SELECT but you have the advantage of also being able to program against table_all when it makes sense. The advantage here over your solution is that you can update against either the table_a, table_b entities or the table_all entity, and you don't have two physical tables to maintain.

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270599

Using a UNION inside a view will be no different in performance than using the UNION in a discrete SELECT statement. Since you are using SELECT * on both tables and require no inner complexity (e.g. no JOINS or WHERE clauses), there won't really be any way to further optimize it.

However, if the tables do hold similar data and you want to keep them logically separated, you might consider storing it all in one table with a boolean column that indicates whether a row would otherwise have been a resident of table_a or table_b. You gain a way to tell the rows apart and avoid the added confusion of the UNION then, and performance isn't significantly impacted either.

Upvotes: 3

Related Questions