MySQL uses intersection instead compound index I have compound index on following columns:

(country_id, lazy, has_avatar, inactive, gender, id, age, challenge_count)

When I run following query:

SELECT COUNT(*)
FROM `user_profile`
WHERE (`user_profile`.`lazy` = 0 AND
      `user_profile`.`has_avatar` = 1 AND 
      `user_profile`.`inactive` = 1 AND 
       NOT (`user_profile`.`id` = 3247028 ) AND 
      `user_profile`.`country_id` = 374 AND
      `user_profile`.`challenge_count` &lt; 10 AND
      `user_profile`.`age` BETWEEN 18 and 28 AND
      `user_profile`.`gender` = 'F' )


It is used correctly. However, when I run following query it is not used. 

The WHERE parameters are the same. In this case mysql uses intersection of separate indexes:

SELECT * 
FROM `user_profile` 
INNER JOIN `iqApp_basecountry` 
ON ( `user_profile`.`country_id` = `iqApp_basecountry`.`id` ) 
INNER JOIN `auth_user` ON ( `user_profile`.`user_id` = `auth_user`.`id` ) 
LEFT OUTER JOIN `iqApp_relationshipstatus` 
ON ( `user_profile`.`relationship_status_id` = `iqApp_relationshipstatus`.`id` )
LEFT OUTER JOIN `iqApp_workstatus` 
ON ( `user_profile`.`work_status_id` = `iqApp_workstatus`.`id` ) 
LEFT OUTER JOIN `iqApp_fieldofwork`
ON ( `user_profile`.`field_of_work_id` = `iqApp_fieldofwork`.`id` ) 
LEFT OUTER JOIN `iqApp_fieldofstudy`
ON ( `user_profile`.`field_of_study_id` = `iqApp_fieldofstudy`.`id` ) 
LEFT OUTER JOIN `iqApp_educationlevel` 
ON ( `user_profile`.`education_level_id` = `iqApp_educationlevel`.`id` )
LEFT OUTER JOIN `iqApp_religion` ON ( `user_profile`.`religion_id` = `iqApp_religion`.`id` )
WHERE (`user_profile`.`lazy` = 0 AND
     `user_profile`.`has_avatar` = 1 AND 
     `user_profile`.`inactive` = 1 AND NOT 
      (`user_profile`.`id` = 3247028 ) AND 
     `user_profile`.`country_id` = 374 AND
     `user_profile`.`challenge_count` &lt; 10 AND
     `user_profile`.`age` BETWEEN 18 and 28 AND 
     `user_profile`.`gender` = 'F' ) 
  LIMIT 1


Here is explain:

SELECT_TYPE: SIMPLE 
Table: user_profile 
Type: index_merge   

POSSIBLE_KEYS: PRIMARY,user_id,user_profile_d860be3c,age,gender,challenge_count,lazy,has_avatar,inactive,country_id,country_id_2    

KEY
user_profile_d860be3c,lazy,has_avatar,inactive,gender   

KEY_LEN
5,1,1,1,5   

REF
None    

ROWS
35394   

EXTRA
Using intersect(user_profile_d860be3c,lazy,has_avatar,inactive,gender); Using where
The selectivity has changed with the added selections.So now the optimizer will think that an intersection of indexes is more efficient than just one index.Compound indexes have a cardinality that matches all their columns,it`s not separate for column so MySQL will try to reduce this selection with an index intersection.Find out which column is the most selective(has the least values returned) and rewrite it with this in mind.

You could turn off index merge with a SESSION option but this the bulldozer approach.