Re: Is Oracle better then MySQL for string searches?

>>>>> "Seth" == Seth Brundle <brundlefly76_at_hotmail.com> writes:

 Seth> I have created a 1.2M record database on MySQL, which has 3-4
 Seth> text fields I need to do searches on - I need to match not only
 Seth> whole words, but *parts* of words, this is very important:

 Seth> select * from emails where subject LIKE "%keyword%" and from  Seth> LIKE "%keyword%" and to LIKE "%keyword%"

 Seth> (ps the email table is just an example - its not what Im using  Seth> it for)

 Seth> Anyways, I need to these queries to perform fast enough for a
 Seth> web page, but they take about 90 seconds. Unfortunately, MySQL
 Seth> cannot use any type of index to help with searching for terms
 Seth> which have wildcards on both sides of the term.

 Seth> (closest it comes is the match...against, which allows wildcard  Seth> on the right-hand side only)

 Seth> Anyways, I was wondering if Oracle Linux would be the solution
 Seth> to my problem - can it index this type of query? Could I expect
 Seth> <5s quereies on a 1GB 1.7Ghz machine?

Using standard Oracle, you have the same limitation - I expect you would have the same limitation with any dbms. If you think about it, if you have a value which starts with a wildcard (e.g. %somestring%), where in the index would you start and where would you stop - you can't determine this, so most dbms will just default to a full table scan and ignore the index (unles there is perhaps another column which is indexed and does not have a leading wildcard).

Having said that, Oracle 8i and better comes with the "intermedia" package which has a lot of support for searching out strings etc. I think, but am not sure, that it does its own indexing and would possibly provide a way of speeding up your queries. You can find out more about it at

http://docs.oracle.com
http://tahiti.oracle.com (search engine)

You need to register to access these sites, but registration is free.

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!