Need filtered result optimised query

Question

I have one table called 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

Now suppose this table has thousands of records , i need to add searching of rows like , genId=5 and description Like = '%motor%' OR Overview Like='%motor%'
Search result will be

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

I need to construct query which first it search for genId 5 from the table , and after that it search for the description and overview having text like motor. Since if my table had 100 records and only 5 of them have their genId set to 5 then my text search query will be executed on those 5 rows instead of 100 rows .

My Search query :

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

Can any one help me to create optimized query?

Why do you think that searching in two steps, with a separate temporary table, would be faster? — CL., Jun 30 '15 at 19:18
@CL. :Like is a heavy operation,and here it may search in 300 - 1000 words on average. So if i filter first from 1000 rows to 20 rows (By genId), then Like query will executed in only 20 rows instead of all 1000 rows, correct me if i am wrong . — Amit Kumar Kannaujiya, Jul 01 '15 at 05:19

score 0 · Answer 1 · edited Jun 30 '15 at 12:00

0

In this case you can go for sub-query

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

Here, first the subquery will be executed first and then the where condition will be applied. This what I know

edited Jun 30 '15 at 12:00

shA.t

16,580
5
54
111

answered Jun 30 '15 at 11:33

Anirudh Jadhav

967
1
9
23

will it be faster the my query ? – Amit Kumar Kannaujiya Jun 30 '15 at 16:05
SQLite will do [subquery flattening](http://www.sqlite.org/optoverview.html#flattening). – CL. Jun 30 '15 at 19:18

Ali · Answer 2 · 2015-06-30T12:24:23.050

0

Why to search them separately then? just change the AND to OR and your query will grab all the possible scenarios.

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

edited Jun 30 '15 at 12:24

answered Jun 30 '15 at 12:16

Ali

2,993
3
19
42

i think it will not reduce cost, query as of now my database does not have much of the records , but in future it may have . – Amit Kumar Kannaujiya Jun 30 '15 at 16:02
But the criteria you explained in your question requires searching on those 3 fields regardless. right? or is there something I'm missing? – Ali Jun 30 '15 at 16:06
Criteria is based on 3 fields, You got it right. Like command is a heavy operations .So i was looking for some optimized solution . Anyway i was using same query as of now . – Amit Kumar Kannaujiya Jun 30 '15 at 16:13
Great then pls don't forget to accept the right answer ;) – Ali Jun 30 '15 at 16:15
P.s. Totally right about 'LIKE' being costly not unless you can change your criteria I'm afraid noting else you can do – Ali Jun 30 '15 at 16:17

score 0 · Accepted Answer · answered Jul 01 '15 at 17:01

Your query already is as optimized as possible.

LIKE itself is not slow; what it slow is loading all table rows from disk, and LIKE usualy needs to do that because it cannot optimize its lookups with an index.

However, when there is no index on the genId column, all rows need to be loaded anyway to check those values, so inserting an extra step would be needless effort:

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

If, on the other hand, there is an index on the genId column, then you don't need to do any manual optimizations, because the database will automatically look up the matching genId rows in the index, and then check for LIKE mathes only on those:

> 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=?)

score -1 · Answer 4 · answered Jun 30 '15 at 12:06

-1

I think it's good to don't use OR operator and reduce using of Like operator like this:

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

or this

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

answered Jun 30 '15 at 12:06

shA.t

16,580
5
54
111

I like `description || ':' || overview Like '%motor%'` syntax, so thanks for sharing it, but are you sure its reducing the query's cost as compare to the other syntax? i think both syntax eventually searching for alike value in those two fields – Ali Jun 30 '15 at 12:40

Need filtered result optimised query

4 Answers4