Trying to remove an inner SQL select statement

Question

I am making a music player where we have stations. I have a table called histories. It has data on the songs a user likes, dislikes or skipped. We store all the times that a person has liked a song or disliked it. We want to get a current snapshot of all the songs the user has either liked (event_type=1) or disliked (event_type=2) in a given station.

The table has the following rows:

id (PK int autoincrement)
station_id (FK int)
song_id (FK int)
event_type (int, either 1, 2, or 3)

Here is my query:

SELECT song_id, event_type, id 
FROM histories 
WHERE id IN (SELECT MAX(id) AS id 
             FROM histories 
             WHERE station_id = 187 
               AND (event_type=1 OR event_type=2) 
             GROUP BY station_id, song_id)  
ORDER BY id;

Is there a way to make this query run without the inner select? I am pretty sure this will run a lot faster without it

If this is MySQL, show us what putting `EXPLAIN` in front of the rest of your query will produce. Any query that's not pulling from an indexed or key column will slow it down. — Makoto, Apr 24 '13 at 06:37
why are you pretty sure? have you examined the query plan and found out what is wrong with it, and is it really about the inner select or something else? — eis, Apr 24 '13 at 06:37
a simple query seems to be enigh as described `SELECT song_id, event_type, id FROM histories WHERE station_id = 187 AND (event_type=1 OR event_type=2) ORDER BY id;` — Raab, Apr 24 '13 at 06:42
If this is in SQL 2012 or Oracle 11g you can use analytical functions to speed it up. But I see you already have an answer. — Nick.Mc, Apr 24 '13 at 08:17

score 5 · Accepted Answer · answered Apr 24 '13 at 06:37

5

You can use JOIN instead. Something like this:

SELECT h1.song_id, h1.event_type, h1.id 
FROM histories AS h1
INNER JOIN
(
   SELECT station_id, song_id, MAX(id) AS MaxId
   FROM histories 
   WHERE station_id = 187 
     AND event_type IN (1, 2) 
   GROUP BY station_id, song_id
)  AS h2  ON h1.station_id = h2.station_id 
         AND h1.song_id    = h2.song_id
         AND h1.id         = h2.maxid
ORDER BY h1.id;

answered Apr 24 '13 at 06:37

Mahmoud Gamal

78,257
17
139
164

`id` is a PK. No other columns are necessary in the derived table (aka "subquery"). Alternative this is, but faster than the original - not. – RichardTheKiwi Apr 24 '13 at 06:52
sorry, can you explain why this isn't faster? (because it is much faster from what I'm seeing) – jamesatha Apr 24 '13 at 07:11
@jamesatha - I am not sure. But, this might be faster than the original one because of the `INNER JOIN` instead of the `IN` predicate. – Mahmoud Gamal Apr 24 '13 at 07:20
is the `IN` predicate better than the OR's? – jamesatha Apr 24 '13 at 20:44
@jamesatha - Yes, the `IN` predicate is faster than multiple `OR`s see this thread: http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance – Mahmoud Gamal Apr 24 '13 at 22:41

score 3 · Answer 2 · answered Apr 24 '13 at 06:55

@Mahmoud Gamal answer is correct, you probably can get rid of the some conditions that is not needed.

SELECT h1.song_id, h1.event_type, h1.id 
FROM histories AS h1
INNER JOIN
(
   SELECT MAX(id) AS MaxId
   FROM histories 
   WHERE station_id = 187 
     AND event_type IN (1, 2) 
   GROUP BY song_id
)  AS h2  ON h1.id = h2.maxid
ORDER BY h1.id;

score 0 · Answer 3 · answered Apr 24 '13 at 06:38

0

Based on your description, this is the answer:

SELECT DISTINCT song_id, event_type, id 
FROM histories 
WHERE station_id = 187 
AND (event_type=1 OR event_type=2) 
ORDER BY id

But you must be doing the MAX for some reason - why?

answered Apr 24 '13 at 06:38

Nick.Mc

18,304
6
61
91

We need the MAX since i want the current snapshot. If a song is liked and later disliked in the station, the latest one (with the highest id value) needs to be the one we return – jamesatha Apr 24 '13 at 06:40

Trying to remove an inner SQL select statement

3 Answers3