On Mon, 3 Sep 2007, Shawn Green wrote: > What I hope to do is to help you to make you queries work better by > applying hints, modifiers, and limits to the optimizer.
Many thanks, Shawn. > "STRAIGHT_JOIN is identical to JOIN, except ... > > You asked what would happen if you replaced all of your view's LEFT JOIN > clauses with STRAIGHT_JOIN clauses. [...] This is not what you are > trying to achieve. I realized that. I overlooked "is identical to JOIN" since I generally use only LEFT JOINs to benefit of null returns (to us it is equally important to know "source A has these 1/2/n counterparts" and "source A has no counterparts" in the other catalogue/s). I originally tested that STRAIGHT_JOIN made the "explain select" arrive to an end without looping forever, but as soon as I tried a real select I realized the different behaviour. > However, there is another place you can put the STRAIGHT_JOIN modifier: > in the SELECT clause I discovered that too, and it looks promising. I plan to test whether that makes any difference on the query result and execution time in some representative cases. > Why are some sets of tables optimized more quickly than the others? My > best guess would be that some mix of WHERE conditions and table indexes > make it much faster to eliminate combinatorial permutations of JOIN > sequences than others. I also realized that the WHERE part enters the optimization too ... this is of course unpredictable a priori, as it depends on the particular user needs. > This is the variable optimizer_search_depth: This also looks interesting thanks. I did some quick experimenting. For some reasons a SHOW VARIABLE tells me its normal value is 62. Apparently this does not depend on the database I select (I thought my real "data" database with the views and G's could have an higher value than e.g. my own "administrative" DB or the mysql DB, but it is always 62. I tried lowering it to 10/15/20 and even to 0 (which according to 5.2.3 in the manual should be a sort of "automatic" value). In all cases the explain select on our maximal statement (the one which loops forever in statistics status under default conditions) reaches an end rather fast, and returns the same result (irrespective of 0/10/15/20). The ORDER returned by playign with optimizer_search_depth is however different from the one returned with SELECT STRAIGHT_JOIN. The difference is just in the order (the type, key and "extra" looks the same, in general "ref" or "eq_ref" and "using index") so I suppose it should not make much difference. > You can set this variable on a per-session basis so it would be possible > to tune it for each call to your views. While I'm doing the tests under the mysql linemode client, our production environment is a Java jsp interface under tomcat. I have to check with my colleague dealing with java programming what is a "session" for him (probably a JDBC connection in connection pool). Actually what looks appealing in optimizer_search_depth vs SELECT STRAIGHT_JOIN is the possibility of fixing (no tuning per session) optimizer_search_depth to a decent default (0 ?) once forever, because this requires no changes to the code (however if SELECT STRAIGHT_JOIN will require changes concentrated in a few places). I'll do some experimenting and report back. -- ----------------------------------------------------------------------- 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]
