Some background - we're in the process of revamping the way that SQL Server Central's search and related content systems work. One part of this is that we need to perform fairly full extractions of the content in the database in order to index it. The version of the search that's currently running in development uses the following query to extract all the articles on the site:
We're about to add scripts into the search as well (previously these haven't been particularly well indexed, particularly by Google, so we're looking to improve that significantly). Scripts are also a subclass of ContentItem in the object structure, so are also stored in
asked Nov 13 '09 at 01:26 PM in Default
Melvyn Harbour 1 ♦♦
This looks like a matter where to get the best results in that particular case you will need to try it multiple ways and see which gives you the best results.
With that said, it seems like you could do it with two queries with a "Union ALL". UNION, which filters out duplicates tends to be slow, but UNION ALL is quite fast. I previously did some comparison testing and found that in some cases I got faster results using a UNION ALL with two queries than I did using an OR test in the Where clause.
Of course, I also did some other performance tests where the OR was faster depending on the table and the details of the query.
answered Nov 13 '09 at 02:27 PM