mysql - 如何使用 join 优化查询

Question

我有一个查询大型查询，它需要更多的时间来执行。如何使用 Join 减少查询的执行时间。这是我的查询。

SELECT mdi.Member_Id,
dp.Payment1_Date AS Duchess_Payment1_Date,
dp.Payment1_Method AS Duchess_Payment1_Method,
dp.Payment1_By AS Duchess_Payment1_By,
dp.Payment1_Amount AS Duchess_Payment1_Amount,
dp.Payment2_Date AS Duchess_Payment2_Date,
dp.Payment2_Method AS Duchess_Payment2_Method,
dp.Payment2_By AS Duchess_Payment2_By,
dp.Payment2_Amount AS Duchess_Payment2_Amount,
mha.First_Name AS Member_First_Name,
mha.Middle_Name AS Member_Middle_Name,
mha.Last_Name AS Member_Last_Name,
mha.Address AS Member_Address,
mha.City AS Member_City,
mha.State AS Member_State,
mha.Zip AS Member_Zip,
mha.Nickname AS Member_Nickname,
mha.Phone AS Member_Phone,
mha.Mobile AS Member_Mobile,
mha.Email AS Member_Email,
mwa.First_Name AS Work_First_Name,
mwa.Middle_Name AS Work_Middle_Name,
mwa.Last_Name AS Work_Last_Name,
mwa.Address AS Work_Adrress,
mwa.City AS Work_City,
mwa.State AS Work_State,
mwa.Zip AS Work_Zip,
mwa.Phone AS Work_Phone,
(CASE WHEN mha.Preferred=0 THEN 'Work Address' WHEN mha.Preferred=1 THEN 'Home Address' END)AS Preferred,
mmt.Admiral_Title,
mmt.Spouse_Title,
mmt.Couple_Title,
mdi.Year AS Duchess_Year,
mdi.College AS Duchess_College,
mdi.Major AS Duchess_Major,
mdi.Sorority AS Duchess_Sorority,
mdi.Parent_Name AS Duchess_Parent_Name,
mdi.Escort_Name AS Duchess_Escort_Name
FROM duchess_payment dp
JOIN  member_duchess_info mdi ON mdi.Duchess_Id = dp.Duchess_Id 
JOIN  member_home_address mha ON mha.Member_Id = mdi.Member_Id
JOIN  member_work_address mwa ON mwa.Member_Id = mdi.Member_Id
JOIN  member_mailing_title mmt ON mmt.Member_Id = mdi.Member_Id
ORDER BY dp.Duchess_Id;

这就是我使用 .它是正确的吗？当我尝试执行该查询时，它仍然需要更多时间。

这里是查询的解释

Answer 1

对要执行连接的项目使用索引，例如 Duchess_Id、Member_Id 等