CODEWITHSUNDEEP
mysql
Jonathan
Jonathan

Reputation: 2014

How to select multiple rows by multi-column primary key in MySQL?

I have a table with a multi-column primary key (city/state/date) and many more columns of data. I'm looking to get the latest data for each city/state. How do I do that cleanly/efficiently? Right now I can do this by doing a first query to get the list of all the rows I'm trying to fetch, followed by a second query with a massive WHERE clause:

 SELECT state, city, max(date) from data GROUP BY city, state;

+-------+---------------------+------------+
| state | city                | MAX(date)  |
+-------+---------------------+------------+
| CA    | San Francisco       | 2013-09-01 |
| CA    | Los Angeles         | 2013-08-01 |
| NY    | New York            | 2013-10-01 |
| ...   | ... (many rows) ... | ...        |
+-------+---------------------+------------+


SELECT * FROM data WHERE 
    (state = "CA" AND city = "San Francisco" AND date='2013-09-01') OR 
    (state = "CA" AND city = "Los Angeles" AND date='2013-08-01') OR 
    (state = "NY" AND city = "New York" AND date='2013-10-01') OR 
    ...

This is really ugly and inefficient, and if the first query returns a lot of rows my second query might be too long. Clearly if I have a single-column primary key I could use a subselect with IN(), but that's not really possible here. Any suggestions?

UPDATE: I tried Bill's suggestion with a subselect, but it's not using any keys and is taking forever. If I restrict the subselect to only return 5 rows it returns in 0.64s. If I let it return all 73 city/state combinations, it takes a very long time (query still running).

EXPLAIN SELECT * FROM data WHERE (city, state, date) IN (SELECT state, city, MAX(date) FROM data GROUP BY city, state)
+----+--------------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type        | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+--------------------+-------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | PRIMARY            | data  | ALL   | NULL          | NULL    | NULL    | NULL | 13342 | Using where |
|  2 | DEPENDENT SUBQUERY | data  | index | NULL          | PRIMARY | 57      | NULL |  8058 | Using index |
+----+--------------------+-------+-------+---------------+---------+---------+------+-------+-------------+

Upvotes: 10

Views: 8926

Answers (2)

Wintermute
Wintermute

Reputation: 1531

I think this should do the trick for you:

select 
    * 
from 
    data t1
natural join 
    ( 
        select 
            city, 
            state, 
            max(date) as date
        from 
            data
        group by 
            city, 
            state
    ) t2;

Upvotes: 6

Bill Karwin
Bill Karwin

Reputation: 562368

MySQL supports tuple comparisons:

SELECT * FROM data WHERE 
 (state, city, date) IN (
  ('CA', 'San Francisco', '2013-09-01'), 
  ('CA', 'Los Angeles', '2013-08-01'), 
  ('NY', 'New York', '2013-10-01'));

Upvotes: 5

Related Questions