慢速查询 - 多个连接和大量数据

问题描述：

我确信有更好的方法可以做到这一点。我有三个包含大量数据的主表： records_main，销售和约会。每个记录接近20,000条记录。慢速查询 - 多个连接和大量数据

我需要加入这三个表以及其他几个不是两个大的表。

$SQL = "SELECT DISTINCT appointments.id AS aid, appointments.date, appointments.estimate_price, appointments.next_action, appointments.next_action_date, appointments.result, appointments.result_type, appointments.notes, 
customer.id AS cid, customer.homeowner1_fname, customer.homeowner1_lname, customer.address, customer.city, customer.state, customer.zipcode, customer.phone1, customer.phone1_type, customer.phone2, customer.phone2_type, customer.phone3, customer.phone3_type, customer.phone4, customer.phone4_type, customer.phone5, customer.phone5_type, customer.lead_source, customer.lead_category, customer.primary_interest, customer.secondary_interest, customer.additional_interest1, customer.additional_interest2, 
originator.employee_id AS originator_employee_id,originator.fname AS originator_fname,originator.lname AS originator_lname, 
setter.employee_id AS setter_employee_id,setter.fname AS setter_fname,setter.lname AS setter_lname, 
resetter.employee_id AS resetter_employee_id, resetter.fname AS resetter_fname, resetter.lname AS resetter_lname, 
salesrep.employee_id AS salesrep_employee_id, salesrep.fname AS salesrep_fname, salesrep.lname AS salesrep_lname, 
salesrep2.employee_id AS salesrep2_employee_id, salesrep2.fname AS salesrep2_fname, salesrep2.lname AS salesrep2_lname 
FROM 
core_records_appointments as appointments 
INNER JOIN core_records_main as customer ON appointments.customer = customer.id 
LEFT JOIN core_employees_main as originator ON appointments.originator = originator.id 
LEFT JOIN core_employees_main as setter ON appointments.setter = setter.id 
LEFT JOIN core_employees_main as resetter ON appointments.resetter = resetter.id 
LEFT JOIN core_employees_main as salesrep ON appointments.sales_representative = salesrep.id 
LEFT JOIN core_employees_main as salesrep2 ON appointments.sales_representative2 = salesrep2.id 
";

这需要几秒钟，但最终确实加载。最后加入我把虽然似乎打破查询一起：由

什么我可以做些什么来改善这种

LEFT JOIN core_records_sales as sales ON appointments.day = sales.day_sold AND appointments.customer = sales.customer

这个我有一个下限设定后和一组？我在jqgrid中使用它，不确定是否有帮助

答

作为第一种方法，尝试使用EXPLAIN语法在数据库上执行查询。这将给你一个分析的声明，并告诉你，如果它使用索引。如果它不使用索引或没有足够的索引，请尝试将它们添加到表中。

我想，当我尝试匹配的sales.day和appts.day它卡住，因为没有钥匙。我从来没有超过一个表中的关键。我将如何着手设置？它会导致与其他任何问题？也就是说：这两个ID都是主键 – kilrizzy 2009-12-02 18:54:29

您可以将表的结构添加到问题中吗？（描述）这将有助于回答这个问题 – 2009-12-02 19:17:05

答

你有没有试过给所有你左边加入的列索引？

尝试给指数以所有这些，如果他们不已经被声明为指数

慢速查询 - 多个连接和大量数据

相关推荐