mysql select count rows from a table where column like values from another table

Question

I'll try to better explain the title with an example

table 1 example

Id  text
1   lorem ipsum doe
2   foo bar lorem ipsum jhon
3   bla bla ipsum tommy

table 2 example

Id  fullname  name  surname  keyword
1   jhon doe  jhon  doe      jhon
2   tom asd   tom   asd      tom
3   sam frf   sam   frr      sam

expected table result using like or regexp?

fullname  count(*)
jhon doe  2
tom asd   1
sam frf   0

many thanks!

Answer 1

The easiest was is to use REGEXP.

SELECT fullname, 
       Count(t1.id) 
FROM   table1 t1 
       RIGHT JOIN table2 t2 
               ON t1.text REGEXP t2.keyword 
GROUP  BY fullname 

DEMO

I used an RIGHT join so that you'd get the Zero for sam (otherwise it would just be eliminated)

Answer 2

some perf test with my real data

t1 => 100,000 rows and growing

t2 => 207 rows

test 1

SELECT 
    t2.fullname,
    count(t1.id) AS total
FROM
    table_1 AS t1
        RIGHT JOIN
    table_2 AS t2 ON t1.text REGEXP t2.keyword
GROUP BY t2.fullname
ORDER BY total DESC

212 seconds

test 2

SELECT 
    t2.fullname,
    count(t1.id) AS total
FROM
    table_1 AS t1
        RIGHT JOIN
    table_2 AS t2 ON t1.text LIKE CONCAT('%', t2.keyword, '%')
GROUP BY t2.fullname
ORDER BY total DESC

30 seconds

test 3

SELECT 
    t2.fullname,
    count(t1.id) AS total
FROM
    table_1 AS t1
        RIGHT JOIN
    table_2 AS t2 ON t1.text LIKE lower(CONCAT('%', t2.name, '%')) AND t1.text LIKE lower(CONCAT('%', t2.surname, '%'))
GROUP BY t2.fullname
ORDER BY total DESC

32 seconds

test 4

SELECT 
    t2.fullname,
    count(t1.id) AS total
FROM
    table_1 AS t1
        RIGHT JOIN
    table_2 AS t2 ON t1.text LIKE lower(CONCAT('%', t2.name, '%')) OR t1.text LIKE lower(CONCAT('%', t2.surname, '%'))
GROUP BY t2.fullname
ORDER BY total DESC

40 seconds

test 5

SELECT 
    t2.fullname,
    count(t1.id) as total
FROM
    table_1 as t1
        RIGHT JOIN
    table_2 as t2 ON t1.text LIKE CONCAT('%', t2.keyword, '%') OR (t1.text LIKE lower(CONCAT('%', t2.name, '%')) AND t1.text LIKE lower(CONCAT('%', t2.surname, '%')))
GROUP BY t2.fullname
ORDER BY total DESC

41 seconds

I'll pick test 5. Best compromise results/perf

Any further advice?

Thanks again for your help!