sql - MySQL performance of VIEW for tables combined with UNION ALL -

let's 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, need treat them same, that's why following query:

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

starts appear in other queries quite often: part of joins or subselects. now, inject query joins or subselects like:

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

but, today had use discussed union query query multiple times i.e. join twice.

since never had experience views , heard have many disadvantages, question is:

is normal practice create view discussed union query , use in joins , subselects etc? in terms of performance - worse, equal or better comparing inserting joins, subselects etc? there drawbacks of having view in case?

thanks in advance help!

i concur of points in bill karwin's excellent answer.

q: normal practice create view discussed union query , use in joins, subselects etc?

a: mysql more normal practices avoid using "create view" statement.

q: in terms of performance - worse, equal or better comparing inserting joins, subselects etc?

a: referencing view object have identical performance equivalent inline view.

(there might teensy-tiny bit more work lookup view object, checking privileges, , replace view reference stored sql, vs. sending statement teeny-tiny bit longer. of differences insignificant.)

q: there drawbacks of having view in case?

a: biggest drawback in how mysql processes view, whether it's stored or inline. mysql run view query , materialize results query temporary myisam table. there's no difference there whether view definition stored, or whether it's included inline. (other rdbmss process views differently mysql).

one big drawback of view predicates outer query never pushed down view query. every time reference view, query single id value, mysql going run view query , create temporary myisam table (with no indexes on it), , mysql run outer query against temporary myisam table.

so, in terms of performance, think of reference view on par "create temporary table t (cols) engine=myisam" , "insert t (cols) select ...".

mysql refers inline view "derived table", , name makes lot of sense, when understand mysql doing it.

---

my personal preference not use "create view" statement. biggest drawback (as see it) "hides" sql being executed. future reader, reference view looks table. , then, when goes write sql statement, he's going reference view table, convenient. decides he's going join table itself, reference it. (for second reference, mysql runs query again, , creates yet temporary (and unindexed) myisam table. , there's join operation on that. , predicate "where view.column = 'foo'" gets added on outer query.

it ends "hiding" obvious performance improvement, sliding predicate view query.

and then, comes along , decides going create new view, references old view. needs subset of rows, , can't modify existing view because might break something, creates new view... create view myview publicview p p.col = 'foo'.

and, now, reference myview going first run publicview query, create temporary myisam table, myview query gets run against that, creating temporary myisam table, outer query going run against.

basically, convenience of view has potential unintentional performance problems. view definition available on database use, going use it, it's not appropriate solution.

at least inline view, person writing sql statement more aware of actual sql being executed, , having sql laid out gives opportunity tweaking performance.

my 2 cents.

taming beastly sql

i find applying regular formatting rules (that tools automatically do) can bend monstrous sql can read , work with.

select row.col1      , row.col2      , person.*   some_table row   left   join ( select 'person'  `person_type`               , p.id      `id`               , concat(p.first_name,' ',p.surname) `name`            person p           union          select 'company' `person_type`               , c.id      `id`               , c.name    `name`            company c        ) person     on person.id = row.person_id    , person.person_type = row.person_type 

i'd equally avoid inline view @ all, , use conditional expressions in select list, though more unwieldy lots of columns.

select row.col1      , row.col2      , row.person_type ref_person_type      , row.person_id   ref_person_id      , case        when row.person_type = 'person'  p.id         when row.person_type = 'company' c.id        end `person_id`      , case        when row.person_type = 'person'  concat(p.first_name,' ',p.surname)        when row.person_type = 'company' c.name        end `name`   some_table row   left   join person p     on row.person_type = 'person'    , p.id = row.person_id   left   join company c     on row.person_type = 'company'    , c.id = row.person_id