![]() , CONCAT(p.first_name,' ',p.surname) AS `name` I find that applying regular formatting rules (that my tools automatically do) can bend monstrous SQL into something I can read and work with. With the view definition available on the database for anyone to use, someone is going to use it, even where it's not the most appropriate solution.Īt least with an inline view, the person writing the SQL statement is more aware of the actual SQL being executed, and having all that SQL laid out gives an opportunity for tweaking it for performance. CREATE VIEW myview FROM publicview p WHERE p.col = 'foo'.Īnd, now, a reference to myview is going to first run the publicview query, create a temporary MyISAM table, then the myview query gets run against that, creating another temporary MyISAM table, which the outer query is going to run against.īasically, the convenience of the view has the potential for unintentional performance problems. He only needs a subset of rows, and can't modify the existing view because that might break something, so he creates a new view. It ends up "hiding" the most obvious performance improvement, sliding that predicate into the view query.Īnd then, someone comes along and decides they are going to create new view, which references the old view. And then a predicate "WHERE lumn = 'foo'" gets added on the outer query. And now there's a JOIN operation on that. (For the second reference, MySQL also runs that query again, and creates yet another temporary (and unindexed) MyISAM table. Then he decides he's going to join that table to itself, with another reference to it. And then, when he goes to write a SQL statement, he's going to reference the view like it was a table, so very convenient. For the future reader, the reference to the view looks like a table. The biggest drawback (as I see it) is that it "hides" SQL that is being executed. My personal preference is to not use the "CREATE VIEW" statement. MySQL actually refers to an inline view as a "derived table", and that name makes a lot of sense, when we understand what MySQL is doing with it. So, in terms of performance, think of a reference to a view on par with " CREATE TEMPORARY TABLE t (cols) ENGINE=MyISAM" and " INSERT INTO t (cols) SELECT. Every time you reference that view, even with a query for a single id value, MySQL is going to run the view query and create a temporary MyISAM table (with no indexes on it), and THEN MySQL will run the outer query against that temporary MyISAM table. One big drawback of a view is that predicates from the outer query NEVER get pushed down into the view query. (Other RDBMSs process views much differently than MySQL). But there's no difference there whether the view definition is stored, or whether it's included inline. MySQL will always run the view query and materialize the results from that query as a temporary MyISAM table. Q: Are there any drawbacks of having a view in this case?Ī: The biggest drawback is in how MySQL processes a view, whether it's stored or inline. But any of those differences are insignificant.) sending a statement that is just a teeny-tiny bit longer. (There might be a teensy-tiny bit more work to lookup the view object, checking privileges, and then replace the view reference with the stored SQL, vs. Q: In terms of performance - will it be worse, equal or better comparing to just inserting it into joins, subselects etc?Ī: Referencing a view object will have the identical performance to an equivalent inline view. Q: Is it normal practice to create a view for discussed union query and use it in my joins, subselects etc?Ī: With MySQL the more normal practices is to avoid using "CREATE VIEW" statement. I concur with all of the points in Bill Karwin's excellent answer. Is it normal practice to create a view for discussed union query and use it in my joins, subselects etc? In terms of performance - will it be worse, equal or better comparing to just inserting it into joins, subselects etc? Are there any drawbacks of having a view in this case? Since I never had experience with views and heard that they have many disadvantages, my question is: On row.person_id = persons.id and row.person_type = persons.person_typeīut, today I had to use discussed union query into another query multiple times i.e. ![]() ![]() Left outer join (> query from above goes here <<<) as `persons` ![]() Starts to appear in other queries quite often: as part of joins or subselects.įor now, I simply inject this query into joins or subselects like: select * Select company.id as `id`, company.name as `name`, 'company' as `person_type` Now, very often I need to treat them the same, that's why following query: select person.id as `id`, concat(person.first_name, ' ', person.surname) as `name`, 'person' as `person_type` `id` bigint unsigned not null auto_increment, Let's say I have 2 tables in MySQL: create table `persons` (
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |