I have a function "fnc_FindIssueId" which accepts an object id and return its assigned issue Id.
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?
解决方案
It does not perform never-ending loop.
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.
The best ways you can do are:
to alter the table smart_objects
another column for the assigned issue Id
define an index on the new column