SQL Search Query

I'm currently working on a query to search a specific table. The requirements say that I need to grab all records where any of a group of fields are like the input value. As such I have a query that looks similar to

SELECT * FROM MyTable
WHERE Col1 like @input
    or Col2 like @input
    or Col3 like @input

There could be any number of columns in the where clause. One particular customer is going to have 50 columns. As such, this where clause is going to get fairly expensive.

Is there another syntax or method that can be开发者_JAVA技巧 used so that I could say "does the input value exist in any of x columns?"

Would you write the query differently?

---

Depending on which and where wildcards are allowed these queries could be expensive indeed, because the optimizer will have trouble using indexes on many of them to speed them up.

If you are running this on MS SQL Server you might consider using the Fulltext searching features and the CONTAINS keyword to implement this.

SELECT *
FROM MyTable
WHERE CONTAINS
((col1,col2,col3) ,@input)

Or to search all text-indexed columns

SELECT *
FROM MyTable
WHERE CONTAINS
(* ,@input)

---

I'd structure the data differently. Are the types of these 50 columns all going to be the same? Since you're using "like", I'm going to assume they are all varchar columns. Why not have 3 tables, 'MyTable', a table with the string data (whatever it may be), and a join table with references to both.

At this point, your query becomes:

select mt.*
from MyTable mt
    inner join JoinTable jt
        on jt.MyTableId = mt.Id
    inner join DataTable dt
        on dt.Id = jt.DataTableId
where dt.Data like @Input

Obviously, on SO, we see a lot of answers that are simply, "Your data is structured wrong. Redo it." This is not always the most helpful. If for some reason (bureaucracy around database structure, legacy dependencies on this structure, etc.) you cannot restructure it, you need to realize you're pivoting these columns. At this point you'll need to look at options for pivoting this, which will vary based on how analytical/transactional (read/write heavy) this db is. If you are very read heavy, you might be ok duplicating the data into a pivot table that you update each time you update 'MyTable'. If you're more transactional, a function or view will at least abstract this pivoted structure but the query will be expensive. MS SQL full text search capabilities would work similarly to duplicating the data and updating on inserts and depending on the nature of your "like" queries, may read faster.

---

If you don't need live data, you can opt for a data-warehouse scenario.

• every day, drop and reload a table containing only an ID and VALUE column
• use a SELECT INTO like

   SELECT ID, Col1 FROM MyTable  
   UNION ALL SELECT ID, Col2 FROM MyTable  
   UNION ALL SELECT ID, Col3 FROM MyTable

• Index the table from front to back, left to right, ...

This allows you to simplify your original select and keep performance on par

SELECT mt.*
FROM   MyTable mt
       INNER JOIN MyDWTable mdwt ON mdwt.ID = mt.ID
WHERE  mdwt.Value LIKE @Input