Trying to optimize MySQL query with LEFT OUTER JOIN?
Unfortunately mysql (and perhaps any dbms) cannot optimize expressions like jobs. Status! = 331 and ack = 0 because B-Tree is not a structure that allows to find fast anything that is-not-equal-to-a-constant-value.
Thus you'll always get a fullscan If there were some better condition like jobs. Status = 331 and ack = 0 (note on the fact that i've changed! To ) then it would be an advice to speed up this query: split the query into 2, joined by UNION ALL replace in one query LEFT JOIN to INNER JOIN (in the one that implies that wq.Info is not NULL ).
Unfortunately mysql (and perhaps any dbms) cannot optimize expressions like jobs. Status! = 331 and ack = 0 because B-Tree is not a structure that allows to find fast anything that is-not-equal-to-a-constant-value.
Thus you'll always get a fullscan. If there were some better condition like jobs. Status = 331 and ack = 0 (note on the fact that i've changed!
= to =) then it would be an advice to speed up this query: split the query into 2, joined by UNION ALL replace in one query LEFT JOIN to INNER JOIN (in the one that implies that wq. Info is not NULL).
Replacing jobs. Status! = 331 for jobs.
Status in (311,321,341) (which are the other possible values) did not help. I don't understand the other options. If I do a UNION, wouldn't that be running two queries and joining the resutls?
How can that be faster? – szysz Mar 26 at 17:14 separating the query with an UNION made it fast: SELECT jobs. * FROM jobs,wq WHERE (wq.
Job = jobs. Id AND jobs. Status IN (341, 331) AND wq.
Status IN ( 101, 111, 151 )) UNION SELECT jobs. * FROM jobs WHERE (1=1) and (jobs. Status in (311, 321, 341) and ack = 0) – szysz Mar 26 at 18:02.
Unfortunately mysql (and perhaps any dbms) cannot optimize expressions like jobs. Status! = 331 and ack = 0 because B-Tree is not a structure that allows to find fast anything that is-not-equal-to-a-constant-value.
Thus you'll always get a fullscan.
I've this query, which works fine except it takes a long while (7 seconds, with 40k records in the jobs table, and 700k in the wq table). I tried an EXPLAIN and it says its looking at all the records in the job table, and not using any of the indexes. I don't know how to tell MySQL that it should use the jobs.
Status field to filter the the records before looking up the wq table. The objective of this, is to get all the records from jobs that have a status!
I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.