On Tue, 4 Sep 2007, Shawn Green wrote:
> > On Tue, 4 Sep 2007, Lucio Chiappetti wrote:
> > For each I tested 3 cases (total 16*3=48) :
> >
> > a) the query on the "virtual" table correlated with the external
> > (the virtual is my G left join t1 ... left join tn). [...]
> > represents my REFERENCE,
this case will also exploit full (or default) optimization
> > b) the query on the VIEW V with "members also" (which implies a
> > redundant join of V with G left join t1 ... left join tn) and
> > correlated with an external table. [...] I used here SELECT
> > STRAIGHT_JOIN.
> > c) the same query of (b) but with a normal select, and preceded by
> > setting optimizer_search_depth=0 (auto).
> In your b) test, did you use the SELECT STRAIGHT_JOIN as your outer
> SELECT statement or within the CREATE VIEW statement?
No. I left the CREATE VIEW alone (standard LEFT JOINs) in all cases,
since that will be fully optimized, and used by a majority of users.
SELECT STRAIGHT_JOIN was used only in (b) ["no optimization"] and not in
(c) ["residual optimization"].
Our inclination would now be to use SELECT STRAIGHT_JOIN because it's the
one involving only "elegant" changes to the code. Consider again that the
case "view + member also + other table" will be rare (very few users)
compare to "view alone".
Anyhow all this was rather instructive.
Now I'll start experimenting with UNIONs too ...
--
-----------------------------------------------------------------------
Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html
-----------------------------------------------------------------------
() ascii ribbon campaign - against html mail
/\ http://arc.pasp.de/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
