2020-06-17, 10:26
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.
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.
