How to organize a search in 3 tables at once?

Question

Search engines

Good afternoon!
There are 3 tables: cars, planes, trains.
Each of them has a varchar field name.
There is a search string, the user enters the name into it, after the 3rd letter entered, the search should begin in all 3 tables, and the first 10 results with the best occurrence should be displayed. How can this be built using php + sql?

Answer 1

As an option, you can make a common transport table containing the name field for all modes of transport. And to connect your tables to the general through join as necessary.
In this case, the search will be much faster and easier. And only one table.

Answer 2

If pagination is not needed, then UNION queries should help. Otherwise, you will have to make a separate table purely for FULLTEXT with foreign keys.

Answer 3

There is no "best match" in the SQL standard. It is necessary to use text search of a specific DBMS.
Build a view to join tables.

Answer 4

If they have the same structure, then UNION all 3 tables, bringing them to the view, text search. More or less like this:
If there is Textsearch in the table type, you can also search for it