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