CODEWITHSUNDEEP
mysql
pixel
pixel

Reputation: 3639

where id = multiple artists

Any time there is an update within my music community (song comment, artist update, new song added, yadda yadda yadda), a new row is inserted in my "updates" table. The row houses the artist id involved along with other information (what type of change, time and date, etc).

My users have a "favorite artists" section where they can do just that -- mark artists as their favorites. As such, I'd like to create a new feature that shows the user the changes made to their various favorite artists.

How should I be doing this efficiently? 

SELECT * 
FROM table_updates 
WHERE artist_id = 1 
OR artist_id = 500 
OR artist_id = 60032

Keep in mind, a user could have 43,000 of our artists marked as a favorite.

Thoughts?

Upvotes: 1

Views: 185

Answers (4)

Wadih M.
Wadih M.

Reputation: 13462

If you're on SQL Server, you can use a nested select statement:

select * from table_updates where artist_id in 
  (select artist_id from favorites_table where user_id = 10)

If you don't mind doing dirty reads, you can speed it up with (nolock).

select * from table_updates (nolock) where artist_id in 
  (select artist_id from favorites_table (nolock) where user_id = 10)

Upvotes: -1

user269474
user269474

Reputation:

Maybe you can try this:

SELECT * 
FROM table_updates 
WHERE artist_id IN(1, 500, 60032)

Upvotes: 2

erisco
erisco

Reputation: 14329

This depends on how your database is setup. If I had my way, I'd set it up with a table like so:

Table: user_favourite_artist

user_id  |  artist_id
---------------------
1        |  2
1        |  8
1        |  13
2        |  2
3        |  6
6        |  20
6        |  1
6        |  3

user_id and artist_id together would be a composite primary key. Each row specifies a user, by id, and an artist they have as a favourite, by id. A query like so:

SELECT artist_id FROM user_favourite_artist WHERE user_id = 1

Would give you the artist_id's 2, 8, and 13. This is a very simple query that will scale to your expectations.

On the reverse, when an artist is updated, you'd run this query:

SELECT user_id FROM user_favourite_artist WHERE artist_id = 2

And you would get the user_id's 1 and 2. This will tell you which users to notify. This query is also simple and will scale.

Upvotes: 5

Adriaan Stander
Adriaan Stander

Reputation: 166476

If you have the marked artists in a secondary table, I would recomend rather using a join.

Something like

SELECT *
FORM table_updates tu INNER JOIN
table_marked_by_user tmbu ON tu.artist_id = tmbu.artist_id
WHERE tmbu.user_id = $user_id

Upvotes: 1

Related Questions