Optimize a query with MySQL in repeated

Question

I have the following query that checks if two columns of a table are in another The query works, I hope you can optimize the call in because they are equal. I doubt if there is a performance penalty because two calls are made to the same query

SELECT name,lastname FROM TABLA_A
WHERE 
name IN (
  SELECT name FROM TABLE_B
)
OR
lastname IN (
  SELECT lastname FROM TABLE_B
)

ANOTHER WAY

SELECT a.name,a.lastname
FROM TABLE_A as a
join TABLE_B  as b on a.name=b.name
or a.lastname= b.lastname

try to join, is a valid option? there are other ways to make this task more efficient? thank you very much

Please synchronize two queries. It is hard to understand the columns from two query. If they return same result edit them with same column name. — Esty, Sep 28 '16 at 05:53
I replaced the incorrect `sql-server` tag with `mysql` because of the question's title — , Sep 28 '16 at 06:01
Keep in mind that would like to know your options and compare performance (use Sql Sever) — DarkFenix, Sep 28 '16 at 06:04
First query returns not duplicate rows, second may (depending on data). — jarlh, Sep 28 '16 at 08:03

shA.t · Accepted Answer · 2016-09-28T06:49:18.960

1

There is a performance issue in using OR that you can use UNION instead like this:

SELECT name, lastname 
FROM TABLE_A
WHERE name IN (
  SELECT name 
  FROM TABLE_B)
UNION -- If there is not any duplicate use `UNION ALL` instead
SELECT name, lastname 
FROM TABLE_A
WHERE lastname IN (
  SELECT lastname 
  FROM TABLE_B);

[SQL Fiddle Demo]

edited Sep 28 '16 at 06:49

answered Sep 28 '16 at 06:08

shA.t

16,580
5
54
111

this would make return duplicate records if both the value and `a.lastname` `a.name` are in the TABLE_B? – DarkFenix Sep 28 '16 at 06:36
Using `UNION` will remove duplicates !? – shA.t Sep 28 '16 at 06:37
It is possible with this solution proposed to eliminate duplicate records returned? – DarkFenix Sep 28 '16 at 06:39
How those records should eliminate? and if you have another question ask another question ;). – shA.t Sep 28 '16 at 11:23

score 1 · Answer 2 · answered Sep 28 '16 at 06:43

1

You can use EXISTS which works more efficiently than IN from every aspects:

SELECT a.name,
    a.lastname
FROM TABLE_A as a
WHERE EXISTS (SELECT b.name 
             FROM TABLE_B b WHERE a.name = b.name OR a.lastname= b.lastname)

answered Sep 28 '16 at 06:43

Shushil Bohara

5,556
2
15
32

[There is no difference between using `EXISTS` vs `IN` as a performance issue](http://stackoverflow.com/a/3964770/4519059) ;). – shA.t Sep 29 '16 at 05:35

score 0 · Answer 3 · answered Sep 28 '16 at 05:56

0

As you have already done

SELECT a.name, a.lastname
FROM TABLE_A as a
INNER JOIN TABLE_B as b on a.name=b.name OR a.lastname= b.lastname

answered Sep 28 '16 at 05:56

Esty

1,882
3
17
36

is the method I made, any other suggestions? – DarkFenix Sep 28 '16 at 06:20

Optimize a query with MySQL in repeated

3 Answers3