For a quick and dirty solution - not 100% accurate due to some full-text search specifics but will get most of the matches - add a FULLTEXT
key to A
and then use a join with be with MATCH AGAINST
as the join condition. Since MATCH AGAINST
cannot use a a non-constant argument, you will have to simulate the join with a cursor in a stored procedure. Below is a fully-functional tested example:
create table a (id int not null primary key,
company_name text, fulltext key(company_name)) engine=myisam;
create table b (id int not null primary key, company_name1 text);
insert into a values(1,'dog kitten'),(2,'spoon fork'),
(3,'fish crab'),(4,'dog mouse'),(5,'noise mouse'),
(6,'kitten dog'),(7,'noise one'),(8,'noise two'),
(9,'noise three'),(10,'noise four'),(11,'noise five');
insert into b values(1,'dog mouse kitten'),
(2,'spoon knife fork'),(3,'fish sea crab');
drop procedure if exists ft_match;
delimiter //
create procedure ft_match()
language sql
deterministic
sql security definer
begin
declare v_id int;
declare v_company_name1 text;
declare v_finished int;
declare c cursor for select * from b;
declare continue handler for not found set v_finished=1;
delete from results;
open c;
c_loop: loop
fetch c into v_id,v_company_name1;
if v_finished then
leave c_loop;
end if;
insert into results select v_id,v_company_name1,a.id,a.company_name
from a where match (a.company_name) against (v_company_name1 in boolean mode);
end loop c_loop;
close c;
select * from results;
end//
delimiter ;
create table results (a_id int not null, a_company_name text,
b_id int not null, b_company_name text);
call ft_match();
More info on full text keys in the MySQL manual at https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html
This is suitable for relatively small tables and in circumstances where high latency is acceptable. For better performance on large datasets and perfect accuracy, you will need to implement some form of external full text indexing.