[RTam1990]

The 60 million is the number of rows MySQL is creating for the episode_view SQL.  When the number is this high, it usually means it's created by full table scans where the database management system (DBMS) has to go through many rows in many tables.  It's the cross-product of the multiple JOINs in the SQL.  So if multiple tables are used then you can have the scenario where it's 500 rows x 500 x 500 is 125 million rows.  That's an example where three tables with 500 rows each need a full table scan on each table.  Not saying that's what's happening here.  Another example could be 400 x 20 x 100 x 30 x 300 = 7,200,000,000 (7 billion).  The number of rows returned is only 24,000.  This indicates a non-performant SQL where there is one or more indices missing.

I've determined the SQL used to create the episode_view and looking through it now.

[RTam1990]

OK, fixed it.

Don't know if this was only on my setup or others may experience this.  There was an index missing on the episode table.  The 60 million was reduced to 118264.  The response time for the query went from 285.091743 seconds down to 0.651170 seconds.

The TV shows Recently added episodes widget is now populated almost immediately.  Makes me want to keep looking at the other views to make them as fast as possible.

Anyway, the SQL that I used to create the index is the following, if anyone is interested...I think this is it:

    CREATE INDEX idx_idSeason ON episode (idSeason);

From my quick look at the response times on other queries in the slow query log, it looks like I can still reduce the response times for the episode_view and reduce the response times for the movie_view.

Queries should usually be sub-second response times.  If they are over a second to execute, that usually means there is something wrong somewhere.

[CrystalP]

Interesting. It's not a standard index and didn't go missing in your database.
How many shows/seasons/episodes in the library?

edit: actually the seasons table is not even used in the episode_view. A better solution would be to fix the view.

[RTam1990]

I tried posting the SQL for the view and kept getting errors so I removed it from my post.

When I view the episode_view definition in my MySQL client, I get a create view statement with a JOIN near the end with the following with removed apostrophes/quotes:

    join seasons on(seasons.idSeason = episode.idSeason)):

I obtained the SQL  when I executed the following SQL:

    SHOW CREATE VIEW episode_view

I guess with the JOIN on a column, if no index exists for any columns, then it does a full table scan.  From what I read on MySQL, it creates intermediate temp tables with the rows required.

Adding the index I described did have the beneficial impact I mentioned.  Perhaps the episode_view had this definition at one time?  What should the view SQL be?  I can try changing it to what you provide to see what happens.  My database was created with a previous version of Kodi and upgraded to what I have now, I think, as I upgraded Kodi.  Probably in the Krypton version.

Please provide me the SQL if you want me to try different SQL for the episode_view.

[CrystalP]

Remove the " JOIN seasons ON seasons.idSeason=episode.idSeason" part of the query.

[RTam1990]

OK, I'll try that.  Do you know why it was there in the first place?  Does your episode_view have this?

[pkscout]

OK, I'll try that.  Do you know why it was there in the first place?  Does your episode_view have this?

I did a check on databases I have, and that join is there.  CrystalP did some digging, at it looks like that join was added almost a decade ago to get the season ID, but it hasn't really been needed for quite awhile.  For what I would call "normal" databases, you probably wouldn't ever notice the performance hit.  When you start getting into setups with the number of TV shows you must have, the performance hit will grow at a non-linear rate (as you saw).  Removing that join will resolve your issue.  I'll check and see if I need to create an issue for this in Github, and perhaps we can remove the join for Kodi 22.

[RTam1990]

I was getting ready to try removing the join when I decided to leave it as-is.  It's working fine with the indices I've created.  No worries.

Thanks.