需要筛选结果优化查询

Question

我有一个名为Device:的表

        Device Table
id    deviceName     genId       description               overview 
1      PV motor       5       motor capacity  5        motor load is good
2      puf pannel     6       puf capacity  2          puf is for small load
3      gd motor       5       motor capacity 7         motor load is very good
4      rg motor       5       capacity is 3            low capacity

现在假设这个表有数千条记录，我需要添加对行的搜索，如genId=5 and description Like = '%motor%' OR Overview Like='%motor%'
搜索结果将是

1      PV motor       5       motor capacity  5        motor load is good
3      gd motor       5       motor capacity 7         motor load is very good

我需要构造查询，首先它从表中搜索genId 5，然后它搜索具有类似马达的文本的描述和概述。因为如果我的表有100条记录，而其中只有5条的genId设置为5，那么我的文本搜索查询将在这5行而不是100行上执行。

我的搜索查询：

Select * 
From Device 
where (genId=5) And (description Like '%motor%' Or overview Like '%motor%') 

有人能帮我创建优化查询吗？

Answer 1

您的查询已经尽可能优化。

LIKE本身并不慢；它慢的是从磁盘加载所有表行，LIKE通常需要这样做，因为它无法通过索引优化查找。

然而，当genId列上没有索引时，所有行都需要加载来检查这些值，因此插入额外的步骤将是不必要的：

> EXPLAIN QUERY PLAN Select * From Device where (genId=5) And (description Like '%motor%' Or overview Like '%motor%');
0|0|0|SCAN TABLE Device

另一方面，如果在genId列上有索引，则不需要进行任何手动优化，因为数据库将自动查找索引中匹配的genId行，然后仅在以下列上检查LIKE数学：

> CREATE INDEX genId_index ON Device(genId);
> EXPLAIN QUERY PLAN Select * From Device where (genId=5) And (description Like '%motor%' Or overview Like '%motor%');
0|0|0|SEARCH TABLE Device USING INDEX genId_index (genId=?)

Answer 2

在这种情况下，您可以使用子查询

Select * 
From (Select * From Device where (genId=5)) 
where description Like '%motor%' Or overview Like '%motor%'

这里，首先执行子查询，然后应用where条件。这就是我所知道的

Answer 3

为什么要分别搜索它们？只需将AND更改为OR，您的查询就会获取所有可能的场景。

SELECT * FROM 
   Device 
WHERE 
    genId = 5 
OR 
    description Like '%motor%' 
OR 
    overview Like '%motor%'; 

Answer 4

我认为最好不要使用OR运算符，减少使用Like运算符，如下所示：

SELECT *
FROM Device
WHERE (genId = 5)
  AND (1 = CASE WHEN description Like '%motor%' THEN 1
                WHEN overview Like '%motor%' THEN 1
                ELSE 0
           END)

或者这个

SELECT *
FROM Device
WHERE (genId = 5)
  AND (description || ':' || overview Like '%motor%')