Login | Register
My pages Projects Community openCollabNet

Discussions > dev > STRAIGHT_JOIN select modifier

Discussion topic

Back to topic list

STRAIGHT_JOIN select modifier


Author Max Manders <mmanders at valleyt dot co dot uk>
Full name Max Manders <mmanders at valleyt dot co dot uk>
Date 2008-03-18 09:00:04 PDT
Message Hi,

I have a large query that equi-joins on multiple tables (i.e. WHERE t1.a
= t2.1 AND...). MySQL isn't optimizing this query as well as it could,
so I thought I'd use the 'STRAIGHT_JOIN' select modifier to force joins
to be evaluated in the order I specify. However, the Criteria object
doesn't seem to provide this facility. I am a Symfony user by the way
and use Propel via Symfony.

There is a setDistinct method and DISTINCT constant in the Criteria
class: similar code would facilitate what I am trying to achieve. I
would like to avoid editing the source code however. I have tried
extending Criteria, but the member array selectModifiers is private and
has no public accessor methods. As such, I would not be able to access
this variable in a subclass of Criteria.

As an alternative to this, I have tried overriding the public
getSelectModifiers method by having it concatenate the required
STRAIGHT_JOIN constant to the array returned by
parent::getSelectModifiers. This does work, but becuase
getSelectModifiers is always called in BasePeer::createSelectSql, this
constant will be added to every query that uses my subclass of Criteria
(CustomCriteria) class, and there is no deleteSelectModifier method.
This behaviour effects the count query done by sfPropelPager->init().
For reasons that I have not been able to figure out, the tables in the
FROM clause are ordered differently when STRAIGHT_JOIN is present in
SELECT COUNT(table.column. This new ordering actually makes the count
incredible inefficient, and I'd rather let MySQL optimize the query.

My options are to only apply STRAIGHT_JOIN to a particular query, which
I can't do because there is no accessor method in Criteria to modify
selectModifiers[]; or to manually change the order of the tables in the
 From clause in the sfPropelPager->init count query. I can't do the
latter because I can't understand why the table order is changing in the
first place - a read through the code with a pen-and-paper suggests that
the order shouldn't be effected, but clearly it is.

Can someone suggest how I can fix this so that I can optionally use
STRAIGHT_JOIN as a select modifier in a Criteria (or subclass of Criteria).

Many Thanks,

Max Manders
Systems Developer

Valley Technology Ltd.
44 St. Marys Street

0131 553 0402
07866 797 983

« Previous message in topic | 1 of 1 | Next message in topic »


Show all messages in topic

STRAIGHT_JOIN select modifier Max Manders <mmanders at valleyt dot co dot uk> Max Manders <mmanders at valleyt dot co dot uk> 2008-03-18 09:00:04 PDT
Messages per page: