June 1, 2011 at 4:03 am
With a table that large, do you have any partitioning on it?
June 1, 2011 at 9:31 am
I'm not sure I understand the question completely. Could you please give us a (small) example of the data, and a sample query?
If you are saying you want to search using a LIKE '%STRING%' comparison, it is going to produce a table scan. Sorry.
If you are looking for certain keywords out of string of words, you might consider full text indexing.
Otherwise, you could write a query to manufacture another table that contained your primary tables's key and each of the possible character combinations and index that table. Something like this.
Source
---------------------------------------------
Paradimethylaminobenzaldehayde
New table
----------------------------------------------
Paradimethylaminobenzaldehayde
aradimethylaminobenzaldehayde
radimethylaminobenzaldehayde
adimethylaminobenzaldehayde
dimethylaminobenzaldehayde
imethylaminobenzaldehayde
methylaminobenzaldehayde
etc etc etc
This would allow you to index the built table and do LIKE 'STRING%' searches that would take advantage of the index (unlike '%STRING%'). Given the size of your table, creating and maintaining this table might prove impractical, but if your data is fairly static and you have the disk space available, it might be doable.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply