[HeresJohnny]

Not a support question, rather a technical discussion. I'm no dev and my in-depth-knowledge of SQL is limited, so if I state something obvious please don't be offended. The little I know about SQL query optimization can be summarized in a few exemplary (and not definitive) rules:

- use INDEXES as much as possible
- Try to avoid DISTINCT when possible
- Use INNER JOIN instead of OUTER JOIN when possible

I've been running the slow_query_log on MariaDB for Matrix and see that everything seems to be very much optimized already. However, one type of query keeps repeating that might warrant some looking at:

sql:
SELECT DISTINCT art_id, media_id, media_type, type, '' as prefix, url, 0 as iorder FROM art WHERE media_id = 18207 AND media_type ='album' UNION SELECT art_id, media_id, media_type, type, 'albumartist' as prefix, url, album_artist.iOrder as iorder FROM art JOIN album_artist ON art.media_id = album_artist.idArtist AND art.media_type ='artist' WHERE album_artist.idAlbum = 18207;

/* Affected rows: 0  Found rows: 2  Warnings: 0  Duration for 1 query: 0,000 sec. */

I re-ran this query directly on the database server, that's why is so fast. However, running it without DISTINCT yields the same result, so there might be a couple of milliseconds to gain. Fetching artwork to fill an artist or album overview takes between 25 and 100 queries, depending on how many entires it's set to show.

sql:
SELECT art_id, media_id, media_type, type, '' as prefix, url, 0 as iorder FROM art WHERE media_id = 18207 AND media_type ='album' UNION SELECT art_id, media_id, media_type, type, 'albumartist' as prefix, url, album_artist.iOrder as iorder FROM art JOIN album_artist ON art.media_id = album_artist.idArtist AND art.media_type ='artist' WHERE album_artist.idAlbum = 18207;

/* Affected rows: 0  Found rows: 2  Warnings: 0  Duration for 1 query: 0,000 sec. */

Again, sorry if I've written something stupid.

[Klojum]

If I remove both the WHERE clauses, and run each query a couple of times, then I see no differences.
Lots will still be cached I'm sure, but I still get a 0,060 secs duration on average with both queries.

[DaveBlake]

MariaDB/MySQL is great at handling high levels of transactions, the one aspect that Kodi has little need of at all. In many other ways it is slower and far less efficient that SQLite, and has some horrible flaws e.g. does not use indices when there is a subquery. The SQL sytax differences between them are also tedious.  But MariaDB/MySQL supports client server which SQLite does not, and so Kodi has a db wrapper and offers it as an option. The music library is designed with SQLite primarily in mind, but with an eye to MySQL//MariaDB too. Because of the fundamental differences between these RDBMS it is impossible to truely optimise anything, but yes we can try to use best SQL in the circumstances.

So preable over, let's look at this particular query. It is all about fetching art for the node of items currently on display, and that is something that is done as a background task. The time taken for the query, even on MariaDB/MySQL, is tiny compared to the time it takes to get the actual image from cache or even refresh from remote source and show it on screen. Therefore a few ms here or there on query speed is totally irrelevent.

If I was designing things just with query efficiency in mind I would not be sending a query per item anyway, I would fetch things in one go and process the results set. In this case it would be just fetch the art for the ~14 items on the current screen, or maybe a few either side - lazy loading in batches. But query effiiciency isn't the whole story, and at least art is loaded in the background while the user can continue.

And then the actual statement and use of DISTINCT, and yes in principle design would avoid DISTINCT unless it is necessary. I can't immediately remember why it is there but I am pretty sure I would not have used it without a reason. I am of course prepared to review that, I make mistakes like everyone or something else could have changed in the data to avoid duplicates. But honestly it isn't a priority because there is so little to be gained, and I know there are other areas that need my attention far more.

So bottom line @HeresJohnny , thanks for being interested and no offense taken, but the fundamental principle of all optimisation is the context something is run in. The critical paths matter, shaving even a tiny part can help, but here the query speed is not significant. Also in a quick test I can't see the DISTINCT having any impact on speed anyway.