I've got a music table with >70,000 tracks and wish to query a playlist from that. I'm presently managing a query which has an ' AND track.id IN ( choose id from playlist where pid=5 ) ' statement. It is possible to faster method to pull the rows out apart from a subselect?
I'm only an enthusiast, and completely self-trained, so should i be repeating what anybody above already stated, please forgive.
However, in my experience your utilisation of the Sub-Choose appears like work for that Db, and fewer scalable. Appears like there must be a Music table (tblMusic, PK_SongID), a playlists table (tblPlaylist PK_PLaylistID), along with a reference table indexing these to eachother (tblPlaylist_tblMusic FK_tblPLaylist.PLaylistID, FK_tblMusic.SongID). This table would basically function as the Many-to-many indexing relationship between playlists and tunes. You would function the interior JOIN between your three tables, and hang your Choose Criteria to PlaylistID.
I'm suspicious of this was what pst was explaining above, however i might be wrong . . .