View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0003208 | MMW v4 | DB/FileMonitor | public | 2007-06-29 13:15 | 2007-08-03 19:06 |
| Reporter | jiri | Assigned To | |||
| Priority | urgent | Severity | minor | Reproducibility | always |
| Status | resolved | Resolution | fixed | ||
| Product Version | 3.0 | ||||
| Fixed in Version | 3.0 | ||||
| Summary | 0003208: Optimize and fix some queries | ||||
| Description | Some queries, most importantly those searching for Artist or Genre aren't currenlty implemented well in the context of the new MM 3.0 DB structure. E.g. Genre searchs look directly in Genre field in Songs table. What it should do instead is to use joined Genre and GenreSongs tables to find song IDs with given genre (or part of genre). The same applies to Artist searches or Classification (Mood, Tempo, ...) searches (I haven't checked out these). | ||||
| Tags | No tags attached. | ||||
| Fixed in build | 1058 | ||||
|
|
I guess that searching directly in Songs.Genre field instead of searching via GenreSongs table is used only in case of searchig a text fields so that it would be more faster. e.g. if you search genre contains "trip" then there is no need to search it via the GenreSongs table because you can search directly in the Songs.Genre field. |
|
|
No, it is significantly better to use Genres and GenreSongs tables. The thing is that if you look for a genre 'trip' in Songs table, you have to go trough all records in Songs table (i.e. even ~100k), which means to read almost the whole DB file from disk (i.e. easily >200MB). On the other hand, using Genres table involves only search of some 0000071:0000100 genres, the rest goes over indexed IDs. So, while the query will be a couple of characters longer, the execution time will be _significantly_ smaller. |
|
|
You are probably right, but according to what I have tested so far on my DB your proposal takes longer (both preparing and executing of the query). Once I can test it on a realy big DB I could find out whether your proposal is better, but I cannot use the big DB from our ftp because of some DB updates. I need a newer one. |
|
|
Recent note from a user reminded me that this isn't just a performance issue, it's actually a bug. By words of the user: ---- I noticed one more thing. Searching for Artists that starts with Ferry doesn't include [Tiesto;Ferry Corsten] in the result, That's because the search is performed on the Artist field in Songs table rather than the Artist field in the Artists table. ---- I.e. we _must_ implement it the suggested way. As for the large DB, I guess it isn't necessary to test it, but anyway I have uploaded the latest version as BigDBLatest.rar to FTP (but I think that the previous version should work too after MM updates it). |
|
|
Btw, the same problem for Mood and other fields is reported at http://www.mediamonkey.com/forum/viewtopic.php?p=97984#97984 |
|
|
All is fixed in build 1058. Btw. The problem with multiple Moods, Tempos, Occasions... were about storing into DB. |
