I have a huge query which for some reason, runs without ending(after 1 hour I stopped the process because obviously something was wrong). The query itself is too bulky to paste here so I will try to paste only the part that (i think) causes the problem:
select l.id,l.date, (select group_concat(u.id) from Logs l2 join Users u... where u.id not in (select u2.id from Logs l3 join Users u2 where....and l3.id=l.id)) from Logs l
The problem lies at the "and l3.id=l.id" part. Without this small comparison, the query runs in 14 seconds. So I'm pretty sure this is the problem. Now the Logs table have about 5 million records, and I'm guessing that somehow he tries to compare each of the 5 mil records with (again) each of the 5 mil recors, causing these huge periods of time. Can anybody actually tell me if the comparison makes any sense, since I find it weird to compare the id's of the same tables between themselves (both l3 and l are infact Logs table). In my oppinion the comparison will always return true so there is no reason for it. Am I right or am I missing some fundamental rules here?