kuba kuba - 2 months ago 8
MySQL Question

Replacing table with view in MySQL

I have to handle database that is read (and only read) by third party software I cannot verify. It has a table which stores partial copy of other table.

Would it be safe to replace this table with view?

Answer

Besides the view restrictions, when using views in MySQL you have to be aware of one very important issue: The performance of WHERE statements suffers greatly:

For example:

SELECT column_a FROM table_n WHERE column_a="some value";

is quick assuming an index is in place on column_a.

Now create a view:

CREATE VIEW column_a_view AS SELECT column_a FROM table_n;

SELECT * FROM column_a_view b WHERE b.column_a="some value";

can lead to a full table scan since MySQL (at least until 5.6) did not always recognize the fact that it can use the index.

So especially if you are working with large tables, it can be more beneficial to create "copies" of the related data and replace that data once per time interval then working with views.