CODEWITHSUNDEEP
mysqldatabase
Jason
Jason

Reputation: 21

Fastest way to query small selection from a large table?

I have a music table with >70,000 tracks and want to query a playlist out of that. I am currently running a query that has an ' AND track.id IN ( select id from playlist where pid=5 ) ' statement. Is there a faster way to pull the rows out other than a subselect?

Upvotes: 2

Views: 192

Answers (1)

XIVSolutions
XIVSolutions

Reputation: 4502

I am nothing but an enthusiast, and completely self-taught, so if I am repeating what anyone above already said, please forgive.

However, to me your use of the Sub-Select seems like extra work for the Db, and less scalable. Seems like there should be a Music table (tblMusic, PK_SongID), a playlists table (tblPlaylist PK_PLaylistID), and a reference table indexing them to eachother (tblPlaylist_tblMusic FK_tblPLaylist.PLaylistID, FK_tblMusic.SongID). This table would essentially be the Many-to-many indexing relationship between playlists and songs. You would then simply do the INNER JOIN between the three tables, and set your SELECT Criteria to PlaylistID.

I am suspecting that this was what pst was describing above, but I could be wrong . . .

Upvotes: 1

Related Questions