Text search across multiple tables using MySQL

query.sql

select
  first_name,
  last_name
from
  users
left join
  companies on companies.id = users.company_id
where (
    companies.name like 'TERM%' or
    first_name like 'TERM%' or
    last_name like 'TERM%'
)

sample_data.md

Find the sample data here:

https://gist.github.com/reinink/30210fd48ef0435f475ed9d13270b09e

schema.txt

Companies

+------------+-----------------+------+-----+---------+----------------+
| Field      | Type            | Null | Key | Default | Extra          |
+------------+-----------------+------+-----+---------+----------------+
| id         | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)    | NO   | MUL | NULL    |                |
+------------+-----------------+------+-----+---------+----------------+

+-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| companies |          0 | PRIMARY              |            1 | id          | A         |       10106 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| companies |          1 | companies_name_index |            1 | name        | A         |        8624 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Users

+------------+-----------------+------+-----+---------+----------------+
| Field      | Type            | Null | Key | Default | Extra          |
+------------+-----------------+------+-----+---------+----------------+
| id         | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| company_id | bigint unsigned | NO   | MUL | NULL    |                |
| first_name | varchar(255)    | NO   | MUL | NULL    |                |
| last_name  | varchar(255)    | NO   | MUL | NULL    |                |
+------------+-----------------+------+-----+---------+----------------+

+-------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name                 | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users |          0 | PRIMARY                  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | users_company_id_foreign |            1 | company_id  | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | users_first_name_index   |            1 | first_name  | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | users_last_name_index    |            1 | last_name   | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+