Select takes's longer the more joins are added

I've got a query where each of the 8 joins individually take 0.0007 seconds but when I combine them the time it takes to query grows exponential, ending up in a 3 second query.

Basically I have a flexible way of requesting venues with additional fields. It' looked pretty good with the first three fields, but then i noticed when I hit 7 that it takes crazy long time. I randomized the order of the additional fields, but it turns out it doesn't matter what field is added.

No additional fields[0.52 ms]

SELECT 
  `v`.`venue_id`, `v`.`venue_nm`, GROUP_CONCAT(DISTINCT vtr.tag_id SEPARATOR ",") tags 
FROM 
   `listing_venue` AS `v` 
INNER JOIN 
   `listing_venue_tag_rel` AS `vtr` ON vtr.venue_id = v.venue_id WHERE  (v.venue_id = 79) AND (v.published = 1) 
GROUP BY 
   `venue_id`

1 additional field [0.72 ms]

SELECT 
   `v`.`venue_id`, `v`.`venue_nm`, GROUP_CONCAT(DISTINCT vtr.tag_id SEPARATOR ",") tags, `addressvalue`.`value` AS `address`
FROM 
   `listing_venue` AS `v` 
INNER JOIN 
   `listing_venue_tag_rel` AS `vtr` ON vtr.venue_id = v.venue_id 
INNER JOIN 
   `listing_field` AS `addressfield` 
LEFT JOIN
   `listing_venue_field_rel` AS `addressvalue` ON addressvalue.venue_id = v.venue_id AND addressvalue.field_id = addressfield.field_id 
WHERE 
   (v.venue_id = 79) AND (v.published = 1) AND (addressfield.field_nm = 'address') 
GROUP BY 
   `venue_id`

2 additional fields [1.42 ms]

SELECT `v`.`venue_id`, `v`.`venue_nm`, GROUP_CONCAT(DISTINCT vtr.tag_id SEPARATOR ",") tags, `addressvalue`.`value` AS `address`, `address_zhvalue`.`value` AS `address_zh` FROM `listing_venue` AS `v` INNER JOIN `listing_venue_tag_rel` AS `vtr` ON vtr.venue_id = v.venue_id INNER JOIN `listing_field` AS `addressfield` LEFT JOIN `listing_venue_field_rel` AS `addressvalue` ON addressvalue.venue_id = v.venue_id AND addressvalue.field_id = addressfield.field_id INNER JOIN `listing_field` AS `address_zhfield` LEFT JOIN `listing_venue_field_rel` AS `address_zhvalue` ON address_zhvalue.venue_id = v.venue_id AND address_zhvalue.field_id = address_zhfield.field_id WHERE (v.venue_id = 79) AND (v.published = 1) AND (addressfield.field_nm = 'address') AND (address_zhfield.field_nm = 'address_zh') GROUP BY `venue_id`

3 -> 1.13 ms

SELECT `v`.`venue_id`, `v`.`venue_nm`, GROUP_CONCAT(DISTINCT vtr.tag_id SEPARATOR ",") tags, `addressvalue`.`value` AS `address`, `address_zhvalue`.`value` AS `address_zh`, `address_nearvalue`.`value` AS `address_near` FROM `listing_venue` AS `v` INNER JOIN `listing_venue_tag_rel` AS `vtr` ON vtr.venue_id = v.venue_id INNER JOIN `listing_field` AS `addressfield` LEFT JOIN `listing_venue_field_rel` AS `addressvalue` ON addressvalue.venue_id = v.venue_id AND addressvalue.field_id = addressfield.field_id INNER JOIN `listing_field` AS `address_zhfield` LEFT JOIN `listing_venue_field_rel` AS `address_zhvalue` ON address_zhvalue.venue_id = v.venue_id AND address_zhvalue.field_id = address_zhfield.field_id INNER JOIN `listing_field` AS `address_nearfield` LEFT JOIN `listing_venue_field_rel` AS `address_nearvalue` ON address_nearvalue.venue_id = v.venue_id AND address_nearvalue.field_id = address_nearfield.field_id WHERE (v.venue_id = 79) AND (v.published = 1) AND (addressfield.field_nm = 'address') AND (address_zhfield.field_nm = 'address_zh') AND (address_nearfield.field_nm = 'address_near') GROUP BY `venue_id`

4 -> 1.94 ms

SELECT `v`.`venue_id`, `v`.`venue_nm`, GROUP_CONCAT(DISTINCT vtr.tag_id SEPARATOR ",") tags, `addressvalue`.`value` AS `address`, `address_zhvalue`.`value` AS `address_zh`, `address_nearvalue`.`value` AS `address_near`, `phone_numbervalue`.`value` AS `phone_number` FROM `listing_venue` AS `v` INNER JOIN `listing_venue_tag_rel` AS `vtr` ON vtr.venue_id = v.venue_id INNER JOIN `listing_field` AS `addressfield` LEFT JOIN `listing_venue_field_rel` AS `addressvalue` ON addressvalue.venue_id = v.venue_id AND addressvalue.field_id = addressfield.field_id INNER JOIN `listing_field` AS `address_zhfield` LEFT JOIN `listing_venue_field_rel` AS `address_zhvalue` ON address_zhvalue.venue_id = v.venue_id AND address_zhvalue.field_id = address_zhfield.field_id INNER JOIN `listing_field` AS `address_nearfield` LEFT JOIN `listing_venue_field_rel` AS `address_nearvalue` ON address_nearvalue.venue_id = v.venue_id AND address_nearvalue.field_id = address_nearfield.field_id INNER JOIN `listing_field` AS `phone_numberfield` LEFT JOIN `listing_venue_field_rel` AS `phone_numbervalue` ON phone_numbervalue.venue_id = v.venue_id AND phone_numbervalue.field_id = phone_numberfield.field_id WHERE (v.venue_id = 79) AND (v.published = 1) AND (addressfield.field_nm = 'address') AND (address_zhfield.field_nm = 'address_zh') AND (address_nearfield.field_nm = 'address_near') AND (phone_numberfield.field_nm = 'phone_number') GROUP BY `venue_id`

5->9.7 ms

SELECT `v`.`venue_id`, `v`.`venue_nm`, GROUP_CONCAT(DISTINCT vtr.tag_id SEPARATOR ",") tags, `addressvalue`.`value` AS `address`, `address_zhvalue`.`value` AS `address_zh`, `address_nearvalue`.`value` AS `address_near`, `phone_numbervalue`.`value` AS `phone_number`, `websitevalue`.`value` AS `website` FROM `listing_venue` AS `v` INNER JOIN `listing_venue_tag_rel` AS `vtr` ON vtr.venue_id = v.venue_id INNER JOIN `listing_field` AS `addressfield` LEFT JOIN `listing_venue_field_rel` AS `addressvalue` ON addressvalue.venue_id = v.venue_id AND addressvalue.field_id = addressfield.field_id INNER JOIN `listing_field` AS `address_zhfield` LEFT JOIN `listing_venue_field_rel` AS `address_zhvalue` ON address_zhvalue.venue_id = v.venue_id AND address_zhvalue.field_id = address_zhfield.field_id INNER JOIN `listing_field` AS `address_nearfield` LEFT JOIN `listing_venue_field_rel` AS `address_nearvalue` ON address_nearvalue.venue_id = v.venue_id AND address_nearvalue.field_id = address_nearfield.field_id INNER JOIN `listing_field` AS `phone_numberfield` LEFT JOIN `listing_venue_field_rel` AS `phone_numbervalue` ON phone_numbervalue.venue_id = v.venue_id AND phone_numbervalue.field_id = phone_numberfield.field_id INNER JOIN `listing_field` AS `websitefield` LEFT JOIN `listing_venue_field_rel` AS `websitevalue` ON websitevalue.venue_id = v.venue_id AND websitevalue.field_id = websitefield.field_id WHERE (v.venue_id = 79) AND (v.published = 1) AND (addressfield.field_nm = 'address') AND (address_zhfield.field_nm = 'address_zh') AND (address_nearfield.field_nm = 'address_near') AND (phone_numberfield.field_nm = 'phone_number') AND (websitefield.field_nm = 'website') GROUP BY `venue_id`

6 -> 230.52 ms

SELECT `v`.`venue_id`, `v`.`venue_nm`, GROUP_CONCAT(DISTINCT vtr.tag_id SEPARATOR ",") tags, `addressvalue`.`value` AS `address`, `address_zhvalue`.`value` AS `address_zh`, `address_nearvalue`.`value` AS `address_near`, `phone_numbervalue`.`value` AS `phone_number`, `websitevalue`.`value` AS `website`, `price_maxvalue`.`value` AS `price_max` FROM `listing_venue` AS `v` INNER JOIN `listing_venue_tag_rel` AS `vtr` ON vtr.venue_id = v.venue_id INNER JOIN `listing_field` AS `addressfield` LEFT JOIN `listing_venue_field_rel` AS `addressvalue` ON addressvalue.venue_id = v.venue_id AND addressvalue.field_id = addressfield.field_id INNER JOIN `listing_field` AS `address_zhfield` LEFT JOIN `listing_venue_field_rel` AS `address_zhvalue` ON address_zhvalue.venue_id = v.venue_id AND address_zhvalue.field_id = address_zhfield.field_id INNER JOIN `listing_field` AS `address_nearfield` LEFT JOIN `listing_venue_field_rel` AS `address_nearvalue` ON address_nearvalue.venue_id = v.venue_id AND address_nearvalue.field_id = address_nearfield.field_id INNER JOIN `listing_field` AS `phone_numberfield` LEFT JOIN `listing_venue_field_rel` AS `phone_numbervalue` ON phone_numbervalue.venue_id = v.venue_id AND phone_numbervalue.field_id = phone_numberfield.field_id INNER JOIN `listing_field` AS `websitefield` LEFT JOIN `listing_venue_field_rel` AS `websitevalue` ON websitevalue.venue_id = v.venue_id AND websitevalue.field_id = websitefield.field_id INNER JOIN `listing_field` AS `price_maxfield` LEFT JOIN `listing_venue_field_rel` AS `price_maxvalue` ON price_maxvalue.venue_id = v.venue_id AND price_maxvalue.field_id = price_maxfield.field_id WHERE (v.venue_id = 79) AND (v.published = 1) AND (addressfield.field_nm = 'address') AND (address_zhfield.field_nm = 'address_zh') AND (address_nearfield.field_nm = 'address_near') AND (phone_numberfield.field_nm = 'phone_number') AND (websitefield.field_nm = 'website') AND (price_maxfield.field_nm = 'price_max') GROUP BY `venue_id`

7-> 3375.29 ms

SELECT 
    `v`.`venue_id`, `v`.`venue_nm`, GROUP_CONCAT(DISTINCT vtr.tag_id SEPARATOR ",") tags, `addressvalue`.`value` AS `address`, `address_zhvalue`.`value` AS `address_zh`, `address_nearvalue`.`value` AS `address_near`, `phone_numbervalue`.`value` AS `phone_number`, `websitevalue`.`value` AS `website`, `price_maxvalue`.`value` AS `price_max`, `price_minvalue`.`value` AS `price_min` 
FROM 
    `listing_venue` AS `v` 
INNER JOIN 
    `listing_venue_tag_rel` AS `vtr` 
    ON 
        vtr.venue_id = v.venue_id 
INNER JOIN
    `listing_field` AS `addressfield` 
LEFT JOIN 
    `listing_venue_field_rel` AS `addressvalue` 
    ON 
        addressvalue.venue_id = v.venue_id AND addressvalue.field_id = addressfield.field_id 
INNER JOIN 
    `listing_field` AS `address_zhfield` 
LEFT JOIN 
    `listing_venue_field_rel` AS `address_zhvalue` 
    ON 
        address_zhvalue.venue_id = v.venue_id AND address_zhvalue.field_id = address_zhfield.field_id 
INNER JOIN 
    `listing_field` AS `address_nearfield` 
LEFT JOIN 
    `listing_venue_field_rel` AS `address_nearvalue` 
    ON 
    address_nearvalue.venue_id = v.venue_id AND address_nearvalue.field_id = address_nearfield.field_id 
INNER JOIN 
    `listing_field` AS `phone_numberfield` 
LEFT JOIN 
    `listing_venue_field_rel` AS `phone_numbervalue` 
    ON 
        phone_numbervalue.venue_id = v.venue_id AND phone_numbervalue.field_id = phone_numberfield.field_id 
INNER JOIN 
    `listing_field` AS `websitefield` 
LEFT JOIN 
    `listing_venue_field_rel` AS `websitevalue` 
    ON 
        websitevalue.venue_id = v.venue_id AND websitevalue.field_id = websitefield.field_id 
INNER JOIN 
    `listing_field` AS `price_maxfield` 
LEFT JOIN 
    `listing_venue_field_rel` AS `price_maxvalue` 
    ON 
        price_maxvalue.venue_id = v.venue_id AND price_maxvalue.field_id = price_maxfield.field_id 
INNER JOIN 
    `listing_field` AS `price_minfield` 
LEFT JOIN 
    `listing_venue_field_rel` AS `price_minvalue`
    ON 
        price_minvalue.venue_id = v.venue_id AND price_minvalue.field_id = price_minfield.field_id 
WHERE 
    (v.venue_id = 79) 
AND (v.published = 1) 
AND (addressfield.field_nm = 'address') 
AND (address_zhfield.field_nm = 'address_zh') 
AND (address_nearfield.field_nm = 'address_near') 
AND (phone_numberfield.field_nm = 'phone_number') 
AND (websitefield.field_nm = 'website') 
AND (price_maxfield.field_nm = 'price_max') 
AND (price_minfield.field_nm = 'price_min') GROUP BY `venue_id`

Obviously I can just split query into 7, but I don't understand why this strange rise in time happens.

Also I'm not sure I am using the correct join. A field might be empty so that is why I am left joining the venue_field_rel...

here are the table structures

listing_field:
field_id | field_nm

listing_venue_field_rel
venue_id | field_id| value

listing_venue
venue_id | venue_nm

EXPLAIN

id  select_type     table               type    possible_keys                                                                                       key                 key_len     ref     rows    Extra
1   SIMPLE          v                   const   PRIMARY,item_id_UNIQUE                                                                              PRIMARY             4           const   1    
1   SIMPLE          addressfield        ALL     NULL                                                                                                NULL                NULL        NULL    7       Using where
1   SIMPLE          addressvalue        ref     field_value_venue,fk_mnc_listing_venue_field_rel_venue_id,fk_mnc_listing_venue_field_rel_field_id   field_value_venue   4           const   6       Using index
1   SIMPLE          address_zhfield     ALL     NULL  开发者_Python百科                                                                                              NULL                NULL        NULL    7       Using where; Using join buffer
1   SIMPLE          address_zhvalue     ref     field_value_venue,fk_mnc_listing_venue_field_rel_venue_id,fk_mnc_listing_venue_field_rel_field_id   field_value_venue   4           const   6       Using index
1   SIMPLE          address_nearfield   ALL     NULL                                                                                                NULL                NULL        NULL    7       Using where; Using join buffer
1   SIMPLE          address_nearvalue   ref     field_value_venue,fk_mnc_listing_venue_field_rel_venue_id,fk_mnc_listing_venue_field_rel_field_id   field_value_venue   4           const   6       Using index
1   SIMPLE          phone_numberfield   ALL     NULL                                                                                                NULL                NULL        NULL    7       Using where; Using join buffer
1   SIMPLE          phone_numbervalue   ref     field_value_venue,fk_mnc_listing_venue_field_rel_venue_id,fk_mnc_listing_venue_field_rel_field_id   field_value_venue   4           const   6       Using index
1   SIMPLE          websitefield        ALL                                                                                                         NULL                NULL        NULL    7       Using where; Using join buffer
1   SIMPLE          websitevalue        ref     field_value_venue,fk_mnc_listing_venue_field_rel_venue_id,fk_mnc_listing_venue_field_rel_field_id   field_value_venue   4           const   6       Using index
1   SIMPLE          price_maxfield      ALL                                                                                                         NULL                NULL        NULL    7       Using where; Using join buffer
1   SIMPLE          price_maxvalue      ref     field_value_venue,fk_mnc_listing_venue_field_rel_venue_id,fk_mnc_listing_venue_field_rel_field_id   field_value_venue   4           const   6       Using index
1   SIMPLE          price_minfield      ALL                                                                                                         NULL                NULL        NULL    7       Using where; Using join buffer
1   SIMPLE          price_minvalue      ref     field_value_venue,fk_mnc_listing_venue_field_rel_venue_id,fk_mnc_listing_venue_field_rel_field_id   field_value_venue   4           const   6       Using index
1   SIMPLE          vtr                 ref     item_tag,fk_venue_id                                                                                fk_venue_id         4           const   11      Using index

EDIT I "index uniqued" the field_nm which reduced the speed back to 7 ms

id  select_type table               type    possible_keys           key                 key_len     ref     rows    Extra
1   SIMPLE      v                   const   PRIMARY,item_id_UNIQUE  PRIMARY             4           const   1    
1   SIMPLE      addressfield        const   field_nm,field_nm_2     field_nm            137         const   1   Using index
1   SIMPLE      address_zhfield     const   field_nm,field_nm_2     field_nm            137         const   1   Using index
1   SIMPLE      address_nearfield   const   field_nm,field_nm_2     field_nm            137         const   1   Using index
1   SIMPLE      phone_numberfield   const   field_nm,field_nm_2     field_nm            137         const   1   Using index
1   SIMPLE      websitefield        const   field_nm,field_nm_2     field_nm            137         const   1   Using index
1   SIMPLE      price_maxfield      const   field_nm,field_nm_2     field_nm            137         const   1   Using index
1   SIMPLE      addressvalue        ref     field_value_venue,...   field_value_venue   4           const   7   Using index
1   SIMPLE      address_zhvalue     ref     field_value_venue,...   field_value_venue   4           const   7   Using index
1   SIMPLE      address_nearvalue   ref     field_value_venue,...   field_value_venue   4           const   7   Using index
1   SIMPLE      phone_numbervalue   ref     field_value_venue,...   field_value_venue   4           const   7   Using index
1   SIMPLE      websitevalue        ref     field_value_venue,...   field_value_venue   4           const   7   Using index
1   SIMPLE      price_maxvalue      ref     field_value_venue,...   field_value_venue   4           const   7   Using index
1   SIMPLE      vtr                 ref     item_tag,fk_venue_id    fk_venue_id         4           const   17  Using index

I'm sure however that the query can be optimized some more

---

I think you may be doing a CROSS JOIN here

INNER JOIN listing_field AS address_zhfield LEFT JOIN

You have no ON clause so you get a cartesian product with everything else, which would probably lead to an exponential slow down.