Problems with WHERE IN (many records)

Question

Problems with WHERE IN (many records)

Navigation

#1 by (4 votes)
#2 by (3 votes)
#3 by (1 votes)

0

I need to mount a query to fetch records in table A, with records filter from table B. 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.

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

mysql sql

asked by anonymous 10.03.2015 / 13:32

3 answers

How to count page views on a site How to pass a value to the next page with PHP

score 4 · Answer 1

You may have performance problems yes, but this also depends on other factors. I do not know where the values that you filter come from, but if they come from another table it might be best to use JOIN ( like in response ) or a subquery:

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

score 3 · Answer 2

Probably if you use JOIN you'll get better performance. Since there are no details I do not know if it would solve your case, if it does not solve, then IN may be the best solution even though I did not see the query that fills it):

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

Whether you're going to have performance problems 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 suitable indexes can change more than the query form.

score 1 · Answer 3

I suggest using exists, select in will capture "all" data from the child query allocate in memory and respond to parent ... Using exists will return only the first valid record proving existence ...

link