just did find time to find the query for my system and it puzzels me that every dvd/recording shows up not only once, but several times !? :
there is a command to prevent duplicate lines in result sets from mysql. Just try to do a "SELECT DISTINCT ..." instead of "SELECT ...". Reducing the result set of a query does also reduce resource usage, especially if the results are used to start another query.
What strikes me. Why is it doing this :
"AND PK_File in (37968,37996,38006, ....."
It looks like there is an query before that which sorts out the files to look at. The question is then if it makes sence to split that up in two. But maybe we should just try and optimize the query first.
Using nested queries should even speed up the process because the results do not have to be tranferred back and forth, i.e. try something like "SELECT ... FROM ... WHERE ... AND PK_File in (SELECT DISTINCT FK_File FROM ....)".