A question about JOIN.

Answer 1

Tell us the actual structure of the data and please show us the EXPLAIN of the query so we can see why it runs slow!

Only a guess: Are there indexes on the right coumns?

Answer 2

Your certain condition should be t2.id=t1.id and have more where clauses in your WHERE statement.

You may want to simplify this down to just have two select statements and see if it is slow, first.

You may be missing an index that would be helpful.

Once the two selects are fine, then you can add in the case command to the sql, and see what is going on, but don't change anything else in your query.

Then, you can give queries and times, which will help people to give a better answer.

Answer 3

Ok for the bleeding obvious : I suppose you have indexed the fields that you use in your joins?

Answer 4

OR clauses run slow and you should consider replacing them with a UNION which would still utilize any INDEXES you may have on your t1, t2, and t3 tables:

SELECT IFNULL(t1.part, t2.part) AS partno, t3.desc
FROM t1
LEFT JOIN t2 ON (condition here)
LEFT JOIN t3 ON (t1.part = t3.part)

UNION DISTINCT

SELECT IFNULL(t1.part, t2.part) AS partno, t3.desc
FROM t1
LEFT JOIN t2 ON (condition here)
LEFT JOIN t3 ON (t2.part = t3.part)

Also, your CASE() function, much the same as my simplified IFNULL() function, ends up using a temporary table. This is unavoidable when utilizing such functions.

Answer 5

Try:

   SELECT COALESCE(t1.part, t2.part) AS partno,
          COALESCE(t3.desc, t4.desc)    
     FROM t1 
LEFT JOIN join t2 ON [certain condition]
LEFT JOIN t3 ON t3.part = t1.part
LEFT JOIN t3 AS t4 ON t4.part = t1.part

OR's are notorious for poor performance.