CODEWITHSUNDEEP
sqlpostgresql
user2160919
user2160919

Reputation: 183

How do I merge the following SQL queries for performance?

I have two SQL queries:

  1. Below query returns records in thousands(13000) and growing.

    SELECT distinct city FROM users;

  2. The result of this above query becomes a parameter for next SQL query which is:

    SELECT CAST(users.lat as VARCHAR) AS latitude, 
       CAST(users.lng as VARCHAR) AS longitude,
        users.city as city,
        users.state as state 
FROM users users 
WHERE users .city='';

I have merged these two queries by making the first query a sub-query using IN clause as in:

SELECT CAST(users.lat as VARCHAR) AS latitude, 
       CAST(users.lng as VARCHAR) AS longitude,
       users.city as city, 
       users.state as state 
FROM users users 
WHERE users.city IN (SELECT distinct us.city FROM users us);

Need to know if this can be optimized further.

DDL:

CREATE TABLE users
(
  id uuid NOT NULL,
  language_id integer NOT NULL,
  lat numeric NOT NULL,
  lng numeric NOT NULL,
  state character varying,
  city character varying,
  CONSTRAINT users_pkey PRIMARY KEY (id)
);

Upvotes: 1

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

First comment. You do not need distinct when using in. So this where clause suffices:

WHERE users.city IN (SELECT us.city FROM users us);

Second, it is very misleading to use a different table name as an alias for another table. Instead:

SELECT CAST(fru.lat as VARCHAR) AS latitude, 
       CAST(fru.lng as VARCHAR) AS longitude,
       fru.city as city, 
       fru.state as state 
FROM farmrise.users fru 
WHERE fru.city IN (SELECT u.city FROM users u);

Most databases will generate a good query plan for this. Personally, I would be inclined to write this as;

FROM farmrise.users fru 
WHERE EXISTS (SELECT 1 FROM users u WHERE u.city = fru.city);

This will definitely take advantage of an index on users(city).

Finally, in most databases you want a length for the VARCHAR in the CAST(). In fact, you should have a length in any database other than MySQL and derivative databases.

Upvotes: 3

Related Questions