Re: [fw-general] MySQL Views
Bill Thanks for the input, I have been reading more and more about MySQL views over the last few days and came up with more or less the same interpretation as you did below, all be it in a less eloquent manner. One thing that views does allow me to do is to use the zend_db_table interface, which if nothing else gives me and my team a consistent modelling scheme to work towards. Thanks again Ian Bill Karwin wrote: There are different implementations of views in different RDBMS brands. Sometimes they store results and sometimes they work by rewriting the query for you and executing it against the base table(s). MySQL has only the latter type. The performance of views is therefore comparable to the performance of a prepared query. There is no extra disk space used by views, unless you mean the storage needed for the metadata definition of the view itself. I don't know of any reason why caching would be better with views vs. tables. In MySQL 5.0, the Query Cache does cache results of queries against views, but this is virtually identical to the way the Query Cache works against base tables. Views are certainly better than using derived tables in subqueries. Derived tables in MySQL _do_ create a temporary materialized copy of data, which is potentially expensive. Joining against a derived table is also likely to expensive, because the temp table has no indexes. Anyway, if the performance of your app is so critical that a tiny difference in performance between querying a view vs. querying a base table is significant, you probably shouldn't be touching the database at all. Instead, cache query results at the application level, using Zend_Cache with a back-end using a persistent in-memory cache such as Memcached or Zend Platform. That way high-demand data is available at the speed of a memory access. Regards, Bill Karwin -Original Message- From: Ian Warner [mailto:[EMAIL PROTECTED] Sent: Friday, August 03, 2007 7:12 PM To: [EMAIL PROTECTED] Cc: Zend Framework Subject: Re: [fw-general] MySQL Views Ok kinda figured this out, as Views act as independant tables then simply creating another model class for them works: Ie for Countries table I created countryselect view, to get all the country rows i needed and ordered correctly. $table = new models_fblovefootball_Countryselect(); $result = $table->fetchAll(); var_dump($result); CAn anyone gives me any heads up on performance of using views, and creating multiple views? I believe it should be very fast as it caches the queries better, but may mean extra disk space thats all, will do some more reading but any pointers appreciated Ian Ian Warner wrote: Hi Does or will Zend_Db_Table have any methods to handle table related Views, or should I just implement these through DB_Select? Also wanted to know best practices on this has anyone coded in any methods to simplify abstraction? Cheers Ian
RE: [fw-general] MySQL Views
There are different implementations of views in different RDBMS brands. Sometimes they store results and sometimes they work by rewriting the query for you and executing it against the base table(s). MySQL has only the latter type. The performance of views is therefore comparable to the performance of a prepared query. There is no extra disk space used by views, unless you mean the storage needed for the metadata definition of the view itself. I don't know of any reason why caching would be better with views vs. tables. In MySQL 5.0, the Query Cache does cache results of queries against views, but this is virtually identical to the way the Query Cache works against base tables. Views are certainly better than using derived tables in subqueries. Derived tables in MySQL _do_ create a temporary materialized copy of data, which is potentially expensive. Joining against a derived table is also likely to expensive, because the temp table has no indexes. Anyway, if the performance of your app is so critical that a tiny difference in performance between querying a view vs. querying a base table is significant, you probably shouldn't be touching the database at all. Instead, cache query results at the application level, using Zend_Cache with a back-end using a persistent in-memory cache such as Memcached or Zend Platform. That way high-demand data is available at the speed of a memory access. Regards, Bill Karwin > -Original Message- > From: Ian Warner [mailto:[EMAIL PROTECTED] > Sent: Friday, August 03, 2007 7:12 PM > To: [EMAIL PROTECTED] > Cc: Zend Framework > Subject: Re: [fw-general] MySQL Views > > Ok kinda figured this out, as Views act as independant tables > then simply creating another model class for them works: > > Ie for Countries table I created countryselect view, to get > all the country rows i needed and ordered correctly. > > $table = new models_fblovefootball_Countryselect(); > $result = $table->fetchAll(); > var_dump($result); > > CAn anyone gives me any heads up on performance of using > views, and creating multiple views? > > I believe it should be very fast as it caches the queries > better, but may mean extra disk space thats all, will do some > more reading but any pointers appreciated > > Ian > > Ian Warner wrote: > > Hi > > > > Does or will Zend_Db_Table have any methods to handle table related > > Views, or should I just implement these through DB_Select? > > > > Also wanted to know best practices on this has anyone coded in any > > methods to simplify abstraction? > > > > Cheers > > > > Ian > > >
Re: [fw-general] MySQL Views
Ok kinda figured this out, as Views act as independant tables then simply creating another model class for them works: Ie for Countries table I created countryselect view, to get all the country rows i needed and ordered correctly. $table = new models_fblovefootball_Countryselect(); $result = $table->fetchAll(); var_dump($result); CAn anyone gives me any heads up on performance of using views, and creating multiple views? I believe it should be very fast as it caches the queries better, but may mean extra disk space thats all, will do some more reading but any pointers appreciated Ian Ian Warner wrote: Hi Does or will Zend_Db_Table have any methods to handle table related Views, or should I just implement these through DB_Select? Also wanted to know best practices on this has anyone coded in any methods to simplify abstraction? Cheers Ian
[fw-general] MySQL Views
Hi Does or will Zend_Db_Table have any methods to handle table related Views, or should I just implement these through DB_Select? Also wanted to know best practices on this has anyone coded in any methods to simplify abstraction? Cheers Ian
