Search Box - Selecting Data from Multiple Tables

Question

In my application, I want to create a "universal search" box that will allow the users to perform a general search on any of the 'informational' data within the database. The system happens to be an account management system, so ideally they'd be able to do searches for e-mail addresses, usernames, ID's, etc.

I've been searching around the web for a solution but I haven't come to a conclusion yet so I figured I'd ask the question on SO.

What's the best way to perform a 'search' query on the database and return potential results from multiple tables?

My initial thought was to perform a SELECT query on each individual table using a wildcard for each 'searchable' column. Would this be a correct approach?

Answer 1

I would use a special search engine for such kind of "universal search". For example, Sphinx, free open source SQL full-text search engine.

A SELECT query on each table will cause very low performance if the database is large enough.