mysql - 选择子查询中的最后一条记录

Question

已阅读所有类似的问题，但无法应用于我的 sql

想要选择所有客户和最后插入的操作记录。下面的 sql 首先选择最大 actionid 然后在另一个子查询上使用它 - 这需要 5+secs 来运行；(

请告知TQ

SELECT cus.cusid,cus.FirstName,cus.Surname,
       lastact.actionid, lastact.actiondate, lastact.siteid
FROM cus 
    LEFT JOIN(
        SELECT MAX(actionid) AS maxactionid, cusid
        FROM `action`
        INNER JOIN `event` ON event.eventid = action.`eventid`
        GROUP BY cusid
    ) AS maxactionid ON maxactionid.cusid = cus.cusid
    LEFT JOIN (
    SELECT 
    action.actionid,
    action.actiondate,
    event.cusid,
    event.siteid 
    FROM
      `action` 
      INNER JOIN `event` 
        ON event.eventid = action.eventid 
    ORDER BY actionid DESC
    ) AS lastact ON lastact.actionid = maxactionid 
WHERE UCASE(CONCAT(firstname, surname)) LIKE '%JIM%HEMM%'

想法的 TQ - 请参阅以下内容:

1) 限制想法，为 lastact.actionid、lastact.actiondate、lastact.siteid 提供空结果 - 但确实在 0.075 秒内运行!可惜这个想法失败了

SELECT cus.cusid,cus.FirstName,cus.Surname, lastact.actionid, lastact.actiondate, lastact.siteid 
FROM cus 
LEFT JOIN (SELECT action.actionid, action.actiondate, event.cusid, event.siteid 
FROM action 
INNER JOIN event ON event.eventid = action.eventid 
ORDER BY actionid DESC LIMIT 1 
) AS lastact ON lastact.cusid = cus.cusid 
WHERE UCASE(CONCAT(firstname, surname)) LIKE '%JIM%HEMM%' 

2) 原始查询的 EXPLAIN 结果为:

3) 添加 LIKE 'JIM%' AND cus.surname LIKE 'HEMM%' 不会对查询时间产生太大影响，但会根据建议包含

您好 - 通过使用每个人的想法取得了很好的结果 - 谢谢

1) 将 WHERE 更改为 cus.FirstName LIKE 'JIM%' AND cus.surname LIKE 'H%'

2) 添加了名字、姓氏的索引

3) 在 Action 表中添加了 cusid(不再需要事件表)

4) 将查找表(不在原始问题中)移到操作子查询之外

完成的 sql 看起来像(在 0.063 秒内运行 - 用只有一个字母的姓氏测试!)

SELECT cus.cusid,cus.FirstName,cus.Surname, lastact.actionid, lastact.actiondate, lastact.siteid, actiontype.action,
FROM cus 
LEFT JOIN (
    SELECT action.actionid, action.actiondate, event.cusid, event.siteid 
    FROM action  
    ORDER BY actionid DESC
) AS lastact ON lastact.cusid = cus.cusid
LEFT JOIN actiontype ON actiontype.actiontypeid = lastact.typeid 
WHERE cus.FirstName LIKE 'JIM%' AND cus.surname LIKE 'H%'
GROUP BY lastact.cusid  

Answer 1

• 正如 JC Sama 所说“通过选择 actionid 更改 select MAX(actionid) 并添加限制 1 并按 desc 排序”，有助于索引搜索。
• 正如 David K-J 所说，“首先运行 EXPLAIN 以查看规划器试图做什么。我怀疑这是对串联字符串的(不可索引的)搜索”。
• 在比较时，您不应该将 clown “%”放在字符串的开头，这会禁用索引搜索。
• 您不应该在比较时使用函数(至少，如果可以的话，避免使用它们)，对于索引搜索也是如此。
• 现在您可以使用索引了，如果您还没有使用，请添加它们。
• 我在这里可能是错的，但就我而言，我不明白最后一个 LEFT JOIN 的意义。您可以从第一个 LEFT JOIN 中提取该数据。你为什么要按 cusid 分组。

总而言之，我做的sql是(显然没有测试过，你可能需要修复一些东西):

SELECT cus.cusid,cus.FirstName,cus.Surname,
maxaction.actionid, maxaction.actiondate, maxaction.siteid
FROM cus 
LEFT JOIN(
    SELECT actionid AS maxaction, action.actiondate, event.cusid, event.siteid
    FROM `action`
    INNER JOIN `event` ON event.eventid = action.eventid
    order by actionid desc limit 1
) AS maxaction ON maxaction.cusid = cus.cusid
WHERE cus.FirstName like 'JIM%' and cus.surname like 'HEMM%'