Skip to content
Advertisement

Alternative way to run a query with join

I have the below query:

select m.name, m.surname,m.teacher,
c.classroom,c.floor
from table1 as m 
inner join table2 as c
on (m.name=c.name or m.surname = c.surname);

But it takes a lot of time to execute the query. I want to ask if there is any other alternative way of running it.

Also the rows that query tried to fetched were ~47b, before I stopped it.

This result is reasonably if the query did cross join not inner join as it does.

Each table has 14m rows.

Advertisement

Answer

Running a join query with OR operator causes none-optimal execution plan for DBMS.

Try this:

select m.name, m.surname,m.teacher,c.classroom,c.floor 
from table1 m inner join table2 as c 
on m.name = c.name 
UNION
select m.name  , m.surname,m.teacher,c.classroom,c.floor
from table1 m inner join table2 as c 
on m.surname = c.surname 

Consider that you must declare indexes on name and surname on both tables

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement