the efficient query for products have two keywords-CodePudding

Suppose I have the following table

ProductValue(ID,ProductID,Value)

I want to get the top 100 products that exist records with values containing "one" and "two"

I wrote the following queries but I want to find a more efficient query

Select Top 100 ProductID From (
SELECT [ProductID]
  FROM [ProductValue]
  where [Value] like '%One%'
  intersect
SELECT [ProductID]
  FROM [ProductValue]
  where [Value] like '%Two%') g

the following query is more efficient

 Select Top 100 ProductID From [ProductValue] Where 
    ProductID in ( Select ProductID From [ProductValue] Where [Value] like '%One%' ) 
    and ProductID in ( Select ProductID From [ProductValue] Where [Value] like '%Two%' )
    group by ProductID

Which query is more optimal than the queries above?

CodePudding user response：

I rather doubt that the second query is more efficient than the first one. And I don't know whether the follwing queries are going to be more efficient, either. They are just two more options:

select top(100) [ProductID]
from [ProductValue]
where [Value] like '%One%' or [Value] like '%Two%'
group by [ProductID]
having max(case when [Value] like '%One%' then 1 else 0 end) = 1
   and max(case when [Value] like '%Two%' then 1 else 0 end) = 1;

The where clause is not really needed, but may speeed up the query.

If there exists a product table, as the column name [ProductID] promises, I'd probably go with

select top(100) [ProductID]
from [Product]
where [ProductID] in (select [ProductID] from [ProductValue] where [Value] like '%One%')
  and [ProductID] in (select [ProductID] from [ProductValue] where [Value] like '%Two%');

This last query would be the best option, if it were not about searching with LIKE '%something%' which does not benefit from an index. In another case where we might be looking for exact matches of 'One' and 'Two' it could loop through the products, quickly look up the values without aggregation and stop at the 100th match.