Re: MySQL View
On Mon, Feb 9, 2009 at 3:41 PM, Steven Buehler wrote: Ok, I just saw a post about using view's in mysql. I tried to look it up and found how to use it, but my question is: what is a view and why would you use it? The problem with any definition of an object in a database is that there are multiple definitions. Usually on the one hand you have the definition from abstract relational theory, and on the other hand you have the definition from actual working databases. So I am not going to bother with a definition, I will try to explain how a view works internally inside database code. The easiest way to understand a view is to consider a view as a macro that gets expanded during the execution of every query that references that view in its FROM. Lets take for example the view that your DBA has defined for you using: CREATE VIEW x AS SELECT * FROM y INNER JOIN z ON y.id = z.id; Then you query that view with the query: SELECT a FROM x; What the database will do for you behind the scenes is expand your usage of the view. In effect, the database will replace x with its definition. So your query SELECT a FROM x; gets expanded to: SELECT a FROM (SELECT * FROM y INNER JOIN z ON y.id = z.id); Notice that I have done nothing but replace x with its definition between parenthesis. And this results in a valid query that can be executed. And that is exactly what the database will do. It will do this substitution and then it will run the result of that substitution as if it were the query that you submitted. Obviously a bit more will go on behind the scenes to handle things like permissions and optimizations (especially if you get to databases that have more functionality then MySQL), but this is really all there is to it. A view is a simple macro that assigns an alias to a select statement, and when you reference that alias the select statement will get substituted back in. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MySQL View
-Original Message- From: Jochem van Dieten [mailto:joch...@gmail.com] Sent: Tuesday, February 10, 2009 5:10 AM To: mysql@lists.mysql.com Subject: Re: MySQL View On Mon, Feb 9, 2009 at 3:41 PM, Steven Buehler wrote: Ok, I just saw a post about using view's in mysql. I tried to look it up and found how to use it, but my question is: what is a view and why would you use it? The problem with any definition of an object in a database is that there are multiple definitions. Usually on the one hand you have the definition from abstract relational theory, and on the other hand you have the definition from actual working databases. So I am not going to bother with a definition, I will try to explain how a view works internally inside database code. The easiest way to understand a view is to consider a view as a macro that gets expanded during the execution of every query that references that view in its FROM. Lets take for example the view that your DBA has defined for you using: CREATE VIEW x AS SELECT * FROM y INNER JOIN z ON y.id = z.id; Then you query that view with the query: SELECT a FROM x; What the database will do for you behind the scenes is expand your usage of the view. In effect, the database will replace x with its definition. So your query SELECT a FROM x; gets expanded to: SELECT a FROM (SELECT * FROM y INNER JOIN z ON y.id = z.id); Notice that I have done nothing but replace x with its definition between parenthesis. And this results in a valid query that can be executed. And that is exactly what the database will do. It will do this substitution and then it will run the result of that substitution as if it were the query that you submitted. Obviously a bit more will go on behind the scenes to handle things like permissions and optimizations (especially if you get to databases that have more functionality then MySQL), but this is really all there is to it. A view is a simple macro that assigns an alias to a select statement, and when you reference that alias the select statement will get substituted back in. Jochem, Not sure about the other poster, but this helps explain it to me. If I understand you correctly, if I have multiple tables with many columns in them, but have several queries that need to pull only a few columns from each and put them together, it is probably best to create a view to do this so that I don't have to keep running joins in my queries? Even if I reboot the computer, the view will still be there when it comes back up too? Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL View
On Tue, Feb 10, 2009 at 1:47 PM, Steven Buehler wrote: From: Jochem van Dieten: What the database will do for you behind the scenes is expand your usage of the view. In effect, the database will replace x with its definition. So your query SELECT a FROM x; gets expanded to: SELECT a FROM (SELECT * FROM y INNER JOIN z ON y.id = z.id); Not sure about the other poster, but this helps explain it to me. If I understand you correctly, if I have multiple tables with many columns in them, but have several queries that need to pull only a few columns from each and put them together, it is probably best to create a view to do this so that I don't have to keep running joins in my queries? No. I am explicitly not saying how you should use views. I am just telling you how they work. But to give you some examples of how you could use views (I am still not saying how you should use views): 1. Use views to replace repetitive elements in queries. If you have lots of queries that perform the same join or filter, put it in a view. That has no semantic value, but you save yourself some typing. 2. Use views to manage permissions. If people have only access to a subset of the data, revoke their permissions on the table and define a view that has exactly the data that they have access to. Then give them permissions on the view. 3. Use views to define new schema elements that have meaning. If you have a normalized schema an invoice may be spread over a dozen tables (customer, invoice, invoiceline, item, price, shipping, payment, account etc.). You can define a view with all the proper joins and filters that groups that together so you get all the data at once. (Some people may argue that this is the same as no. 1, but I think it is an important distinction that the view represents an actual object: an invoice as you print and send them.) 4. your great view usage here Even if I reboot the computer, the view will still be there when it comes back up too? Yes, views are persitent. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL View
Ok, I just saw a post about using view's in mysql. I tried to look it up and found how to use it, but my question is: what is a view and why would you use it? Is it like a temporary table? Does it write a new database to the disk or use memory? Thanks Steve
Re: MySQL View
On Mon, Feb 9, 2009 at 9:41 AM, Steven Buehler st...@ibushost.com wrote: Ok, I just saw a post about using view's in mysql. I tried to look it up and found how to use it, but my question is: what is a view and why would you use it? Is it like a temporary table? Does it write a new database to the disk or use memory? A view is a piece of SQL whose result can be queried like a table. It stores no data; the results are always generated as the query executes. In some cases it does use a temporary table to hold the result and then query against it; in other cases it merges the original query's SQL and the view's SQL together and then executes the resulting query. Why use it? To abstract a complex bit of code away for simplicity. To grant permissions in a certain way (you can grant access to the view and deny access to the underlying table). There's a lot of complexity to it though, in terms of how to use views correctly and get good performance. I think the manual goes over it in good detail, and our book High Performance MySQL 2nd Edition has probably the best exploration of it otherwise. Baron -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MySQL View
-Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: Monday, February 09, 2009 9:19 AM To: Steven Buehler Cc: mysql@lists.mysql.com Subject: Re: MySQL View On Mon, Feb 9, 2009 at 9:41 AM, Steven Buehler st...@ibushost.com wrote: Ok, I just saw a post about using view's in mysql. I tried to look it up and found how to use it, but my question is: what is a view and why would you use it? Is it like a temporary table? Does it write a new database to the disk or use memory? A view is a piece of SQL whose result can be queried like a table. It stores no data; the results are always generated as the query executes. In some cases it does use a temporary table to hold the result and then query against it; in other cases it merges the original query's SQL and the view's SQL together and then executes the resulting query. Why use it? To abstract a complex bit of code away for simplicity. To grant permissions in a certain way (you can grant access to the view and deny access to the underlying table). There's a lot of complexity to it though, in terms of how to use views correctly and get good performance. I think the manual goes over it in good detail, and our book High Performance MySQL 2nd Edition has probably the best exploration of it otherwise. Baron Baron, Thank You Questions: 1. The view is temporary then? So it kind of uses it in place of a temporary table? 2. Does it go away after the query or after the mysql_close? I am going to have to go to the book store and get your book too. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MySQL View
My current understanding of the delta between Views and Temporary Tables Views are read only results from 1 or more tables ..in Oracle they are stored in TEMP tablespace http://www.psoug.org/reference/views.html Temporary Tables are tables which are created/updated/inserted and exist only for the duration of your client session Oracle calls these Global Temporary Tables http://www.psoug.org/reference/gtt.html HTH Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: st...@ibushost.com To: ba...@xaprb.com CC: mysql@lists.mysql.com Subject: RE: MySQL View Date: Mon, 9 Feb 2009 10:10:45 -0600 -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: Monday, February 09, 2009 9:19 AM To: Steven Buehler Cc: mysql@lists.mysql.com Subject: Re: MySQL View On Mon, Feb 9, 2009 at 9:41 AM, Steven Buehler st...@ibushost.com wrote: Ok, I just saw a post about using view's in mysql. I tried to look it up and found how to use it, but my question is: what is a view and why would you use it? Is it like a temporary table? Does it write a new database to the disk or use memory? A view is a piece of SQL whose result can be queried like a table. It stores no data; the results are always generated as the query executes. In some cases it does use a temporary table to hold the result and then query against it; in other cases it merges the original query's SQL and the view's SQL together and then executes the resulting query. Why use it? To abstract a complex bit of code away for simplicity. To grant permissions in a certain way (you can grant access to the view and deny access to the underlying table). There's a lot of complexity to it though, in terms of how to use views correctly and get good performance. I think the manual goes over it in good detail, and our book High Performance MySQL 2nd Edition has probably the best exploration of it otherwise. Baron Baron, Thank You Questions: 1. The view is temporary then? So it kind of uses it in place of a temporary table? 2. Does it go away after the query or after the mysql_close? I am going to have to go to the book store and get your book too. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Windows Live™: Keep your life in sync. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks_022009
RE: MySQL View
I just found something else out and did a test. So a view is a table in a database that can be shared. The example I found was if you have multiple people that need a database for something, but it still has to keep the information separate, instead of having multiple databases, you can create one with a VIEW statement. With the privileges setup correctly, each user can only access the information in that database that they put in and not other people's data. And it does actually create this on disk not in memory so it doesn't get deleted between sessions, reboots, etc. Does that sound correct? Thanks Steve From: Martin Gainty [mailto:mgai...@hotmail.com] Sent: Monday, February 09, 2009 11:26 AM To: Steven Buehler; ba...@xaprb.com Cc: mysql@lists.mysql.com Subject: RE: MySQL View My current understanding of the delta between Views and Temporary Tables Views are read only results from 1 or more tables ..in Oracle they are stored in TEMP tablespace http://www.psoug.org/reference/views.html Temporary Tables are tables which are created/updated/inserted and exist only for the duration of your client session Oracle calls these Global Temporary Tables http://www.psoug.org/reference/gtt.html HTH Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: st...@ibushost.com To: ba...@xaprb.com CC: mysql@lists.mysql.com Subject: RE: MySQL View Date: Mon, 9 Feb 2009 10:10:45 -0600 -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: Monday, February 09, 2009 9:19 AM To: Steven Buehler Cc: mysql@lists.mysql.com Subject: Re: MySQL View On Mon, Feb 9, 2009 at 9:41 AM, Steven Buehler st...@ibushost.com wrote: Ok, I just saw a post about using view's in mysql. I tried to look it up and found how to use it, but my question is: what is a view and why would you use it? Is it like a temporary table? Does it write a new database to the disk or use memory? A view is a piece of SQL whose result can be queried like a table. It stores no data; the results are always generated as the query executes. In some cases it does use a temporary table to hold the result and then query against it; in other cases it merges the original query's SQL and the view's SQL together and then executes the resulting query. Why use it? To abstract a complex bit of code away for simplicity. To grant permissions in a certain way (you can grant access to the view and deny access to the underlying table). There's a lot of complexity to it though, in terms of how to use views correctly and get good performance. I think the manual goes over it in good detail, and our book High Performance MySQL 2nd Edition has probably the best exploration of it otherwise. Baron Baron, Thank You Questions: 1. The view is temporary then? So it kind of uses it in place of a temporary table? 2. Does it go away after the query or after the mysql_close? I am going to have to go to the book store and get your book too. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Windows LiveT: Keep your life in sync. See how it works. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks_022 009
Re: MySQL View
Steven, I would suggest you the reading of a basic book about SQL and Databases, I explain you why. Views are a very fundamental concept in database theory that you would love to study in deep. This mailing list, though the most professional on MySQL, is not suitable to explain such basic and important concepts. Views and temporary tables are like completely different concepts, temporary tables are not even a concept, but a 'tool' for SQL developer, while views are (views!) 'snapshots' of data structured in a way that phisically does not exist, are reorganization of the database relations(read tables) to pull out only relevant information for a part of an application and by which you can create infinite views (sorry) of the database. If you like Excel, very roughly speaking, views are excel functions, while the table data is the actual excel column containing data. Steven, this is my humble opinion. Please let me know what you think Claudio 2009/2/9 Steven Buehler st...@ibushost.com I just found something else out and did a test. So a view is a table in a database that can be shared. The example I found was if you have multiple people that need a database for something, but it still has to keep the information separate, instead of having multiple databases, you can create one with a VIEW statement. With the privileges setup correctly, each user can only access the information in that database that they put in and not other people's data. And it does actually create this on disk not in memory so it doesn't get deleted between sessions, reboots, etc. Does that sound correct? Thanks Steve From: Martin Gainty [mailto:mgai...@hotmail.com] Sent: Monday, February 09, 2009 11:26 AM To: Steven Buehler; ba...@xaprb.com Cc: mysql@lists.mysql.com Subject: RE: MySQL View My current understanding of the delta between Views and Temporary Tables Views are read only results from 1 or more tables ..in Oracle they are stored in TEMP tablespace http://www.psoug.org/reference/views.html Temporary Tables are tables which are created/updated/inserted and exist only for the duration of your client session Oracle calls these Global Temporary Tables http://www.psoug.org/reference/gtt.html HTH Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: st...@ibushost.com To: ba...@xaprb.com CC: mysql@lists.mysql.com Subject: RE: MySQL View Date: Mon, 9 Feb 2009 10:10:45 -0600 -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: Monday, February 09, 2009 9:19 AM To: Steven Buehler Cc: mysql@lists.mysql.com Subject: Re: MySQL View On Mon, Feb 9, 2009 at 9:41 AM, Steven Buehler st...@ibushost.com wrote: Ok, I just saw a post about using view's in mysql. I tried to look it up and found how to use it, but my question is: what is a view and why would you use it? Is it like a temporary table? Does it write a new database to the disk or use memory? A view is a piece of SQL whose result can be queried like a table. It stores no data; the results are always generated as the query executes. In some cases it does use a temporary table to hold the result and then query against it; in other cases it merges the original query's SQL and the view's SQL together and then executes the resulting query. Why use it? To abstract a complex bit of code away for simplicity. To grant permissions in a certain way (you can grant access to the view and deny access to the underlying table). There's a lot of complexity to it though, in terms of how to use views correctly and get good performance. I think the manual goes over it in good detail, and our book High Performance MySQL 2nd Edition has probably the best exploration of it otherwise. Baron Baron, Thank You Questions: 1. The view is temporary then? So it kind of uses it in place of a temporary table? 2. Does it go away after the query or after the mysql_close? I am going to have to go to the book store and get your book too. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Windows LiveT: Keep your life in sync. See how it works. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks_022 009
RE: MySQL View
Claudio, Thank you. I do agree with you. I need to read up on it. I only work with MySQL databases and queries and have never heard of it until today. Maybe after I learn more about them, I will find them useful. Guess they must be useful or it wouldn't be part of SQL. Thank You Steve From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Monday, February 09, 2009 3:59 PM To: Steven Buehler Cc: mysql@lists.mysql.com Subject: Re: MySQL View Steven, I would suggest you the reading of a basic book about SQL and Databases, I explain you why. Views are a very fundamental concept in database theory that you would love to study in deep. This mailing list, though the most professional on MySQL, is not suitable to explain such basic and important concepts. Views and temporary tables are like completely different concepts, temporary tables are not even a concept, but a 'tool' for SQL developer, while views are (views!) 'snapshots' of data structured in a way that phisically does not exist, are reorganization of the database relations(read tables) to pull out only relevant information for a part of an application and by which you can create infinite views (sorry) of the database. If you like Excel, very roughly speaking, views are excel functions, while the table data is the actual excel column containing data. Steven, this is my humble opinion. Please let me know what you think Claudio 2009/2/9 Steven Buehler st...@ibushost.com I just found something else out and did a test. So a view is a table in a database that can be shared. The example I found was if you have multiple people that need a database for something, but it still has to keep the information separate, instead of having multiple databases, you can create one with a VIEW statement. With the privileges setup correctly, each user can only access the information in that database that they put in and not other people's data. And it does actually create this on disk not in memory so it doesn't get deleted between sessions, reboots, etc. Does that sound correct? Thanks Steve From: Martin Gainty [mailto:mgai...@hotmail.com] Sent: Monday, February 09, 2009 11:26 AM To: Steven Buehler; ba...@xaprb.com Cc: mysql@lists.mysql.com Subject: RE: MySQL View My current understanding of the delta between Views and Temporary Tables Views are read only results from 1 or more tables ..in Oracle they are stored in TEMP tablespace http://www.psoug.org/reference/views.html Temporary Tables are tables which are created/updated/inserted and exist only for the duration of your client session Oracle calls these Global Temporary Tables http://www.psoug.org/reference/gtt.html HTH Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: st...@ibushost.com To: ba...@xaprb.com CC: mysql@lists.mysql.com Subject: RE: MySQL View Date: Mon, 9 Feb 2009 10:10:45 -0600 -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: Monday, February 09, 2009 9:19 AM To: Steven Buehler Cc: mysql@lists.mysql.com Subject: Re: MySQL View On Mon, Feb 9, 2009 at 9:41 AM, Steven Buehler st...@ibushost.com wrote: Ok, I just saw a post about using view's in mysql. I tried to look it up and found how to use it, but my question is: what is a view and why would you use it? Is it like a temporary table? Does it write a new database to the disk or use memory? A view is a piece of SQL whose result can be queried like a table. It stores no data; the results are always generated as the query executes. In some cases it does use a temporary table to hold the result and then query against it; in other cases it merges the original query's SQL and the view's SQL together and then executes the resulting query. Why use it? To abstract a complex bit of code away for simplicity. To grant permissions in a certain way (you can grant access to the view and deny access to the underlying table). There's a lot of complexity to it though, in terms of how to use views correctly and get good performance. I think the manual goes over it in good detail, and our book High Performance MySQL 2nd Edition has probably the best exploration of it otherwise. Baron Baron, Thank You Questions: 1. The view is temporary then? So it kind of uses it in place of a temporary table? 2. Does it go away after the query or after the mysql_close? I am going to have to go to the book store and get your book too. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql
Re: MySQL View Optimization Help
Hi! Daniel Kasak wrote: [EMAIL PROTECTED] wrote: Views differ from tables in that they cannot be indexed. I've just started experimenting with derived tables under 4.1.14, and I had a hunch this was so. Is there any plan to include index support for views / derived tables? An index is a separate data structure which must be maintained when the base table is changed (in the indexed columns). A view is a restricted (by rows and/or columns) look at a base table. IMO, having separate indexes for views is not in line with the relational approach at all. The way to go is an efficient use of all indexes defined on a table, whether it is accessed as a base table or via a view. Any where condition in the view definition may be evaluated via base table indexes, if suitable ones are defined on the base table - provided the optimizer chooses this access path. Improving the optimizer would be feature changes, so you should expect to see that in newer versions only. Regards, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL View Optimization Help
[EMAIL PROTECTED] wrote: Views differ from tables in that they cannot be indexed. I've just started experimenting with derived tables under 4.1.14, and I had a hunch this was so. Is there any plan to include index support for views / derived tables? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL View Optimization Help
Thanks a lot Shawn. I didn't realize that views don't take advantage of indexing. This is the cause of my major performance hits. I'm basically using views as a form of DB abstraction over the tables. So, many of my views pull all records from all tables they join, and it is up to the user to submit a where query to the view. In many cases, I'm getting 20-30 second queries, whereas the underlying (indexed) tables return results in .33 seconds. The views themselves aren't using criteria. This runs contrary to what I imagine to be a common use of views, ie, vwSelectAllArizonaResidents sort of thing, where the view internally compiles the where criteria from the underlying table. Scott Klarenbach On 1/6/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Klarenbach [EMAIL PROTECTED] wrote on 01/06/2006 08:13:10 PM: Hello, I'm new to views and am discovering massive performance hits in the views I've created once the records start to climb above 20,000 or so. Does anyone know of a great primer/tutorial site for optimizing views in MySQL, or even generally? What are the best practices etc...? I find when I create the same view in SQL by joining the tables directly, it's much faster than the views which invariably are joining other views. Is there a recursion problem with this method? Should views only join underlying tables and not other views? Thanks. Scott. Treat views as you would any other query. All of the optimizations that normally apply to SELECT query performance should also apply to view performance. Views differ from tables in that they cannot be indexed. That is probably why you are getting performance hits by building views on views. Any query against a view (such as a second-tier derivative view) will end up performing the equivalent of a full table scan on any view it uses. There is no hard and fast rule about building views based on other views or based on tables. What works best for you should be which solution you stick with. If you have millions of rows in a base table and a view can reduce that to about ten thousand rows of summary information, I would be very tempted to stick with the view as the basis of a future query. You still have to generate that view each time you want to use it but its data may be sitting there in the query cache so it has the potential to be very fast. If I were you I would review the entire optimization chapter: http://dev.mysql.com/doc/refman/5.0/en/optimization.html It's loaded with useful information. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
MySQL View Optimization Help
Hello, I'm new to views and am discovering massive performance hits in the views I've created once the records start to climb above 20,000 or so. Does anyone know of a great primer/tutorial site for optimizing views in MySQL, or even generally? What are the best practices etc...? I find when I create the same view in SQL by joining the tables directly, it's much faster than the views which invariably are joining other views. Is there a recursion problem with this method? Should views only join underlying tables and not other views? Thanks. Scott.
Re: MySQL View Optimization Help
Scott Klarenbach [EMAIL PROTECTED] wrote on 01/06/2006 08:13:10 PM: Hello, I'm new to views and am discovering massive performance hits in the views I've created once the records start to climb above 20,000 or so. Does anyone know of a great primer/tutorial site for optimizing views in MySQL, or even generally? What are the best practices etc...? I find when I create the same view in SQL by joining the tables directly, it's much faster than the views which invariably are joining other views. Is there a recursion problem with this method? Should views only join underlying tables and not other views? Thanks. Scott. Treat views as you would any other query. All of the optimizations that normally apply to SELECT query performance should also apply to view performance. Views differ from tables in that they cannot be indexed. That is probably why you are getting performance hits by building views on views. Any query against a view (such as a second-tier derivative view) will end up performing the equivalent of a full table scan on any view it uses. There is no hard and fast rule about building views based on other views or based on tables. What works best for you should be which solution you stick with. If you have millions of rows in a base table and a view can reduce that to about ten thousand rows of summary information, I would be very tempted to stick with the view as the basis of a future query. You still have to generate that view each time you want to use it but its data may be sitting there in the query cache so it has the potential to be very fast. If I were you I would review the entire optimization chapter: http://dev.mysql.com/doc/refman/5.0/en/optimization.html It's loaded with useful information. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
mysql view implementation
Hi there, Does anyone know how views are implemented in MySQL? Is the view query executed each time the view is interacted with (ie. query modification) or is there some sort of caching involved (i.e. view materialization). thanks _ All the news that matters. Just the way you like it. http://www.msn.co.in/News/ Only at MSN News! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
