Query explain: +----+-------------+----------------+-------------+-----------------------------------------------------+---------------------------------+---------+------------------------------------+------+------------------------------------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+-------------+-----------------------------------------------------+---------------------------------+---------+------------------------------------+------+------------------------------------------------------------------------------------------------------+ | 1 | SIMPLE | av3 | index_merge | val_text_cz,attr_value_fkindex1,item_id | attr_value_fkindex1,val_text_cz | 4,767 | NULL | 1 | Using intersect(attr_value_fkindex1,val_text_cz); Using where; Using temporary; Using filesort | | 1 | SIMPLE | av2 | index_merge | val_text_cz,attr_value_fkindex1,item_id | attr_value_fkindex1,val_text_cz | 4,767 | NULL | 4 | Using intersect(attr_value_fkindex1,val_text_cz); Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | av1 | index_merge | val_text_cz,attr_value_fkindex1,item_id | attr_value_fkindex1,val_text_cz | 4,767 | NULL | 4 | Using intersect(attr_value_fkindex1,val_text_cz); Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | item | eq_ref | PRIMARY,item_FKIndex4 | PRIMARY | 4 | jnp_datcz_data.av3.item_id | 1 | Using where | | 1 | SIMPLE | erp_object | eq_ref | PRIMARY | PRIMARY | 4 | jnp_datcz_data.av3.item_id | 1 | Using where; Using index; Distinct | | 1 | SIMPLE | iic | ref | item_in_category_FKIndex1,nmIndex | nmIndex | 4 | jnp_datcz_data.item.id | 1 | Using where; Using index; Distinct | | 1 | SIMPLE | item_available | ref | item_has_segment_FKIndex1,hidden,shop,multiple | multiple | 31 | const,const,jnp_datcz_data.item.id | 1 | Using index condition; Using where; Distinct | | 1 | SIMPLE | item_bind | ref | item_bind_index3301,item_has_item_FKIndex1 | item_bind_index3301 | 4 | jnp_datcz_data.item.id | 1 | Using where; Using index; Distinct | | 1 | SIMPLE | av0 | ref | val_text_cz,attr_value_fkindex1,item_id | item_id | 4 | jnp_datcz_data.item.id | 8 | Using index condition; Using where; Distinct | | 1 | SIMPLE | av4 | ref | attr_value_fkindex1,item_id,val_float | item_id | 4 | jnp_datcz_data.item.id | 8 | Using index condition; Using where; Distinct | | 1 | SIMPLE | price | ref | price_FKIndex1,validFrom,validTo,priceType,multiple | price_FKIndex1 | 4 | jnp_datcz_data.av1.item_id | 10 | Using index condition; Using where; Distinct | +----+-------------+----------------+-------------+-----------------------------------------------------+---------------------------------+---------+------------------------------------+------+------------------------------------------------------------------------------------------------------+ 11 rows in set (0.00 sec) Query explain with straight join hint used: +----+-------------+----------------+--------+-----------------------------------------------------+---------------------+---------+------------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+--------+-----------------------------------------------------+---------------------+---------+------------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | item | ref | PRIMARY,item_FKIndex4 | item_FKIndex4 | 4 | const | 468 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | item_available | ref | item_has_segment_FKIndex1,hidden,shop,multiple | multiple | 31 | const,const,jnp_datcz_data.item.id | 1 | Using index condition; Using where; Distinct | | 1 | SIMPLE | erp_object | eq_ref | PRIMARY | PRIMARY | 4 | jnp_datcz_data.item.id | 1 | Using index; Distinct | | 1 | SIMPLE | iic | ref | item_in_category_FKIndex1,nmIndex | nmIndex | 4 | jnp_datcz_data.item.id | 1 | Using where; Using index; Distinct | | 1 | SIMPLE | av0 | ref | val_text_cz,attr_value_fkindex1,item_id | item_id | 4 | jnp_datcz_data.item.id | 8 | Using index condition; Using where; Distinct | | 1 | SIMPLE | price | ref | price_FKIndex1,validFrom,validTo,priceType,multiple | price_FKIndex1 | 4 | jnp_datcz_data.item.id | 10 | Using where; Distinct | | 1 | SIMPLE | av1 | ref | val_text_cz,attr_value_fkindex1,item_id | item_id | 4 | jnp_datcz_data.item.id | 8 | Using index condition; Using where; Distinct | | 1 | SIMPLE | av2 | ref | val_text_cz,attr_value_fkindex1,item_id | item_id | 4 | jnp_datcz_data.item.id | 8 | Using index condition; Using where; Distinct | | 1 | SIMPLE | av3 | ref | val_text_cz,attr_value_fkindex1,item_id | item_id | 4 | jnp_datcz_data.item.id | 8 | Using index condition; Using where; Distinct | | 1 | SIMPLE | av4 | ref | attr_value_fkindex1,item_id,val_float | item_id | 4 | jnp_datcz_data.item.id | 8 | Using index condition; Using where; Distinct | | 1 | SIMPLE | item_bind | ref | item_bind_index3301,item_has_item_FKIndex1 | item_bind_index3301 | 4 | jnp_datcz_data.item.id | 1 | Using where; Using index; Distinct | +----+-------------+----------------+--------+-----------------------------------------------------+---------------------+---------+------------------------------------+------+----------------------------------------------+ 11 rows in set (0.01 sec)