php - mysql 查询需要很长时间

Question

我的下面的查询花费了超过 10 秒的时间。我如何优化这个查询。请建议在哪个字段添加索引。此查询是距离搜索的 drupal View 。

    SELECT DISTINCT location.lid AS location_lid, node.nid AS nid, location.name AS location_name, location_phone.phone AS location_phone_phone, location_fax.fax AS location_fax_fax, node.title AS node_title, node.language AS node_language, location.additional AS location_additional, location.city AS location_city, location.latitude AS location_latitude, location.longitude AS location_longitude, location.country AS location_country, location.postal_code AS location_postal_code, location.province AS location_province, location.street AS location_street, field_data_field_showroom_level.field_showroom_level_value AS field_data_field_showroom_level_field_showroom_level_value, field_data_field_showroom_type.field_showroom_type_value AS field_data_field_showroom_type_field_showroom_type_value, field_data_field_showroom_inventory_records.field_showroom_inventory_records_value AS field_data_field_showroom_inventory_records_field_showroom_i, field_data_field_record_type_id.field_record_type_id_value AS field_data_field_record_type_id_field_record_type_id_value, field_data_field_toto_gallery.field_toto_gallery_value AS field_data_field_toto_gallery_field_toto_gallery_value, 'node' AS field_data_field_showroom_location_node_entity_type, 'node' AS field_data_field_day_of_operation_node_entity_type, 'node' AS field_data_field_day_of_operation_comments_node_entity_type, 'node' AS field_data_field_showroom_open_node_entity_type, 'node' AS field_data_field_showroom_close_node_entity_type, 'node' AS field_data_field_appointment_needed_node_entity_type, (COALESCE(ACOS(0.83308162381476*COS(RADIANS(location.latitude))*(0.098658826854837*COS(RADIANS(location.longitude)) + -0.99512131716873*SIN(RADIANS(location.longitude))) + 0.55315007734083*SIN(RADIANS(location.latitude))), 0.00000)*6371570.9190939) AS location_distance, 'node' AS field_data_field_salesforce_id_node_entity_type, 'node' AS field_data_field_multiple_product_sku_node_entity_type, 'node' AS field_data_field_product_types_node_entity_type, 'node' AS field_data_field_toto_gallery_node_entity_type, 'node' AS field_data_field_showroom_level_node_entity_type
FROM 
node node
LEFT JOIN location_instance location_instance ON node.vid = location_instance.vid
LEFT JOIN location location ON location_instance.lid = location.lid
INNER JOIN field_data_field_toto_gallery field_data_field_toto_gallery ON node.nid = field_data_field_toto_gallery.entity_id AND (field_data_field_toto_gallery.entity_type = 'node' AND field_data_field_toto_gallery.deleted = '0')
LEFT JOIN field_data_field_multiple_product_sku field_data_field_multiple_product_sku ON node.nid = field_data_field_multiple_product_sku.entity_id AND (field_data_field_multiple_product_sku.entity_type = 'node' AND field_data_field_multiple_product_sku.deleted = '0')
LEFT JOIN location_phone location_phone ON location_instance.lid = location_phone.lid
LEFT JOIN location_fax location_fax ON location_instance.lid = location_fax.lid
LEFT JOIN field_data_field_showroom_level field_data_field_showroom_level ON node.nid = field_data_field_showroom_level.entity_id AND (field_data_field_showroom_level.entity_type = 'node' AND field_data_field_showroom_level.deleted = '0')
LEFT JOIN field_data_field_showroom_type field_data_field_showroom_type ON node.nid = field_data_field_showroom_type.entity_id AND (field_data_field_showroom_type.entity_type = 'node' AND field_data_field_showroom_type.deleted = '0')
LEFT JOIN field_data_field_showroom_inventory_records field_data_field_showroom_inventory_records ON node.nid = field_data_field_showroom_inventory_records.entity_id AND (field_data_field_showroom_inventory_records.entity_type = 'node' AND field_data_field_showroom_inventory_records.deleted = '0')
LEFT JOIN field_data_field_record_type_id field_data_field_record_type_id ON node.nid = field_data_field_record_type_id.entity_id AND (field_data_field_record_type_id.entity_type = 'node' AND field_data_field_record_type_id.deleted = '0')
WHERE ((    (node.status = '1') 
        AND (node.type IN  ('showrooms')) 
           AND (location.latitude > '32.859795378699'
           AND location.latitude < '34.306986221301'
           AND location.longitude > '-85.206632058424'
           AND location.longitude < '-83.469478341576')
        AND ((COALESCE(ACOS(0.83308162574562*COS(RADIANS(location.latitude))*(0.098658823381208*COS(RADIANS(location.longitude)) + -0.99512131751311*SIN(RADIANS(location.longitude))) + 0.55315007443282*SIN(RADIANS(location.latitude))), 0.00000)*6371570.9191628) < '80467.35')
        AND (location.province = 'GA') 
        AND (location.city LIKE 'Atlanta' ESCAPE '\\') )
        AND( (field_data_field_toto_gallery.field_toto_gallery_value = '1')
           OR (field_data_field_multiple_product_sku.field_multiple_product_sku_value NOT LIKE 'NULL' ESCAPE '\\') ))

ORDER BY field_data_field_showroom_level_field_showroom_level_value DESC, 
         field_data_field_showroom_type_field_showroom_type_value DESC, 
         location_distance ASC, 
         field_data_field_showroom_inventory_records_field_showroom_i DESC,
         field_data_field_record_type_id_field_record_type_id_value ASC,          
         field_data_field_toto_gallery_field_toto_gallery_value DESC
LIMIT 10 OFFSET 0

Answer 1

我建议您了解MySQL的PROCEDURE ANALYSE函数:http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html

它会告诉您哪些连接可能没有可以使用的索引。仅查看您的查询而不了解表的结构或数据，我们无法为您提供良好的建议。通常，您应该在查询的 JOIN 和 ORDER BY 部分中使用的每一列上都有一个索引，并且可能对 WHERE 子句中使用的列有一些索引。