MySQL: How to search multiple tables for a string existing in any column

Question

How can I search for in table_a table_b table_c, which have a random number of columns for a string?

I know this is not proper sql but it would be something like:

SELECT * FROM users, accounts, something_else WHERE ->ANY COLUMN CONTAINS 'this_string'<-

Ty in advance for SO community

Forename

Refer this: winashwin.wordpress.com/2012/08/28/mysql-search – Loco

Answer 1

Add fulltext indexes to all of the string columns in all of those tables, then union the results

select * from table1 where match(col1, col2, col3) against ('some string')
union all
select * from table2 where match(col1, col2) against ('some string')
union all
select * from table3 where match(col1, col2, col3, col4) against ('some string')
...

Commandment

@kiu Very cool - Been looking for this answer for a while now. Just need to know how to ORDER the results by the number of times the string occurs. In my case, I am doing a boolean search in multiple fields from multiple tables. Just need to know how to get the results ordered by relevancy. – Organdy