Query Not Using Index as Expected
I an using phpMyAdmin with MariaDB Server version: 10.3.31-MariaDB-0ubuntu0.20.04.1-log - Ubuntu 20.04. My Linux version is actually Mint 20.2. I decided to look "under the hood" with phpAdmin's "Advisor" to check things out. I have what should be considered a small home database. I have not observed any performance concerns, but based on what the "Advisor" is reporting in phpMyAdmin, I have significant "tuning" concerns. I believe that I may have narrowed one issue down to the variable "handler_read_rnd". The following query serves as an example:
Select IssueIDNUM, IssueDate, MagazineName FROM tblMagazineIssueList JOIN tblMagazineList ON MagazineIDNUM = MagazineNUM ORDER BY IssueDate DESC, MagazineNUM,IssueIDNUM;
MagazineIDNUM is the primary key in tblMagazineIssueList.
MagazineNUM is a foreign key in tblMagazineIssueList which derives from the primary key in tblMagazineList
The slow query log did not show anything after the query was run. However, the query immediately prior to the query above did show-up in the slow query log.
slow query log reports:
--------------------------------------------------------------------------
use sfmedia;
SET timestamp=1629911455;
Select IssueIDNUM,MagazineNUM FROM tblMagazineIssueList ORDER by IssueDate DESC, IssueIDNUM;
Time: 210825 13:11:38
User@Host: steve[steve] @ localhost []
Thread_id: 1726 Schema: sfmedia QC_hit: No
Query_time: 0.001013 Lock_time: 0.000038 Rows_sent: 2221 Rows_examined: 4442
Rows_affected: 0 Bytes_sent: 23537
SET timestamp=1629911498;
Select IssueIDNUM,MagazineNUM FROM tblMagazineIssueList ORDER by IssueDate DESC, IssueIDNUM;
User@Host: steve[steve] @ localhost []
Thread_id: 1726 Schema: sfmedia QC_hit: No
Query_time: 0.002545 Lock_time: 0.000024 Rows_sent: 2221 Rows_examined: 6669
Rows_affected: 0 Bytes_sent: 85787
SET timestamp=1629911498;
Select IssueIDNUM, IssueDate, MagazineName
FROM tblMagazineIssueList
JOIN tblMagazineList
ON MagazineIDNUM = MagazineNUM
ORDER BY IssueDate DESC, MagazineNUM,IssueIDNUM;
---------------------------------------------------------------
Slow query log ends here. Nothing below the "ORDER BY". As such the slow query log does not identify any problem with the query.
After refreshing the tab "status", the following red items increased in value:
--------------------------------------------------------------
"handler_read_rnd" went from 80.4 k to 82.6K
"handler_read_rnd" went from 27 to 29
All other "notifications" in red, remained the same.
---------------------------------------------------------------
The message associate with the variable "handler_read_rnd" reads:
"The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly."
I suspect the message above means that something, but what, is wrong?
-----------------------------------------------------------------------------------------
Increased the values of some variables by x100 (added two 00) as an attempt to eliminate the variables below as a potential issue. These changes are probably unnecessary as this is a small database for home use and not an enterprise sized database. Included for reference purposes.
tmp_table_size=1677721600 #Default=16777216
max_heap_table_size=1677721600 #Default=16777216
table_open_cache=20000 #Default=2000
key_buffer_size=1342177280 #Default=134217728
sort_buffer_size=209715200 #Default=2097152
Answer Answered by Daniel Black in this comment.
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.
