General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>Hi all,
>I use a query that contain the condition where fieldname like "%searchitem%" in one of my apps to query tables with large numbers of records. Is that a way to optimize the query via Rushmore (ie indexing?)
>Thanks
>Yau
I had a thread going several months ago on this (I think it was titled "Google like queries" or something like that). What I did was:
1. Decide what column I wanted to "substring search"
2. Create a table with reference-ID and string
3. Write a routine to scan the original table, split the original column into words, and write each alltrim(word) to the new table.
So if the original table had an ID column of "ABC" and a data column of "steve craft", the reference table would have two rows:
"ABC" "steve"
"ABC" "craft"
Then change your SQL from
Select * from origtable where name like '%craft%'
to
Select ref-ID from reftable where string = 'craft' && returns "ABC"
Select * from origtable where ID=="ABC" && returns everything you want
So now you get the full benefit from Rushmore's reading left-to-right. This is much, much, much faster than doing essentially a table scan.
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only