Comments - Query Not Using Index as Expected
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
sort_buffer_size may have an effect, but looking at you slow query log for Rows_examined it doesn't seem likely.
Please include SHOW CREATE TABLE information for both tables. If you want to cut this down to just the fields and indexes of the table that's ok.
Use ANALYZE FORMAT=JSON to show how the query is being executed. log_slow_verbosity can help get some extended information in the slow query log.
Contrary to the implication of phpmyadmin advice, you can't tune yourself out of every problem. You query returns the entire data from two entire tables. The recommendation on handler_read_rnd is based on the assumption you don't read entire tables often and that when MariaDB does read the entire tables, its because it isn't index right.
Is this query really a problem? The table sizes seem quite small and I suspect you are getting less than a 10ms response time.
A concern with using programs such as PhpMyAdmin is learning which advice is valid and which is spurious. Your suggestions have been helpful in that regard. I see that there are a lot of options that can be used for the "slow log" that will help with the analysis of SQL statements. Thanks for responding.
Thanks, I will have to follow-up on your advice.
No, this query (as one example of several) is not a problem as I don't see any performance issues. Just an attempt by me to learn more.
You wrote: "Contrary to the implication of phpmyadmin advice, you can't tune yourself out of every problem". Quite true!!! In experimenting, I ran across several cases of conflicting advice. Additionally, some advise went away after MariaDB ran for a while. Part of the learning experience.