CODEWITHSUNDEEP
mysqlsql
Mr R
Mr R

Reputation: 95

Improve performance mySQL query

Hi all champions out there

I am far from a guru when it comes to high performance SQL queries and and wonder in anyone can help me improve the query below. It works but takes far too long, especially since I can have 100 or so entries in the IN () part.

The code is as follows, hope you can figure out the schema enough to help.

SELECT  inv.amount
FROM    invoice inv
WHERE   inv.invoiceID IN (
        SELECT  childInvoiceID
        FROM    invoiceRelation ir
                LEFT JOIN Payment pay ON pay.invoiceID = ir.parentInvoiceID
        WHERE   pay.paymentID IN ( 125886, 119293, 123497 ) )

Upvotes: 0

Views: 76

Answers (4)

Rahul
Rahul

Reputation: 77866

What if you try like this instead:

SELECT  inv.amount
FROM    invoice inv
inner join invoiceRelation ir on inv.invoiceID = ir.parentInvoiceID
left join Payment pay on pay.invoiceID = ir.parentInvoiceID 
WHERE  pay.paymentID IN ( 125886, 119293, 123497 )

(OR) better; if you sure you have a invoiceID FK in payment table then make that left join to a inner join.

In a nutshell, you should try avoiding correlated subquery at all times if you can replace that subquery to a join.

Upvotes: 0

Jakob
Jakob

Reputation: 749

One way to improve performance is to have a good index on relevant columns. In your example, an index on inv.invoiceID would probably speed up the query quite a bit.

Also on pay.paymentID

Try this and see if it helps:

ALTER TABLE invoice ADD INDEX invoiceID_idx (invoiceID);

and

ALTER TABLE Payment ADD INDEX paymendID_idx (paymentID);

Upvotes: 2

A Paul
A Paul

Reputation: 8251

I think instead of first in you can use inner join.

select inv.amount from invoice inv 
inner join  invoiceRelation ir on (inv.invoiceID = ir.childInvoiceID)
LEFT JOIN Payment pay ON pay.invoiceID = ir.parentInvoiceID
WHERE pay.paymentID IN (125886,119293,123497)

Upvotes: 0

Dan
Dan

Reputation: 10680

Restructure your query to use a join instead of a subselect. Also, use an INNER JOIN instead of a LEFT JOIN to the Payment table. This is justified, since you have a WHERE filter that would filter rows without a match in the Payment table anyway.

SELECT inv.amount 
FROM invoice inv
INNER JOIN invoiceRelation ir ON inv.incoiceID = ir.childInvoiceID
INNER JOIN Payment pay on pay.invoiceID = ir.parentInvoiceID
WHERE pay.paymentID IN (...)

Upvotes: 3

Related Questions