MySQL doesn't cache views, so it's not saving you any performance, and depending on the nature of the view, may be costing you more than doing the raw queries.
Personally, the penalty, when there is one, is so vanishingly small that I prefer to use views for certain kinds of complex joins that I will always be reading from constantly in an application. There really is no alternative that can save you performance cycles other than denormalizing the information into reporting tables during updates and reading from the reporting tables. Nowadays, developer time is more valuable than shaving 0.5 -2 milliseconds of a second from a select. If you're at the point where that 0.5-2 milliseconds actually matters, you're probably better off coming up with some good caching schemes. Hopefully MySQL will support view caching soon making this all moot. I name my views v_tablename when I use them as an extension of some standard table. For example, my users table joins with the groups table, the people table, the company table, and a whole bunch of other things. I created a v_user table and for all my user management indexes in Cake, I use the VUser model instead of the User model. My Users controller simply uses both and that's it. Sure, for the cases where all I need is user information, I'll be worse off using the view than the table. But for a lot of index "views" and reports in the application, I need to display all the relevant fields. So there's no performance hit in those cases and the development and maintenance is much improved. On Oct 7, 8:13 pm, Jeremy Burns | Class Outfit <[email protected]> wrote: > My quick and dirty response is that if this view is essentially only used for > view operations (in other words you're not updating the data) and you don't > need to do any joins at the model level, then if it works your approach is > the right one. The database is designed to do these complex joins so I'd say > you were leveraging its native power. > > Jeremy Burns > Class Outfit > > [email protected]http://www.classoutfit.com > > On 8 Oct 2010, at 03:36, Michael T wrote: > > > > > Hi all, > > > In attempt to solve a particular problem, I've tried a solution which > > to me, a CakePHP novice, seems a bit un-Cake like. I wanted to ask > > your opinion here on whether a) this matters; and b) if it does > > matter, what's the Cake-friendly way to do it? > > > Here's a summary of my models and their relationships: > > > I have a model for Jobs. I have a model for Employees. Over time, one > > Job has different Employees occupying it. This information is stored > > in a joining table JobOccupations (with a foreign key to the Job, the > > Employee, plus additional details like start date, end date, etc). > > > Job hasMany JobOccupation > > JobOccupation belongsTo Job > > Employee hasMany JobOccupation > > JobOccupation belongsTo Employee > > > To query the database for the "latest" employee (i.e. the last > > employee to occupy each job) I need to do something like this: > > > SELECT JO1.id, JO1.job_id, JO1.employee_id > > FROM job_occupations JO1 > > LEFT JOIN job_occupations JO2 > > ON JO1.job_id = EO2.job_id > > AND JO1.id < JO2.id > > WHERE JO2.id IS NULL > > > This will select the records of the job_occupations table with the > > maximal primary key id for any given job id. (Because records are > > inserted sequentially, this guarantees that the maximal id belongs to > > the most recent record.) > > > So in database world, to execute this query easily and use its > > information with other tables in the database, I created a stored view > > called "job_latest_occupations". Then I can join this view with other > > views or tables to get all the employee or job details for only the > > most recent occupations. > > > Now, I want to report on this information in my Cake application. So I > > created a Model "LatestJobOccupation" based off the database view > > (actually, not the view I showed above, but a more complex one which > > incorporates that view and joins onto another 6 or so tables). This > > model has no relationships to other models. > > > To report on the data, I just run find queries on this model. Because > > the view just pulls in every field from all the joined tables, I get > > convenient access to all the fields in the one result record (and not > > split into different associative arrays based on the table name -- > > although that still happens when I specify conditions on the query > > strangely). I get away with not having to specify hairy join > > conditions. > > > So I guess my question is -- is this wrong? Should I be trying to do > > it the more Cake way (I'm not sure if such a thing exists)? Or should > > I be utilising database features wherever possible? What would you do? > > > Cheers > > > Check out the new CakePHP Questions sitehttp://cakeqs.organd help others > > with their CakePHP related questions. > > > You received this message because you are subscribed to the Google Groups > > "CakePHP" group. > > To post to this group, send email to [email protected] > > To unsubscribe from this group, send email to > > [email protected] For more options, visit this group > > athttp://groups.google.com/group/cake-php?hl=en Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
