Hello,<br><br>I've rebuild amarok with this debug 1 option, but on an other computer (job's one) which is much (much) more powerful (double dual core xeon and 4 GO Ram). This one is running sqlite. Anyway each query seems to take quite a long time (0.3 s) too, and the action to change a track also make the sytem hangs for about 10 seconds, even on this very powerful system. So it might definitly be the queries (mysql or slite) who are too heavy<br>
<br>One idea, on both computer I use exactly the same path for the database path (so i can synch both database)<br>I don't know much about sql query, but i can see at the end "order by path". All my music file path contain a lot of space and strange character, (-= =-) in both computer, and both computer are slow on track change, sqlite or mysql backend. Do you thing this can cause a significant difference on the queries, compared to a clear path (aka no space) when ordering ?<br>
<br>Here are some examples of file path : <br><br>/home/simon/Autres/Musique/-=Albums=-/-= A =-/Aphex Twin/1995 - Classics/13 - Digeridoo Live in Cornwall 1990.flac<br>/home/simon/Autres/Musique/-=Albums=-/-= B =-/Boban Markoviæ Orkestar/2002 - Bistra Reka/14 - Bolujem ja.flac<br>
/home/simon/Autres/Musique/-=Albums=-/-= M =-/My Bloody Valentine/1991 - Loveless/01 - Only Shallow.flac<br>/home/simon/Autres/Musique/-=Albums=-/-= Y =-/Yo La Tengo/2005 - Prisoners of Love (Disc 1)/02 - Sugarcube.flac<br>
/home/simon/Autres/Musique/-=B.O Divers=-/-= B.O. Jeux Vidéos =-/Alex Kidd In Miracle World (1988)/Alex Kidd in Miracle World - 02 - Start Level.mp3<br>/home/simon/Autres/Musique/-=B.O Divers=-/-= B.O. Films : Compilations =-/Danny Elfman - Music for a Darkened Theatre - Volume Two (cd1) (1996)/Danny Elfman - Music For A Darkened Theatre - Volume Two (Cd1) - 05 - Edward Scissorhands The Grand Finale.mp3<br>
/home/simon/Autres/Musique/-=B.O Films=-/-= 0...9 =-/8 Femmes (2001)/8 Femmes - 16 - Krishna Levy - Portrait De Gaby.mp3<br>/home/simon/Autres/Musique/-=Compil=-/Best of legal download 2006/Best Free Legal Downloads 2006 - 21 - Sally Shapiro - Anorak Christmas.mp3<br>
<br>Attached is the log file of a track changed, with debug option set to 1.<br><br>Thanks a lot for your help ! And still apologize for french-glish<br>Simon<br><br>PS > Jer, i can log on the mysql server and run the command you asked, but i don't understand how to interpret them. Both of them return very fast answer, about 0.02 or something like this. What should I do next ? Thanks a lot for helping<br>
<br><div class="gmail_quote">On Jan 27, 2008 11:08 PM, Jer Johnson <<a href="mailto:jer@gweep.net" target="_blank">jer@gweep.net</a>> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Allo Simon,<br><br>if you're compiling Amarok from source, you can add<br><br>#define DEBUG 1<br><br>at the top of collectiondb.cpp , for some more debug output on DB queries.<br><br><br>you can check the efficiency of a MySQL query by using the 'explain' command.<br>
<br>from your shell:<br>$ dcop amarok collection query "explain select * from tags where 0 or (1 and artist=2317)"<br>1<br>SIMPLE<br>tags<br>ref<br>artist_tag,tags_artist_index<br>artist_tag<br>5<br>const<br>22<br>
Using where<br><br>this can also be run from the MySQL command line:<br><br>mysql> explain select * from tags where artist = '2317';<br>+----+-------------+-------+------+------------------------------+------------+---------+-------+------+-------------+<br>
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br>+----+-------------+-------+------+------------------------------+------------+---------+-------+------+-------------+<br>
| 1 | SIMPLE | tags | ref | artist_tag,tags_artist_index | artist_tag | 5 | const | 22 | Using where |<br><br><br>this is a simple select with a known key (artist_tag), and should be very quick.<br><br><br>
now, the same query run as a wildcard match:<br><br>mysql> explain select * from tags where artist like '%2317';<br>+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+<br>
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br>+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+<br>| 1 | SIMPLE | tags | ALL | NULL | NULL | NULL | NULL | 488568 | Using where |<br>
<br><br>with the wildcard, the table index can't be used, and the<br>entire table needs to be scanned.<br><br><br>hopefully, this will help you locate where your MySQL queries are<br>slowing down. I've submitted a patch or two for collectiondb.cpp based<br>
on what 'explain' shows.. I think the Amarok crew is waiting for the<br>Amarok2 rewrite . hopefully that'll contain the needed query<br>optimizations for big collections.<br><br> -jer johnson<br>
<a href="mailto:jer@gweep.net" target="_blank">jer@gweep.net</a><br><font color="#888888"><br>--<br><a href="mailto:jer@sidehack.gweep.net" target="_blank">jer@sidehack.gweep.net</a> Programmer / Sysadmin / Gweep<br>
<a href="mailto:mpython@gnu.org" target="_blank">mpython@gnu.org</a> -><- Author of Xtacy, an X11 Graphics Hack<br>
<a href="mailto:jer_johnson@real.life.edu" target="_blank">jer_johnson@real.life.edu</a> <a href="http://www.gweep.net/%7Ejer/index.html" target="_blank">http://www.gweep.net/~jer/index.html</a><br>ICQ#: 19599864 AIM: DruelChaos YM!: DruelChaos GT: druelchaos<br>
</font></blockquote></div><br><br clear="all"><br>-- <br>------------------------------------------------------------------<br>Simon Esneault<br>Laboratoire Traitement du Signal et de l'Image, (LTSI, UMR-INSERM 642)<br>
Université de Rennes I, Campus de Beaulieu, <br>35042 Rennes Cedex, France.<br>Tel : +33 (0)6 64 61 30 94<br>Mail : <a href="mailto:simon.esneault@univ-rennes1.fr" target="_blank">simon.esneault@univ-rennes1.fr</a><br>------------------------------------------------------------------