Improve performance mySQL query

Question

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 ) )

Does your tables have indexes on the columns in the WHERE parts of your queries? — Jakob
– Jakob, Commented Dec 17, 2013 at 12:49

Dan · Accepted Answer · 2013-12-17 12:49:40Z

3

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 (...)

answered Dec 17, 2013 at 12:49

Dan

10.8k27 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mr R Over a year ago

Wow what a difference. From 380 ms to 0.4 ms. Thanks also for you explanation to your solution, helps me understand. Thanks Dan.

Jakob · Accepted Answer · 2013-12-17 12:55:49Z

2

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);

edited Dec 17, 2013 at 12:55

answered Dec 17, 2013 at 12:50

Jakob

7384 silver badges17 bronze badges

2 Comments

K.C. Over a year ago

+1 Most of the time, indexes are most important to improve performance of selects.

Mr R Over a year ago

Yup I had the indexes there but I guess my query did not use them due to the huge where clause. Now they work :-) Thanks Jakob.

A Paul · Accepted Answer · 2013-12-17 12:49:59Z

0

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)

answered Dec 17, 2013 at 12:49

A Paul

8,3113 gold badges33 silver badges65 bronze badges

Comments

Rahul · Accepted Answer · 2013-12-17 13:01:05Z

0

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.

answered Dec 17, 2013 at 13:01

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

1 Comment

Strawberry Over a year ago

either way, it's an inner join

Collectives™ on Stack Overflow

Improve performance mySQL query

4 Answers 4

1 Comment

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related