> On 16 Jan 2018, at 17:32, hmidi slim <hmidi.slim2@gmail.com> wrote:
>
> 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
soon.
> 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
Ineed 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'))
That query is equivalent to:
select g.name, t.city
from geoname as g, test_table as t
where false or lower(g.name) = lower(t.city || 'city'));
So those are probably not the results you want.
At the very least, if you're lower-casing column contents, don't compare those to an upper-cased constant ;)
Also, AND has precedence over OR, which is the other reason why my equivalent query is so much shorter.
And finally, LIKE is equivalent to = (equals) without any wildcards.
> 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
whichcontains the country_code and make an inner join with the geoname table or should I use indexs to accelerate the
process?
Some indices on lower(g.name) and lower(t.city) from your query would be useful, but in that case make sure you take
theconcatenation of 'city' out of the lower()-call in your query.
Just reading your latest addition - using lower() on constants is just a waste of cycles. It won't hurt your query much
though.
Regards,
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.