Problems with WHERE IN (many records)

Asked

Viewed 137 times

0

I need to assemble a query to search for records in table A, with table B record filter. Something like:

SELECT * FROM tabelaA tblA WHERE tblA.coluna IN (1,2,3,...)

The values contained in the "IN" filter come from table B and in some cases may be tens or even hundreds.

Doubts: Because it is an undefined number of values, this is the correct form? If not, what is the best approach to the case? When the number of values in the filter is too large, it can cause problems with bank performance?

mysql sql database

  • Take a look at [tour]. You can accept an answer if it solved your problem. You can vote on every post on the site as well. Did any help you more? You need something to be improved?

  • 1

    You’re right @bigown. I’m just running so fast that I didn’t have time to test any of the solutions presented. I will do this as soon as possible and I will point out the best solution. Thank you for reminding me!

3 answers

5

You may have performance problems, yes, but that also depends on other factors. I don’t know where the values you filter come from, but if they come from another table it might be better to use one JOIN (as in the answer) or a subquery:

SELECT * FROM tabelaA tblA 
WHERE tblA.coluna IN (SELECT id FROM tabelaB WHERE coluna = x)

4

Probably if you use one JOIN will get better performance. As there are no details I do not know if would solve your case, if not solve, then the IN may be the best solution even though I have not seen the query that fills him):

SELECT tblA.*, tblB.coluna FROM tabelaA tblA
    INNER JOIN tabelaB tblB ON (tblA.coluna = tblB.coluna)

I put in the Github for future reference.

Whether you’re going to have performance issues or not and which approach is best, just testing your case. Any general information for your specific case will be speculation, even if it is a reasoned estimate.

The existence of adequate indices may change more than the form of the query.

1

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.