phpmyadmin Question posted in
The official documentation can be found here.

MySQL – just adding ORDER BY an indexed field adds 5 minutes for just 52 records. Where to start? – Phpmyadmin

EDIT 2: now that we have optimized the db and narrowed down in MySQL – Why is phpMyAdmin extremely slow with this query that is super fast in php/mysqli?

EDIT 1: there are two solutions that helped us. One on database level (configuration) and one on query level. I could of course only accept one as the best answer, but if you are having similar problems, look at both.

We have a database that has been running perfectly fine for years. However, right now, we have a problem that I don’t understand. Is it a mysql/InnoDB configuration problem? And we currently have nobody for system maintenance (I am a programmer).

The tabel TitelDaggegevens is a few Gigs in size, about 12,000,000 records, so nothing extraordinary.

If we do: 

SELECT * 
  FROM TitelDaggegevens 
 WHERE fondskosten IS NULL 
   AND (datum BETWEEN 20200401 AND 20200430)

it runs fine, within a few tenths of a second.

The result: 52 records.

Also if we add ORDER BY datum or if we order by any other non-indexed field: all is well, same speed.

However, if I add ORDER BY id (id being the primary key), suddenly the query takes 15 seconds for the same 52 records.

And when I ORDER BY another indexed field, the query-time increases tot 4-6 minutes. For ordering 52 records. On an indexed field.

I have no clue what is going on. EXPLAIN doesn’t help me. I optimized/recreated the table, checked it, and restarted the server. All to no avail. I am absolutely no expert on configuring MySQL or InnoDB, so I have no clue where to start the search.

I am just hoping that maybe someone recognises this and can point me into the right direction.

SHOW TABLE STATUS WHERE Name = 'TitelDaggegevens'
Gives me:

SHOW TABLE STATUS WHERE Name = 'TitelDaggegevens'

I know this is a very vague problem, but I am not able to pin it down more specifically. I enabled the logging for slow queries but the table slow_log stays empty. I’m lost.

Thank you for any ideas where to look.

This might be a help to someone who knows something about it, but not really to me, phpmyadmins ‘Advisor’:

Advisor

In the comments and a reaction were asked for EXPLAIN outputs:

1) Without ORDER BY and with ORDER BY datum (which is in the WHERE and has an index):

Without orderby

2) With ORDER BY plus any field other than datum (indexed or not, so the same for both quick and slow queries).

With order by with indexed fields

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
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    For people searching for tweaks in similar cases, these are the tweaks the specialist made to the db that sped it up considerably (mind you this is for a database with 100s of tables and MANY very complex and large queries sometimes joining over 15 tables but not super massive number of records. The database is only 37 gigabytes.

    [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

    The optimizer_search_depth was DECREASED to minimize the time the optimizer needs for the complex queries.

    After restarting the server, (regularly) run all queries that are the result of running this query:

    SELECT CONCAT('OPTIMIZE TABLE `', TABLE_SCHEMA , '`.`', TABLE_NAME ,'`;') AS query
FROM INFORMATION_SCHEMA.TABLES
WHERE DATA_FREE/DATA_LENGTH > 2 AND DATA_LENGTH > 4*1024*1024

    (This first one better when the server is off line or has low use if you have large tables. It rebuilds and thus optimizes the tables that need it.)

    And then:

    SELECT CONCAT('ANALYZE TABLE `', TABLE_SCHEMA , '`.`', TABLE_NAME ,'`;') AS query
FROM INFORMATION_SCHEMA.TABLES
WHERE DATA_FREE/DATA_LENGTH > 2 AND DATA_LENGTH > 1*1024*1024

    (This second querie-series is much lighter and less infringing but may still help speed up some queries by recalculating query strategies by the server.)


  2. 0

    Looks like ORDER BY uses 3 different optimization plans

    1. ORDER BY id – Extra: Using index condition; Using where; Using filesort. MySQL uses filesort to resolve the ORDER BY. But rows are sorted already. So, it takes 15 second.
    2. ORDER BY Datum or other non-indexed field – Extra: Using index condition; Using where. MySQL uses Datum index to resolve the ORDER BY. It takes few seconds.
    3. ORDER BY index_field – Extra: Using index condition; Using where; Using filesort. MySQL uses filesort to resolve the ORDER BY. Rows are unsorted. It takes few minutes.

    It’s my suggestion. Only EXPLAIN can tells what’s going on

    Influencing ORDER BY Optimization

    UPD:
    Could you check this query with every ORDER BY clauses?

    SELECT * 
  FROM TitelDaggegevens USE INDEX FOR ORDER BY (Datum)
 WHERE fondskosten IS NULL 
   AND (Datum BETWEEN 20200401 AND 20200430)

    Also you may try to increasing the sort_buffer_size

    If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing.
    On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values.

    Login or Signup to reply.
  3. 0

    1. Have this to handle the WHERE entirely:

      INDEX(fondskosten, Datum)

    Note: the = is first, then the range.

    1. Fetch the *. Note: If there are big TEXT or BLOB columns that you don’t need, spell out the SELECT list so you can avoid them. They may be stored “off-record”, hence take longer to fetch.

    2. An optional ORDER BY. If it is on Datum, then there is no extra effort. If it is on any other column, then there will be a sort. But a sort of 52 rows will be quite fast (milliseconds).

    Notes:

    • If you don’t have fondskosten IS NULL or you have some other test, then all bets are off. We have to start over in designing the optimal composite index.
    • USE/FORCE INDEX — use this as a last resort.
    • Always provide SHOW CREATE TABLE when needing to discuss a query.
    • The Advisor has some good stuff, but without any clues of what is “too big”, it is rather useless.
    • I suspect all the other discussions failed to realize that there are far more than 52 rows for the given Datum range. That is fondskosten IS NULL is really part of the problem and solution.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search