GeoNames Forum

Hello,
I have imported the geonames data in a oracle database. That is working fine so far.

But now my problem is when I search certain entries with my select statement.

To get all rows of (in this example: London) my select statement looks like this:

select * from TABLE1 where NAME LIKE '% London' OR NAME LIKE 'London %' OR NAME LIKE '% London %' OR NAME = 'London'

I created an index for the name column:
create index my_name_idx on TABLE1(NAME);

But the problem is, that the index is not used when I search with Like

Therefore when I start a select statement like this one here, it took very long until I get my result.

Are there any possibilities to make my search a bit faster. Maybe are there other indexes which can be used with like.

Thanks for the help.

Regards

You should look at Full-text search indexes. I have no idea if oracle offer such a thing - but would be very surprised if they dont.

If not look at something like sphinxsearch.com - works great with geonames data. Shouldnt be too difficult to hook up to a oracal database, either using odbc,
http://sphinxsearch.com/docs/current.html#conf-odbc-dsn
or xmlpipe

select * from TABLE1 where NAME LIKE '% London' OR NAME LIKE 'London %' OR NAME LIKE '% London %' OR NAME = 'London'

Hi,

That's a killing query when you have a lot of rows! When you use the % sign in front of the search keyword. It scans the whole table (without index) to get the match. Can be anything from 1 to 9 and from a to z.

Can get faster with more (hardware)resources.

So re-define your query and get try it without the % before the keyword. Must be faster.

Thanks,
Ron Sahertian

Hi,
thanks for the answers.

I solved it. The solution is called Oracle Text Index.

Instead of using like you have to work with contains then. With this the sql is much faster.

Regards