CODEWITHSUNDEEP
mysqlsqlquery-optimizationdatabase-performance
bbpi
bbpi

Reputation: 19

How expensive is creating a database view?

Right now, I'm altering a table with a crap ton of data, which is going to take weeks to run. In the meantime, I figured I'd create a new table to write to while this one is locked.

I can create an empty table and just write to this one, and check both tables when reading. Or I can create a copy of the current table and do all reads/writes from this copy for the next month.

It looks like copying the table is not the best solution. What about creating a view (just to read from) combining both tables?

CREATE VIEW MY_TABLE_VIEW AS
   SELECT * FROM MY_TABLE
   UNION ALL
   SELECT * FROM MY_TABLE_COPY

Would creating the view be just as expensive as altering the original table? Should I instead just change all my table reads to UNION ALL the results from both tables?

Upvotes: 1

Views: 1098

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

Creating a view is a metadata-only operation. Views don't store any data. Querying the view will actually query the base table as if you ran the query in the view definition. So there's practically nothing to do to create the view. It only stores the query as metadata.

Upvotes: 3

Related Questions