Triple join asking for trouble?


#1

Model.scoped_by_body_type_id
( event.body_type_ids ).scoped_by_skin_color_id
( event.skin_color_ids ).find( :all,
:conditions => [‘weight_pounds <= ? AND total_height_inches >= ?
AND minimum_pay <= ? AND job_types.id = ? AND regions.id = ? AND
languages.id in (?)’,
event.maximum_weight_in_pounds,
event.total_minimum_height_inches,
event.payment_per_head,
event.job_type_id,
event.region_id,
event.language_ids ],
:joins => [:job_types, :regions, :languages])

Is this triple join asking for trouble? I mean is my server going to
hate me? language, region and job_types are going to have less than
10 row entries, but job_type_preferences, language_preferences and
region_preferences will scale linearly with the number of users…

Model Load (0.7ms) SELECT models.* FROM models INNER JOIN
job_type_preferences ON (models.id =
job_type_preferences.model_id) INNER JOIN job_types ON
(job_types.id = job_type_preferences.job_type_id) INNER JOIN
region_preferences ON (models.id =
region_preferences.model_id) INNER JOIN regions ON
(regions.id = region_preferences.region_id) INNER JOIN
language_preferences ON (models.id =
language_preferences.model_id) INNER JOIN languages ON
(languages.id = language_preferences.language_id) WHERE
(weight_pounds <= NULL AND total_height_inches >= 60 AND minimum_pay
<= 2000 AND job_types.id = 4 AND regions.id = 1 AND languages.id in
(NULL)) AND ((models.body_type_id IN (4) AND
models.skin_color_id IN (4)))


#2

On Mar 19, 4:45 pm, David B. removed_email_address@domain.invalid wrote:

Is this triple join asking for trouble? I mean is my server going to
hate me? language, region and job_types are going to have less than
10 row entries, but job_type_preferences, language_preferences and
region_preferences will scale linearly with the number of users…

In the absence of all other things they fewer joins the better but if
they’re needed to do the job then that’s ok. Just be sure that you
have indexes on those columns you join on and keep an eye on the
explain output for that query.

Fred