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
– Esty, Commented Sep 28, 2016 at 5:53
I replaced the incorrect sql-server tag with mysql because of the question's title — user330315
– user330315, Commented Sep 28, 2016 at 6:01
Keep in mind that would like to know your options and compare performance (use Sql Sever) — DarkFenix
– DarkFenix, Commented Sep 28, 2016 at 6:04

shA.t · Accepted Answer · 2016-09-28 06:49:18Z

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, 2016 at 6:49

answered Sep 28, 2016 at 6:08

shA.t

17k5 gold badges59 silver badges121 bronze badges

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

4 Comments

DarkFenix Over a year ago

this would make return duplicate records if both the value and a.lastname a.name are in the TABLE_B?

shA.t Over a year ago

Using UNION will remove duplicates !?

DarkFenix Over a year ago

It is possible with this solution proposed to eliminate duplicate records returned?

shA.t Over a year ago

How those records should eliminate? and if you have another question ask another question ;).

Shushil Bohara · Accepted Answer · 2016-09-28 06:43:12Z

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, 2016 at 6:43

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

1 Comment

shA.t Over a year ago

There is no difference between using EXISTS vs IN as a performance issue ;).

Esty · Accepted Answer · 2016-09-28 05:56:00Z

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, 2016 at 5:56

Esty

1,8923 gold badges17 silver badges36 bronze badges

1 Comment

DarkFenix Over a year ago

is the method I made, any other suggestions?

Collectives™ on Stack Overflow

Optimize a query with MySQL in repeated

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related