I've posted a thread about freqent crashes when clicking on Video button on main Orbiter screen... Crash came out of data not being available in reasonable time - and cause is that my disk is so slooow...

both disks are sata. First is system disk, the second is for media (2T)... First one is about half full, second aroung 75%. I doubt I have the biggest media collection. When disk is fast enough (when that huge query is not slowing it down), then Orbiter doesn't crash. It probably gets all data within timeout... But I have a feeling, that all media is retrieved at that moment, although only subset will be shown on screen...

I've disabled UpdateMediaDaemon from command line (Radu just gave me a hint that check in web-admin is broken) and will see if that query will come back again....

To give you an idea of how crazy of a rabbit hole, this is.. I've spent the last three and a half hours with a friend well versed in SQL. We were able to compact the query, but the results actually wound up being slower than when we started.

To give you an idea of how crazy of a rabbit hole, this is.. I've spent the last three and a half hours with a friend well versed in SQL. We were able to compact the query, but the results actually wound up being slower than when we started.

I could REALLY use a database designer's help here.

-Thom

Thom... did you have any luck there? I ran that query on my DB and I got

3017 rows in set (0.40 sec)

Obviously my media db is pretty modest but that's pretty snappy all the same... . Bulek, how big is yours and how long does it take if you run the query manually from the mysql prompt?

Hmmm... don't have time to check if the data is exactly the same but it is certainly the same number of rows and results in about a 25% speed improvement for me... perhaps someone else can try the following query rewrite to compare with the original:

Did some research on that explain plan you posted and I think the problem was the group-by clause that groups by a bunch of dependent fields that aren't indexed. If we group on the PK of File and then join back to file to get the fields we need it seems to take care of that. In fact, it improves my query by quite a dramatic amount. Does anyone else want to try this?