Best way to search a database

[Krizo]

I have a database with some tables and i want to search for a term in all of those tables.
What is the best (fastest) way to do this?

[devinemke]

if you wish to search every field of every table then i suggest creating a 2 dimensional array with table names as keys and arrays of fields as values then loop thru each table and do a SELECT query on each field.

[Sxooter]

If you're gonna have a lot of data to slog through, look at a full text search engine for your database. Both PostgreSQL and MySQL have them (PostgreSQL has a couple, tsearch 2 is generally the best for it.)

[Roger_Ramjet]

A slick little trick is to use a UNION query across all the tables with the search string in the where clause of each component query

"SELECT 'tbl1' AS table_code, id, descript FROM tbl1 WHERE descript LIKE '%$search_string%'
UNION
SELECT 'tbl2' AS table_code, id, descript FROM tbl2 WHERE descript LIKE '%$search_string%'
UNION
SELECT 'tbl3' AS table_code, id, descript FROM tbl3 WHERE descript LIKE '%$search_string%'
UNION
SELECT 'tbl4' AS table_code, id, descript FROM tbl4 WHERE descript LIKE '%$search_string%'"

You'll see I've added a column called table_code to each query. This is to identify which table each result was found in. All the usual restrictions for UNION queries apply.

Don't know how this would perform if the component queries were doing match with full text searches. Probably take till next xmas.