CODEWITHSUNDEEP
sqlmysql
Luiscencio
Luiscencio

Reputation: 3965

A question about JOIN

I need to do something like this to fill a parts table:

SELECT (CASE t1.part IS NULL THEN t2.part ELSE t1.part END) AS partno,
       t3.desc    
FROM t1 
LEFT JOIN join t2 ON [certain condition]
LEFT JOIN t3 ON t1.part = t3.part 
             OR t2.part = t3.part

...so this will select the value for partno from t2 in case that part is null in t1, then i need to take the description from t3 but when I run it it takes forever and never return the results, How can I do this faster? if I am missing some details please ask.

this are the tables

alt text http://img15.imageshack.us/img15/3878/74385879.png

this is the actual procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `getMonthDetail` $$
CREATE DEFINER=`root`@`%` PROCEDURE `getMonthDetail`(fechai Date, wid int)
BEGIN

select distinct
ins.inventoryinid,
(
select group_concat(concat(documents.documentname,': ', inventoryin_documents.documentno))
from inventoryin_documents
left join documents on documents.documentid=inventoryin_documents.documentid
where inventoryin_documents.inventoryinid = docin.inventoryinid
group by inventoryin_documents.inventoryinid
)as docin,
trace.inventoryoutid,
(
select group_concat(concat(documents.documentname,': ', inventoryout_documents.documentno))
from inventoryout_documents
left join documents on documents.documentid=inventoryout_documents.documentid
where inventoryout_documents.inventoryoutid = docout.inventoryoutid
group by inventoryout_documents.inventoryoutid
) as docout,
outs.inventoryoutdate,
(case when trace.partnumberp is null then indetails.partnumberp else trace.partnumberp end) as nopart,
p.descriptionsmall,
trace.quantity


from
inventoryin as ins
left join inventoryinouttrace as trace on trace.inventoryinid = ins.inventoryinid
left join inventoryin_documents as docin on docin.inventoryinid = ins.inventoryinid
left join inventoryout_documents as docout on docout.inventoryoutid = trace.inventoryoutid
left join inventoryout as outs on outs.inventoryoutid = trace.inventoryoutid
left join inventoryindetails indetails on ins.inventoryinid = indetails.inventoryinid
left join product as p on trace.partnumberp=p.partnumberp

where
((ins.inventorydate > fechai+0 and ins.inventorydate < fechai+100)
or (outs.inventoryoutdate > fechai+0 and outs.inventoryoutdate < fechai+100));

END $$

DELIMITER ;

and when I Hit the explain button in the query browser it returns a error...

Upvotes: 0

Views: 91

Answers (5)

OMG Ponies
OMG Ponies

Reputation: 332531

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.

Upvotes: 1

Corey Ballou
Corey Ballou

Reputation: 43457

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.

Upvotes: 1

Peter
Peter

Reputation: 48958

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

Upvotes: 0

James Black
James Black

Reputation: 41858

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.

Upvotes: 0

theomega
theomega

Reputation: 32031

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?

Upvotes: 0

Related Questions