本文介绍了如何在MySQL中使用带有INNER JOIN的SQL函数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个函数"fnc_FindIssueId",该函数接受一个对象ID并返回其分配的问题ID.

I have a function "fnc_FindIssueId" which accepts an object id and return its assigned issue Id.

当我使用纯select语句调用该函数时,它可以正常工作:

When I call the function using pure select statements, it works fine:

select fnc_FindIssueId(150083); // returns 1 as issueId for objectId of 150083
select fnc_FindIssueId(150072); // returns 2 as issueId for objectId of 150072

但是当我在内部联接中使用它时,它会陷入无休止的循环:

But when I use it within an Inner Join, it goes into a never-ending loop:

select so.id, si.id
from smart_objects as so
       LEFT OUTER join smart_issues as si
        on si.id = fnc_FindIssueId(so.id)
where so.id in (150083, 150072);

原因是什么,如何解决?

What's the reason and how to resolve it?

推荐答案

它不执行永无止境循环.

之所以这样做,是因为服务器执行FULL TABLE SCAN的速度非常慢.即使您已在si.idso.id上定义一个索引,此条件si.id = fnc_FindIssueId(so.id)也不使用索引.

The reason for that is because the server performs FULL TABLE SCAN which is very slow. This condition si.id = fnc_FindIssueId(so.id) doesn't use an index even if you have define one on si.id and so.id.

最好的方法是:

  • 更改表smart_objects
  • assigned issue Id
  • 的另一列
  • 在新列上定义索引
  • to alter the table smart_objects
  • another column for the assigned issue Id
  • define an index on the new column

这篇关于如何在MySQL中使用带有INNER JOIN的SQL函数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-23 03:11