My recordedseek table has 1.1M rows. But, I don't think that table is part of the BUSQ. Why would converting this table to innoDB affect the BUSQ?<br><br><div><span class="gmail_quote">On 11/28/07, <b class="gmail_sendername">
ross camm</b> <<a href="mailto:rossco@whyza.net">rossco@whyza.net</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>
do you have many rows in recordedseek ?<br><br>i do, nearly 1.5 million rows, and converting from MyISAM to InnoDB for<br>the recordedseek table reduced my query time from 20-30 secs down to 2-3<br>secs or less.<br><br>as such deletions are now nearly instant.
<br><br>On Wed, 2007-11-28 at 20:16 -0500, Larry K wrote:<br>> I am also having trouble with the BUSQ when I delete recordings, with<br>> query times no better than 7-8 seconds. This is on an Athlon 2500+<br>> with 512MB ram. Myth
0.20 and MySQL 5.0.37, I think. Before I<br>> upgraded to 0.20, I was running 0.18 and this problem did not exist<br>> for me. I'm guessing this BUSQ was introduced after 0.18.<br>><br>> I cleaned up my recording schedules to eliminate duplicate schedules
<br>> (there were a few), and I also tried to avoid schedules on 'any<br>> channel'. I currently have 4,800 rows in oldrecorded, 550 rows in<br>> recordmatch, and 100 rows in record. None of this seems excessive to
<br>> me. I also optimized my database, and it still takes 6-7 seconds to<br>> run this query.<br>><br>> I wonder if 6-7 seconds is as good as it gets for me? Have I hit the<br>> wall? Can anyone with a config similar to mine get this query to run
<br>> 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>> On 11/24/07, crs23 <<a href="mailto:pvr@groundhog.pair.com">pvr@groundhog.pair.com</a>
> wrote:<br>><br>> I used MythWeb to both discover duplicates by inspection and<br>> delete them.<br>> That took a long time because of the very problem I was trying<br>> to fix but
<br>> each deletion went faster than the last as the query got<br>> faster and faster.<br>> A way to delete multiple recordings or recording rules at once<br>> would be nice<br>
> but I wasn't about to try deleting records directly from the<br>> database<br>> because I don't know if that would cause some kind of<br>> inconsistency between<br>> tables.
<br>><br>><br>> Larry K wrote:<br>> ><br>> > What was your method to determine the duplicates? And did<br>> 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<br>> having identical<br>> >> > recording rules. Though it shouldn't cause issues,
<br>> cleaning out the<br>> >> > duplicates (triplicates? whatever) would significantly<br>> simplify the<br>> >> > query's processing, so doing so would be very much to
<br>> your advantage.<br>> >> ><br>> >><br>> >> Done and the improvement was substantial. There were<br>> several multiple<br>> >> entries. One program had about a dozen duplicate entries
<br>> and that just<br>> >> happened to be the program with the most previously<br>> recorded episodes. I<br>> >> also changed the recordings to be on a particular channel
<br>> 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<br>> rows
<br>> >> returned. 5<br>> >> or 6 seconds is hugely better but still a bit of an<br>> annoyance. But I'm<br>> >> very<br>> >> pleased with the results so far and the incredible support
<br>> I've received<br>> >> on<br>> >> this forum. Thank you to everyone!<br>> >><br>> >> --<br>> >> View this message in context:
<br>> >><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<br>> <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:<br>> <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>><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>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>