2020-06-17, 00:28
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:
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.
Again, sorry if I've written something stupid.
- 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.