[FIXED] MySQL - Why is phpMyAdmin extremely slow with this query that is super fast in php/mysqli?

Issue

Edit: see also my answer, the main difference is the LIMIT that phpmyadmin adds, but I still don't understand and phpmyadmin is still slower than mysqli.

On our database (+web) server we have a huge difference in performance when doing a query in phpmyadmin vs doing it from php (mysqli) or directly on the mariadb server. 60 seconds vs < 0.01 seconds!

This query functions quite well:

SELECT * FROM `TitelDaggegevens` 
WHERE `datum` > '2020-03-31' AND datum < '2020-05-02' AND `fondskosten` IS NULL 
ORDER BY isbn;

But, only in phpMyAdmin, the query becomes extremely slow when we change 2020-05-02 to 2020-05-01.

SHOW PROCESSLIST shows that the queryu is mainly Sending data whilst running.

Following mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts I did the following query-series:

FLUSH STATUS;
SELECT-query above with one of the two dates;
SHOW SESSION STATUS LIKE 'Handler%';

The differences are fascinating. (I left out all the values equal to 0 in all cases). And consistent over time.

|                        how:   |     server/MySqli       |      phpMyAdmin 
|         date used in query:   | 2020-05-02 | 2020-05-01 | 2020-05-02 | 2020-05-01
|           records returned:   | 6912       | 1          | 6912       | 1
|                  avg speed:   | 0.27s      | 0.00s      | 0.52s      | 60s (!)
| Variable_name                 | Value      | Value      | Value      | Value
| Handler_icp_attempts          | 213197     | 206286     | 213197     | 0
| Handler_icp_match             | 6912       | 1          | 6912       | 0
| Handler_read_next             | 6912       | 1          | 26651      | 11728896 (!)
| Handler_read_key              | 1          | 1          | 151        | 4
| Handler_commit                | 1          | 1          | 152        | 5
| Handler_read_first            | 0          | 0          | 1          | 1
| Handler_read_rnd_next         | 0          | 0          | 82         | 83
| Handler_read_rnd              | 0          | 0          | 0          | 1
| Handler_tmp_write             | 0          | 0          | 67         | 67

The EXPLAIN results are the same in all cases (phpmyadmin/mysqli/putty+mariadb).

    [select_type] => SIMPLE
    [table] => TitelDaggegevens
    [type] => range
    [possible_keys] => fondskosten,Datum+isbn+fondskosten
    [key] => Datum+isbn+fondskosten
    [key_len] => 3
    [ref] => 
    [Extra] => Using index condition; Using filesort

The only difference is in rows:

    [rows] => 422796 for 2020-05-01
    [rows] => 450432 for 2020-05-02

The question

Can you give us any directions in where we should could look to solve this problem? We've worked for a week to optimize the mariadb server (now optimal, except in phpmyadmin) and narrow some of our problems down to the example underneath. We use phpmyadmin a lot but have little to no experience with what is under the surface (like how it connects to the db).

About the indexing/ordering

In the slow query, if we change the ORDER BY from the indexed isbn field to a non-indexed field or leave out the ORDER BY altogether, everything has its normal lightning speed again. Changing the ORDER BY to the primary key id makes it slow too, but still 10x as fast as with the indexed isbn field.

We *know* we can solve this particular query by better indexing, which we already have ready to implement. However, we want to know what causes the different times within phpmyadmin vs mysqli/directly.

The details:

TitelDaggegevens contains < 11mln records, not even 3Gb, and has been OPTIMIZEd (rebuild)

The table structure:

CREATE TABLE `TitelDaggegevens` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `isbn` decimal(13,0) NOT NULL,
 `datum` date NOT NULL,
 `volgendeDatum` date DEFAULT NULL,
 `prijs` decimal(8,2) DEFAULT NULL,
 `prijsExclLaag` decimal(8,2) DEFAULT NULL,
 `prijsExclHoog` decimal(8,2) DEFAULT NULL,
 `stadiumDienstverlening` char(2) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
 `stadiumLevenscyclus` char(1) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
 `gewicht` double(7,3) DEFAULT NULL,
 `volume` double(7,3) DEFAULT NULL,
 `24uurs` tinyint(1) DEFAULT NULL,
 `UitgeverCode` varchar(4) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
 `imprintId` int(11) DEFAULT NULL,
 `distributievormId` tinyint(4) DEFAULT NULL,
 `boeksoort` char(1) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
 `publishingStatus` tinyint(4) DEFAULT NULL,
 `productAvailability` tinyint(4) DEFAULT NULL,
 `voorraadAlles` mediumint(8) unsigned DEFAULT NULL,
 `voorraadBeschikbaar` mediumint(8) unsigned DEFAULT NULL,
 `voorraadGeblokkeerdEigenaar` smallint(5) unsigned DEFAULT NULL,
 `voorraadGeblokkeerdCB` smallint(5) unsigned DEFAULT NULL,
 `voorraadGereserveerd` smallint(5) unsigned DEFAULT NULL,
 `fondskosten` enum('depot leverbaar','depot onleverbaar','POD','BOV','eBoek','geen') COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `ISBN+datum` (`isbn`,`datum`) USING BTREE,
 KEY `UitgeverCode` (`UitgeverCode`),
 KEY `Imprint` (`imprintId`),
 KEY `VolgendeDatum` (`volgendeDatum`),
 KEY `Index op voorraad om maxima snel te vinden` (`isbn`,`voorraadAlles`) USING BTREE,
 KEY `fondskosten` (`fondskosten`),
 KEY `Datum+isbn+fondskosten` (`datum`,`isbn`,`fondskosten`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=16519430 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci 

Configuration of our virtual web+database+mail server:

MariaDB 10.4 
InnoDB
CentOs7 
phpMyAdmin 4.9.5
php 5.6
Apache 

Some important mariadb configuration parameters that we changed from what our virtual webserver had as default:

[mysqld]
innodb_buffer_pool_size=2G
innodb_buffer_pool_instances=4
innodb_flush_log_at_trx_commit=2

tmp_table_size=64M
max_heap_table_size=64M

join_buffer_size=4M
sort_buffer_size=8M

optimizer_search_depth=5

Solution

We've had a specialist look at it, additional to all your tips.

It turned out after MANY tests that the LIMIT 0,25 that phpMyAdmin added was the ONLY thing that caused the extreme delay. The expert could find NO differences between mysqli/phpmyadmin and executing it directly on the mariadb server.

Sometimes a VERY small difference in query (like adding a LIMIT for a query that returns only one record anyway) can cause a query to take 100.000 as long because it wil scan a whole index because the engine will see another strategy fit for that query. That is standard behaviour.

We already had found an index that eliminated this specific problem, nut now we are also assured that there is nothing wrong with our DB. Something we were not sure of because it seemed extreme behaviour. So: much ado about nothing.

HOWEVER I learned such a lot from this experiences. Both from our expert as from this community. I learned about MySQL diagnostics, logging, how mariaDB handles queries... For every diagnosis that turned out not to be the problem, I learned things to avoid or to strive for in tables, indexes or queries.

THANK YOU ALL, especially @Rick James, @Wilson Hauck and @ExploitFate

Answered By - Roemer