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
