I am also having trouble with the BUSQ when I delete recordings, with query times no better than 7-8 seconds. This is on an Athlon 2500+ with 512MB ram. Myth 0.20 and MySQL 5.0.37, I think. Before I upgraded to 0.20, I was running
0.18 and this problem did not exist for me. I'm guessing this BUSQ was introduced after 0.18.<br><br>I cleaned up my recording schedules to eliminate duplicate schedules (there were a few), and I also tried to avoid schedules on 'any channel'. I currently have 4,800 rows in oldrecorded, 550 rows in recordmatch, and 100 rows in record. None of this seems excessive to me. I also optimized my database, and it still takes 6-7 seconds to run this query.
<br><br>I wonder if 6-7 seconds is as good as it gets for me? Have I hit the wall? Can anyone with a config similar to mine get this query to run in say 1 second? <br><br>/etc/my.conf:<br><br>[mysqld]<br>datadir=/var/lib/mysql
<br>socket=/var/lib/mysql/mysql.sock<br>key_buffer = 16M<br>table_cache = 128<br>sort_buffer_size = 2M<br>myisam_sort_buffer_size = 8M<br>query_cache_size = 16M<br>log_slow_queries=/var/log/slowsql.log<br>long_query_time=5
<br><br>[mysql.server]<br>user=mysql<br>basedir=/var/lib<br><br>[safe_mysqld]<br>err-log=/var/log/mysqld.log<br>pid-file=/var/run/mysqld/mysqld.pid<br><br><br><br><div><span class="gmail_quote">On 11/24/07, <b class="gmail_sendername">
crs23</b> <<a href="mailto:pvr@groundhog.pair.com">pvr@groundhog.pair.com</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>I used MythWeb to both discover duplicates by inspection and delete them.<br>That took a long time because of the very problem I was trying to fix but<br>each deletion went faster than the last as the query got faster and faster.
<br>A way to delete multiple recordings or recording rules at once would be nice<br>but I wasn't about to try deleting records directly from the database<br>because I don't know if that would cause some kind of inconsistency between
<br>tables.<br><br><br>Larry K wrote:<br>><br>> What was your method to determine the duplicates? And did you simply<br>> issue<br>> a SQL delete to get rid of them, or some other mechanism?<br>><br>> On 9/6/07, crs23 <
<a href="mailto:pvr@groundhog.pair.com">pvr@groundhog.pair.com</a>> wrote:<br>>><br>>><br>>><br>>> Michael T. Dean wrote:<br>>> ><br>>> > I don't think much testing has been done on systems having identical
<br>>> > recording rules. Though it shouldn't cause issues, cleaning out the<br>>> > duplicates (triplicates? whatever) would significantly simplify the<br>>> > query's processing, so doing so would be very much to your advantage.
<br>>> ><br>>><br>>> Done and the improvement was substantial. There were several multiple<br>>> entries. One program had about a dozen duplicate entries and that just<br>>> happened to be the program with the most previously recorded episodes. I
<br>>> also changed the recordings to be on a particular channel instead of any<br>>> channel which I think helped to.<br>>><br>>> I'm now down to ~150,000 rows examined in 5.5 seconds, 696 rows
<br>>> returned. 5<br>>> or 6 seconds is hugely better but still a bit of an annoyance. But I'm<br>>> very<br>>> pleased with the results so far and the incredible support I've received<br>
>> on<br>>> this forum. Thank you to everyone!<br>>><br>>> --<br>>> View this message in context:<br>>> <a href="http://www.nabble.com/Slow-MySQL-query-after-delete-tf4366877s15552.html#a12534738">
http://www.nabble.com/Slow-MySQL-query-after-delete-tf4366877s15552.html#a12534738</a><br>>> Sent from the mythtv-users mailing list archive at <a href="http://Nabble.com">Nabble.com</a>.<br>>><br>>> _______________________________________________
<br>>> mythtv-users mailing list<br>>> <a href="mailto:mythtv-users@mythtv.org">mythtv-users@mythtv.org</a><br>>> <a href="http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users">http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
</a><br>>><br>><br>> _______________________________________________<br>> mythtv-users mailing list<br>> <a href="mailto:mythtv-users@mythtv.org">mythtv-users@mythtv.org</a><br>> <a href="http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users">
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users</a><br>><br>><br><br>--<br>View this message in context: <a href="http://www.nabble.com/Slow-MySQL-query-after-delete-tf4366877s15552.html#a13926650">http://www.nabble.com/Slow-MySQL-query-after-delete-tf4366877s15552.html#a13926650
</a><br>Sent from the mythtv-users mailing list archive at <a href="http://Nabble.com">Nabble.com</a>.<br><br>_______________________________________________<br>mythtv-users mailing list<br><a href="mailto:mythtv-users@mythtv.org">
mythtv-users@mythtv.org</a><br><a href="http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users">http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users</a><br></blockquote></div><br>