MySQL VIEW performance for tables combined with UNION ALL

Question

Let's say I have 2 tables in MySQL :

create table `persons` ( `id` bigint unsigned not null auto_increment, `first_name` varchar(64), `surname` varchar(64), primary key(`id`) ); create table `companies` ( `id` bigint unsigned not null auto_increment, `name` varchar(128), primary key(`id`) ); 

Now very often I need to treat them the same way, so the following request:

 select person.id as `id`, concat(person.first_name, ' ', person.surname) as `name`, 'person' as `person_type` from persons union all select company.id as `id`, company.name as `name`, 'company' as `person_type` from companies 

begins to appear in other queries quite often: as part joins or subqueries . For now, I am just inserting this query into connections or subqueries , for example:

 select * from some_table row left outer join (>>> query from above goes here <<<) as `persons` on row.person_id = persons.id and row.person_type = persons.person_type 

But today I had to use the discussed join request in another request several times, that is, attach it twice.

Since I never had any experience with opinions and I heard that they have many shortcomings, my question is:

Is it common practice to create a presentation for the discussed join request and use it in my connections , and sub-selections , etc.? In terms of performance - would it be worse, equal, or better than just pasting it into connections , subqueries , etc.? Are there any drawbacks to having a representation in this case?

Thanks in advance for your help!

Answer 1

I agree with all points in Bill Carvin's wonderful answer.

Q: Is it common practice to create a view for the union query under discussion and use it in my joins, subqueries, etc.?

A: In MySQL, a more common practice is to avoid using the CREATE VIEW statement.

Q: In terms of performance - will it be worse, equal, or better than just pasting it into connections, subqueries, etc.?

A: A reference to a view object will have identical performance for an equivalent inline view.

(It may take a little more work to find the view object, check privileges, and then replace the view reference with stored SQL, and also sending a statement that will be a little longer than a tiny bit. Any of these differences does not matter.)

Q: Are there any flaws in the presentation in this case?

A: The biggest flaw is how MySQL handles the view, regardless of whether it is stored or inline. MySQL will always run a view query and materialize the results of that query as a temporary MyISAM table. But it makes no difference whether the definition of the view is preserved or whether it is included in the line. (Other RDBMSs handle views much differently than MySQL).

One big drawback of the view is that the predicates from the external NEVER query will go into the view query. Each time you refer to this view, even with a request for a single id value, MySQL will run a view query and create a temporary MyISAM table (without indexes on it), and THEN MySQL will run an external query against this temporary MyISAM table.

So, in terms of performance, think of a parameter view link with " CREATE TEMPORARY TABLE t (cols) ENGINE=MyISAM " and " INSERT INTO t (cols) SELECT ... ".

MySQL actually refers to the inline view as a "view", and that name makes a lot of sense when we understand what MySQL is doing with it.

---

My personal preference is not to use the "CREATE VIEW" statement. The biggest drawback (as I see) is that it “hides” the SQL that is being executed. For the future reader, the link to the view looks like a table. And then, when he goes to write an SQL statement, he is going to refer to the view as a table, so it’s very convenient. Then he decides that he is going to join this table with himself, with another reference to it. (For the second link, MySQL starts this query again and creates another temporary (and non-indexed) MyISAM table. And now it has a JOIN operation. Then the predicate "WHERE view.column = 'foo" is added to the external query.

He finishes “hiding” the most obvious performance improvement by shifting this predicate to a view query.

And then someone comes and decides that they are going to create a new view that refers to the old view. It only needs a subset of the rows and cannot change the existing view because it can break something, so it creates a new view ... CREATE VIEW myview FROM publicview p WHERE p.col = 'foo'.

And now the link to myview is going to start the publicview query first, create a temporary MyISAM table, then the myview query is launched against this, creating another temporary MyISAM table that the external query will work against.

In principle, viewing convenience may have unforeseen performance issues. With a view definition available in the database for someone, someone will use it, even if this is not the most suitable solution.

At least in the built-in view, the person writing the SQL statement is more aware of the actual execution of SQL, and having all this SQL code makes it possible to tune it for performance.

My two cents.

TAMING BEASTLY SQL

I find that applying regular formatting rules (which my tools automatically change) can bend monstrous SQL into something that I can read and work.

 SELECT row.col1 , row.col2 , person.* FROM some_table row LEFT JOIN ( SELECT 'person' AS `person_type` , p.id AS `id` , CONCAT(p.first_name,' ',p.surname) AS `name` FROM person p UNION ALL SELECT 'company' AS `person_type` , c.id AS `id` , c.name AS `name` FROM company c ) person ON person.id = row.person_id AND person.person_type = row.person_type 

I would equally avoid the inline view altogether and use conditional expressions in the SELECT list, although this becomes more cumbersome for a large number of columns.

 SELECT row.col1 , row.col2 , row.person_type AS ref_person_type , row.person_id AS ref_person_id , CASE WHEN row.person_type = 'person' THEN p.id WHEN row.person_type = 'company' THEN c.id END AS `person_id` , CASE WHEN row.person_type = 'person' THEN CONCAT(p.first_name,' ',p.surname) WHEN row.person_type = 'company' THEN c.name END AS `name` FROM some_table row LEFT JOIN person p ON row.person_type = 'person' AND p.id = row.person_id LEFT JOIN company c ON row.person_type = 'company' AND c.id = row.person_id 

Answer 2

The view makes your SQL shorter. It's all.

This is a common misconception for MySQL users who are browsing something. They are not (at least not in MySQL). They are more like an alias or macro. A view request is most often like executing a request in an extended form. A request twice in a single request (as in the example connection example) has no advantage in presentation - it will run the request twice.

In fact, browsing can lead to poor performance, depending on the query and how you use them, as they may need to store the result in a temporary table each time you query them.

See http://dev.mysql.com/doc/refman/5.6/en/view-algorithms.html for details, when a view uses a tempting algorithm.

On the other hand, UNION queries also create temporary tables as their results accumulate. So you are still stuck with the cost of the temporary table.