Re: OPtimize the performance of a query

Sorry I forget the lower command when I wrote the code, it is like this:
lower(g.country_code) like lower('US')
(lower(g.feature_class) like lowwer('P') or lower(g.feature_class) like
lower('L'))

2018-01-16 17:40 GMT+01:00 Martin Moore <martin(dot)moore(at)avbrief(dot)com>:

>
>
> >Hi,
>
> >I have two tables in the same database: geoname and test_table.
>
> >The geoname table contains many columns which are: name, feature_class,
> feature_code, admin1, admin2,admin3, name and so on.
>
> >The second table 'test_table' contains only the columns: city, state.
>
> >There is no join between the two tables and I want to make a match
> between the data contained in each of them because I need the result for a
> farther process.
>
> >I wrote this query:
>
> >select g.name, t.city
>
> >from geoname as g, test_table as t
>
> >where (lower(g.feature_class) like 'P' or lower(g.feature_class) like 'L')
>
> >and lower(g.country_code) like 'US'
>
> >and lower(g.admin1) like lower(t.state)
>
> >and (lower(g.name) like lower(t.city) or lower(g.name) like lower(t.city
> || 'city'))
>
> >The table geoname contains 370260 rows and the table test_table contains
> 10270 rows.
>
> >The query took a long time to accomplish more than half an hour.Should I
> add another column in the table test_table which contains the country_code
> and make an inner join with the geoname table or >should I use indexs to
> accelerate the process?
>
>
>
> Indexes are your friends ☺
>
>
>
> I’d certainly add indexes on lower(g.feature_class, g.country_code) and
> lower(t.state)
>
>
>
> Note “and lower(g.country_code) like 'US'” will not return any results as
> you’ve got the ‘US’ in UPPER…. Same as (lower(g.feature_class) like 'P' or
> lower(g.feature_class) like 'L')
>
>
>
> Why are you using LIKE? Equals (=) is surely correct and probably faster?
>
>
>
>
>
> Martin.
>