Hi,

We have some problem with selecting data from table, which has many records
and we must to do it by clause where Field1 containing 'AAA' and field1
containing 'BBB' and ... Field1 is varchar(100), we can assume that it
containing only upper characters.
We have similar databases in many locations, and it woks fine, but in two of
them, we have some troubles. The differences is in metod of serching some
information. In 'good' localizations there is main kind of searching by
'starting with'. Load average on Linux server is beetween 0,8 to 2,0. In
'bad' localizations load average is between 2,0 to 8,0.
About 100 users selecting data from about 10 tables with approximatly
30000-100000 records per table.
In peak hours it is not possible to work.
We have some solution. We,ve made extra table which holds rekords:
ExtraTable
item, word1 from field1
item, word2 from field1
...
...
item, word_last from field1
table has to indexes by Item and Word

then we can select as follow

SELECT M.Item, M.field1, something else....

FROM Table M
INNER JOIN ExtraTable A ON (M.Item=A.Item AND A.Word STARTING WITH 'AAA')
INNER JOIN ExtraTable B ON (M.Item=B.Item AND B.Word STARTING WITH 'BBB')
INNER JOIN ExtraTable C ON (M.Item=C.Item AND C.Word STARTING WITH 'CCC')
...
...
ORDER BY 2

This solution works better, but not so good like 'good' solution and assumes
that user everytime input fraze which is starting fraze for each word. I
think that it covers about 80% needs. May be solution is in splitting words
into smaler parts ?



Regards,
Jacek Borowski