CODEWITHSUNDEEP
mysqlsql
Shane
Shane

Reputation: 4983

How do I speed up a sql query searching from two tables?

Here's an example, so I have table user and table city, they're like this:

user columns are (user_id, city_id, timestamp) [user_id and city_id are unique]

city columns are (city_name, city_id) [city_id is unique]

And I want to get user count in a given date from certain city, so basically I did it like this: 

select city_id, city_name, 
    (select count(user.user_id) 
     from user, city 
     where DATE_FORMAT(user.timestamp, '%Y-%m-%d') = '2017-03-07' 
     and user.city_id = ct.city_id) as user_count
from city ct 
where (city_id = 20 or city_id = 30)

And Result:

city_id, city_name, user_count
20       New York   100
30       LA         200

Then I realized this is way slower than searching directly for 

select count(user.user_id) 
from user, city 
where DATE_FORMAT(user.timestamp, '%Y-%m-%d') = '2017-03-07' 
    and user.city_id = 20

Why is this? Isn't ct.city_id in the original search already being set as either 20 or 30? How should I optimize the search and get the result in the table format I want?

Upvotes: 1

Views: 79

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133380

You could improve your query avoiding subselect and using an inner join and group by

select city_id, city_name,    count(user.user_id) 
from user
inner join city on user.city_id = city.city_id
where DATE_FORMAT(user.timestamp, '%Y-%m-%d') = '2017-03-07' 
and city_id in (city_id = 20 or city_id = 30)
group by city_id, city_name

Upvotes: 6

jfatal
jfatal

Reputation: 253

Try this:

select city_id, city_name, count(user.user_id) as user_count
from city ct 
inner join user on user.city_id = ct.city_id
where (ct.city_id = 20 or ct.city_id = 30)
AND DATE_FORMAT(user.timestamp, '%Y-%m-%d') = '2017-03-07'

Upvotes: 0

SqlZim
SqlZim

Reputation: 38033

I am would assume that MySQL is choosing to materialize your derived table into an internal temporary table in the first query, and does not make that choice with your second query.

For derived tables (subqueries in the FROM clause), the optimizer has these choices:

  • Merge the derived table into the outer query block
  • Materialize the derived table to an internal temporary table

source: Mysql Documentation - 8.2.2 Optimizing Subqueries, Derived Tables, and Views

Upvotes: 0

Related Questions