View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0005655 | MMW v4 | Playlist / Search | public | 2009-05-22 11:28 | 2009-06-05 12:03 |
| Reporter | Bex | Assigned To | |||
| Priority | urgent | Severity | major | Reproducibility | always |
| Status | closed | Resolution | fixed | ||
| Product Version | 3.1 | ||||
| Target Version | 3.1 | Fixed in Version | 3.1 | ||
| Summary | 0005655: Filter/Auto-playlist/Advance search using "Is/Is Not - Playlist" is too slow | ||||
| Description | Currently this sql is generated: SELECT Songs.* FROM Songs WHERE ( ( Songs.Id in (SELECT Songs.Id FROM Songs WHERE Songs.DateAdded<39905.535819039 LIMIT 100) OR exists (select null from PlaylistSongs where PlaylistSongs.IdSong=Songs.Id and PlaylistSongs.IdPlaylist = 2946 ) OR exists (select null from PlaylistSongs where PlaylistSongs.IdSong=Songs.Id and PlaylistSongs.IdPlaylist = 3052 ) OR exists (select null from PlaylistSongs where PlaylistSongs.IdSong=Songs.Id and PlaylistSongs.IdPlaylist = 2945 ) ) ) This simple change makes it much faster: SELECT Songs.* FROM Songs WHERE ( ( Songs.Id in (SELECT Songs.Id FROM Songs WHERE Songs.DateAdded<39905.535819039 LIMIT 100) OR Songs.Id in (select IdSong from PlaylistSongs where PlaylistSongs.IdPlaylist = 2946 ) OR Songs.Id in (select IdSong from PlaylistSongs where PlaylistSongs.IdPlaylist = 3052 ) OR Songs.Id in (select IdSong from PlaylistSongs where PlaylistSongs.IdPlaylist = 2945 ) ) ) However, grouping the static playlists together into an IN clause makes it extremely fast! So if possible to implement I suggest this one. :) SELECT Songs.* FROM Songs WHERE ( ( Songs.Id in (SELECT Songs.Id FROM Songs WHERE Songs.DateAdded<39905.535819039 LIMIT 100) OR Songs.Id in (select IdSong from PlaylistSongs where PlaylistSongs.IdPlaylist IN (2946,3052,2945)) ) ) | ||||
| Additional Information | http://mediamonkey.com/forum/viewtopic.php?f=6&t=39744 | ||||
| Tags | No tags attached. | ||||
| Fixed in build | 1249 | ||||
|
|
Bex, it is not true. There is index on the PlaylistSongs.IdSong table and therefore using EXISTS is much much faster. In order to proof it I have done a test with 2 playlists Playlist A: 7000 tracks Playlist B: 2000 tracks Searching for 'Playlist is A,B' The SQL takes in case of using EXISTS: ' .... exists (select null from PlaylistSongs where PlaylistSongs.IdSong=Songs.Id and PlaylistSongs.IdPlaylist = A) OR exists (select null from PlaylistSongs where PlaylistSongs.IdSong=Songs.Id and PlaylistSongs.IdPlaylist = B)' takes only 0.001 seconds, but in case of using IN: '... Songs.Id in (select IdSong from PlaylistSongs where PlaylistSongs.IdPlaylist = A ) OR Songs.Id in (select IdSong from PlaylistSongs where PlaylistSongs.IdPlaylist = B )' it takes 0.1 seconds! The reason here is, that in case of using '.. IN (SELECT ...)' you need to perform (select IdSong from PlaylistSongs where PlaylistSongs.IdPlaylist = B ) beforehand which is unnecessary when there is index on the PlaylistSongs.IdSong. |
|
|
Strange, with my original database (100 000 tracks) the first query takes 8 seconds. The second one 2 seconds. The third one 1,5 seconds. With a small testdatabase I got bigger differences with the third one executed instantly. I found that using EXISTS in SQLIte it almost always slower than using IN, strangely enough that goes for NOT EXISTS as well. I'll do some more testing. |
|
|
OK, I ran these sql's in SQL-Viewer with my big database: Executed in 16 seconds SELECT Songs.id FROM Songs WHERE exists (select null from PlaylistSongs where PlaylistSongs.IdSong=Songs.Id and PlaylistSongs.IdPlaylist = 13695 ) OR exists (select null from PlaylistSongs where PlaylistSongs.IdSong=Songs.Id and PlaylistSongs.IdPlaylist = 13696 ) OR exists (select null from PlaylistSongs where PlaylistSongs.IdSong=Songs.Id and PlaylistSongs.IdPlaylist = 34 ) OR exists (select null from PlaylistSongs where PlaylistSongs.IdSong=Songs.Id and PlaylistSongs.IdPlaylist = 40 ) OR exists (select null from PlaylistSongs where PlaylistSongs.IdSong=Songs.Id and PlaylistSongs.IdPlaylist = 41 ) OR exists (select null from PlaylistSongs where PlaylistSongs.IdSong=Songs.Id and PlaylistSongs.IdPlaylist = 42 ) OR exists (select null from PlaylistSongs where PlaylistSongs.IdSong=Songs.Id and PlaylistSongs.IdPlaylist = 43 ) OR exists (select null from PlaylistSongs where PlaylistSongs.IdSong=Songs.Id and PlaylistSongs.IdPlaylist = 45 ) Executed in 5 seconds: SELECT Songs.id FROM Songs WHERE Songs.ID IN (select IdSong from PlaylistSongs where PlaylistSongs.IdPlaylist = 13695 ) OR Songs.ID IN (select IdSong from PlaylistSongs where PlaylistSongs.IdPlaylist = 13696 ) OR Songs.ID IN (select IdSong from PlaylistSongs where PlaylistSongs.IdPlaylist = 34 ) OR Songs.ID IN (select IdSong from PlaylistSongs where PlaylistSongs.IdPlaylist = 40 ) OR Songs.ID IN (select IdSong from PlaylistSongs where PlaylistSongs.IdPlaylist = 41 ) OR Songs.ID IN (select IdSong from PlaylistSongs where PlaylistSongs.IdPlaylist = 42 ) OR Songs.ID IN (select IdSong from PlaylistSongs where PlaylistSongs.IdPlaylist = 43 ) OR Songs.ID IN (select IdSong from PlaylistSongs where PlaylistSongs.IdPlaylist = 45 ) Executed in 0.9 seconds: SELECT Songs.id FROM Songs WHERE Songs.ID IN (select IdSong from PlaylistSongs where PlaylistSongs.IdPlaylist IN (13695,13696,34,40,41,42,43,45)) If anyone want to reproduce on their own DB just run this SQL which gives the IdPlaylist. Exchange the first 8 ones in the SQL's above: SELECT IDPLAYLIST, PLAYLISTNAME FROM Playlists WHERE ISAUTOPLAYLIST=0 or ISAUTOPLAYLIST is null It really seems to be room for improvements here! |
|
|
OK, I hope you don't have 13696 playlists in your DB :-)) , but you seem to be rigth that approach C) seems to be generally the best one. i.e. we can go with it (....IN (13695,13696,34,40,41,42,43,45).....) I am about to fix it tomorrow. BTW. RE the using of EXISTS: I have optimized many queries like this one (form A) SELECT Songs.* FROM Songs WHERE exists (SELECT NULL FROM GenresSongs WHERE GenresSongs.IDSong=Songs.ID AND GenresSongs.IDGenre=?) to the form B: SELECT Songs.* FROM Songs WHERE ID IN (SELECT IDSong FROM GenresSongs WHERE GenresSongs.IDGenre=?) The true is that the form B is at least ten times faster if you use it for searching a bigger set of tracks (e.g. showing all tracks of the selected genre node), but if you use it for finding whether a single track satisfies the (genre) criteria then the form A is hundred times faster. i.e. In such a cases the query should look like SELECT Songs.* FROM Songs WHERE Songs.ID = X and exists (SELECT NULL FROM GenresSongs WHERE GenresSongs.IDSong=Songs.ID AND GenresSongs.IDGenre=Y) and should be of the form A. |
|
|
:) No I don't. But I don't know why the counter is so high? Yeah, I'm positive that C) is the way to go! |
|
|
Fixed in build 1249. |
|
|
Verified 1250 |
