CODEWITHSUNDEEP
mysqlsqldatabase-performance
Andrej
Andrej

Reputation: 3839

Select subset of records from very large SQL table

There are two tables, namely LongTable and ShortTable. For example, LongTable looks like:

personA | personB
1 | 2
1 | 3
2 | 4
2 | 5
3 | 4
3 | 5
3 | 6
4 | 5
4 | 6

and ShortTable has the following content:

PersonA
1
2

I wonder how to select records from a LongTable (please note that this table is really long, i.e. about 2.000.000 rows) based on records in the ShortTable. So, the result for this particular case should be:

personA | personB
1 | 2
1 | 3
2 | 4
2 | 5

I started with the following query (but with failure: "Can't reopen table: 'ShortTable'"):

SELECT * FROM LongTable
  WHERE
    personA IN (SELECT * FROM ShortTable)
    AND
    personB IN (SELECT * FROM ShortTable)

Code to reproduce temporary tables is given below.

Thanks in advance for any pointer.

CREATE TABLE LongTable (
  personA INT,
  personB INT
);

INSERT INTO LongTable VALUES
(1,2),(1,3),(2,4),
(2,5),(3,4),(3,5),
(3,6),(4,5),(4,6);

CREATE TABLE ShortTable (
  personA INT
);

INSERT INTO ShortTable VALUES
(1),(2);

Upvotes: 1

Views: 5146

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try this 

 SELECT DISTINCT l.personA, l.personB
 FROM longTable l 
 INNER JOIN ShortTable s ON l.personA  = s.personA

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269703

The fastest way to do this in MySQL is to create an index on longTable.personA. Then do the following query:

select l.personA, l.personB
from longTable l
where exists (select 1 from shortTable s where s.PersonA = l.PersonA limit 1)

Upvotes: 1

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

You can use this :

SELECT * FROM LongTable
WHERE personA IN (SELECT * FROM ShortTable)

Note that the running time usually depends on tables structure(e.g. indexes), rather than on query

Upvotes: 1

Related Questions