I hava a View which would count the number of items inside a Taxonomy term. There are Field Collections, and inside each field collection there is one or more taxonomy terms. I have a contextual filter by taxonomy ID. The SQL is taking almost a second to retrieve a few hundred items (just the COUNT) from a 6,000 items database. Obviously, there is something wrong, because scalating from direct SQL checks, it should take no more than a hundredth or second. Where is the bottleneck in performance? The following SQL is adapted from Views GUI, slightly retyped to accomodate to phpMyAdmin and SQL_NO_CACHE added to check time performance.

 SELECT SQL_NO_CACHE COUNT(DISTINCT field_oem_ubicacion_field_collection_item__field_data_field_field_rf_producto.field_field_rf_producto_value) AS field_oem_ubicacion_field_collection_item__field_data_field_,
  COUNT(DISTINCT field_oem_ubicacion_field_collection_item__field_data_field_field_rf_producto.delta) AS field_oem_ubicacion_field_collection_item__field_data_field__1,  
  COUNT(DISTINCT field_oem_ubicacion_field_collection_item__field_data_field_field_rf_producto.language) AS field_oem_ubicacion_field_collection_item__field_data_field__2, 
 COUNT(DISTINCT field_oem_ubicacion_field_collection_item__field_data_field_field_rf_producto.bundle) AS field_oem_ubicacion_field_collection_item__field_data_field__3, 
 COUNT(DISTINCT field_oem_ubicacion_field_collection_item__field_data_field_field_rf_producto.field_field_rf_producto_format) AS field_oem_ubicacion_field_collection_item__field_data_field__4
FROM 
 field_collection_item AS field_collection_item
     LEFT JOIN field_data_field_oem_ubicacion AS      
 field_data_field_oem_ubicacion ON field_collection_item.item_id =     
 field_data_field_oem_ubicacion.field_oem_ubicacion_value
  LEFT JOIN node AS  field_oem_ubicacion_field_collection_item ON  
 field_data_field_oem_ubicacion.entity_id =   
 field_oem_ubicacion_field_collection_item.nid
     LEFT JOIN field_data_field_familia AS field_data_field_familia ON 
 field_collection_item.item_id = field_data_field_familia.entity_id AND  
 field_data_field_familia.entity_type = 'field_collection_item'
     LEFT JOIN field_data_field_field_rf_producto AS     
field_oem_ubicacion_field_collection_item__field_data_field_field_rf_producto    ON
field_oem_ubicacion_field_collection_item.nid =     
field_oem_ubicacion_field_collection_item__field_data_field_field_rf_producto.entity_id
 AND field_oem_ubicacion_field_collection_item__field_data_field_field_rf_producto.entity_type = 'node'
 WHERE
  (( (field_data_field_familia.field_familia_tid = '257738' ) ))
 LIMIT 1 OFFSET 0

I have a View which would count the number of items related to a Taxonomy term. The view counts Field Collections, and inside each field collection there is one or more taxonomy terms. I have a contextual filter by taxonomy ID. The SQL is taking almost a second to retrieve a few hundred items (just the COUNT) from a 6,000 items database. Obviously, there is something wrong, with those figures it presumably should take no more than a hundredth of second. Where is the bottleneck in performance? The following SQL is adapted from Views GUI, slightly retyped to accomodate to phpMyAdmin and the SQL_NO_CACHE option added to check for time performance.

 SELECT SQL_NO_CACHE COUNT(DISTINCT field_oem_ubicacion_field_collection_item__field_data_field_field_rf_producto.field_field_rf_producto_value) AS field_oem_ubicacion_field_collection_item__field_data_field_,
  COUNT(DISTINCT field_oem_ubicacion_field_collection_item__field_data_field_field_rf_producto.delta) AS field_oem_ubicacion_field_collection_item__field_data_field__1,  
  COUNT(DISTINCT field_oem_ubicacion_field_collection_item__field_data_field_field_rf_producto.language) AS field_oem_ubicacion_field_collection_item__field_data_field__2, 
 COUNT(DISTINCT field_oem_ubicacion_field_collection_item__field_data_field_field_rf_producto.bundle) AS field_oem_ubicacion_field_collection_item__field_data_field__3, 
 COUNT(DISTINCT field_oem_ubicacion_field_collection_item__field_data_field_field_rf_producto.field_field_rf_producto_format) AS field_oem_ubicacion_field_collection_item__field_data_field__4
FROM 
 field_collection_item AS field_collection_item
     LEFT JOIN field_data_field_oem_ubicacion AS      
 field_data_field_oem_ubicacion ON field_collection_item.item_id =     
 field_data_field_oem_ubicacion.field_oem_ubicacion_value
  LEFT JOIN node AS  field_oem_ubicacion_field_collection_item ON  
 field_data_field_oem_ubicacion.entity_id =   
 field_oem_ubicacion_field_collection_item.nid
     LEFT JOIN field_data_field_familia AS field_data_field_familia ON 
 field_collection_item.item_id = field_data_field_familia.entity_id AND  
 field_data_field_familia.entity_type = 'field_collection_item'
     LEFT JOIN field_data_field_field_rf_producto AS     
field_oem_ubicacion_field_collection_item__field_data_field_field_rf_producto    ON
field_oem_ubicacion_field_collection_item.nid =     
field_oem_ubicacion_field_collection_item__field_data_field_field_rf_producto.entity_id
 AND field_oem_ubicacion_field_collection_item__field_data_field_field_rf_producto.entity_type = 'node'
 WHERE
  (( (field_data_field_familia.field_familia_tid = '257738' ) ))
 LIMIT 1 OFFSET 0