Can this mysql subquery be shortened with JOINs?

Question

I am attempting to find all entries in the 'archive' table that have the same workID as any rows where historyID is NULL, worktype = "WEST", and the completeDate at least four months old. I am using a sub query to complete this request. Is there a more efficient way? My query is below.

SELECT * from archive b WHERE b.workID IN 
(SELECT a.workID FROM archive a, worktypes t 
WHERE a.historyID IS NULL AND (t.worktype IN ('WEST') AND a.worktype = t.id)
AND a.completeDate >= DATE(NOW() - Interval 4 month));

Additional Info: The tables are related only by the worktype. Basically I'm using the worktypes table just so I can type 'WEST' instead of giving an id number so it's easier to understand the query at-a-glance. I added on to the query. I didn't copy and paste it all. Oops.

Thank you!

Looks like you're missing a join condition between archive and worktypes. How are the two tables related? — Joe Stefanelli, Nov 30 '10 at 15:00
Your subquery doesn't have any join predicate between `a` and `t` so at the moment you are doing a cartesian join. This is very bad news in an `in` in mysql as [it will get evaluated repeatedly!](http://stackoverflow.com/questions/3417074/why-would-an-in-condition-be-slower-than-in-sql) — Martin Smith, Nov 30 '10 at 15:01

score 1 · Accepted Answer · answered Nov 30 '10 at 15:26

SELECT b.* 
    FROM archive b
        INNER JOIN archive a
            on b.workID = a.workID
                AND a.historyID IS NULL
                AND a.completeDate >= DATE(NOW() - Interval 4 month)
        INNER JOIN worktypes t
            ON a.worktype = t.id 
                AND t.worktype = 'WEST'

score 0 · Answer 2 · answered Nov 30 '10 at 15:01

0

The problem with your query and the proposed solution is that we don't know how the tables relate to each other. What column do you have in archive that is also in worktypes?

Also you should avoid "IN" where possible, it is inefficient.

answered Nov 30 '10 at 15:01

Aaron Scruggs

712
1
4
10

That is completely true, so if you want an exact solution please post more infos. Thanks Aaron Scruggs. – Hypnus Nov 30 '10 at 15:02

score 0 · Answer 3 · answered Nov 30 '10 at 15:01

Yeah sure: SELECT * from archive a LEFT JOIN worktypes t ON a.workID=t.workID WHERE a.historyID IS NULL AND t.worktype IN ('WEST') AND a.completeDate >= DATE(NOW() - Interval 4 month));

The LEFT JOIN will get all the entries in the first table and appends to them the equivalent columns from the matched second table.

Hopes this helps.

Can this mysql subquery be shortened with JOINs?

3 Answers3