为什么这个左外连接查询拒绝在 phpMyAdmin 中完成？

Question

编辑：所以查询确实有效，但在我的本地主机上运行需要一分钟多的时间。诚然，我的本地主机上的表要大得多（每个表大约 8000 行），但在生产中，每个表可能有 25,000 行以上。有什么办法可以优化这个，这样就不会花那么长时间吗？正如评论之一所示，两个表都已建立索引。

我有两个表，jos_eimcart_customers_addresses 和 jos_eimcart_customers。我想从客户表中提取所有记录，并包括地址表中可用的地址信息。我有一个我认为相当普通的左外连接查询，但它在 phpMyAdmin 中不断超时，即使它应该找到的结果并不多。谁能指出我是否做错了什么？我没有收到 mySQL 错误。

select 
    c.firstname,
    c.lastname,
    c.email as customer_email, 
    a.email as address_email,
    c.phone as customer_phone,
    a.phone as address_phone,
    a.company,
    a.address1,
    a.address2,
    a.city,
    a.state,a.zip, 
    c.last_signin
from jos_eimcart_customers c
    left outer join  jos_eimcart_customers_addresses a  
    on c.id = a.customer_id  
order by c.last_signin desc

Answer 1

您需要一个 group by 子句：

SELECT 
  c.firstname, 
  c.lastname, 
  c.email AS customer_email, 
  a.email AS address_email, 
  c.phone AS customer_phone, 
  a.phone AS address_phone, 
  a.company, 
  a.address1, 
  a.address2, 
  a.city, 
  a.state, 
  a.zip, 
  c.last_signin 
FROM jos_eimcart_customers c 
LEFT OUTER JOIN jos_eimcart_customers_addresses a ON c.id = a.customer_id 
GROUP BY 
  c.firstname, 
  c.lastname, 
  c.email AS customer_email, 
  a.email AS address_email, 
  c.phone AS customer_phone, 
  a.phone AS address_phone, 
  a.company, 
  a.address1, 
  a.address2, 
  a.city, 
  a.state, 
  a.zip, 
  c.last_signin 
ORDER BY c.last_signin DESC

Answer 2

您的查询没有任何问题（假设计数如您所说）。您的服务器中正在发生其他事情。

它有可能正在等待锁吗？