mysql - 为什么这个左外连接查询拒绝在 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