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       |
+-------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

@reinink - interested in the course - the above is very close to an issue I want to solve - so wondering if the course will cover it - querying unrelated data at the same time and returning not just the ID of the model, but the table reference it is derived from, in the return?

The purpose is to allow users to just have one search box to find any one model in the search. So say you have 4 tables - COMPANY, SERVICE, PRODUCT, and DEVICE. I want to design a query that queries for a typeahead "meta" search of the description or name (or some other column) in each table, and returns the ID and the model type (i.e. table reference) so that if someone selected that, it would take them to that particular record. Right now these are 4 different searches on my application - in other words, the person needs to know they are looking for a DEVICE, and then search the DEVICE table.

I think this can be done with Unions - just including an AS column (from an SO post):

(SELECT content, title, 'msg' as type FROM messages WHERE content LIKE '%" . 
$keyword . "%' OR title LIKE '%" . $keyword ."%') 
UNION
(SELECT content, title, 'topic' as type FROM topics WHERE content LIKE '%" . 
$keyword . "%' OR title LIKE '%" . $keyword ."%') 
UNION
(SELECT content, title, 'comment' as type FROM comments WHERE content LIKE '%" . 
$keyword . "%' OR title LIKE '%" . $keyword ."%')";

In my case the tables have different columns in each table, so instead of say content, title above, say the table has description, name in one of the tables. As per your post above, however, I suspect this is not performant at all . . . and thus have not implemented it.

In any event, I am on your email list and very interested in this course and really appreciate the above gist and all the comments on it.