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