Query on large database much slower on HDD

Question

I have the following table:

CREATE TABLE `Geo1` (
  `id` int(8) UNSIGNED NOT NULL,
  `nameRef` int(8) DEFAULT NULL,
  `placeRef` mediumint(7) UNSIGNED DEFAULT NULL,
  `category` enum(...) COLLATE utf8_bin DEFAULT NULL,
  `parentRef` mediumint(7) DEFAULT NULL,
  `incidence` int(9) UNSIGNED NOT NULL,
  `percent` decimal(11,9) UNSIGNED DEFAULT NULL,
  `ratio` int(11) NOT NULL,
  `rank` mediumint(7) UNSIGNED DEFAULT NULL,
  `year` smallint(4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `Geo1`
  ADD PRIMARY KEY (`id`),
  ADD KEY `placeRef_rank` (`placeRef`,`rank`),
  ADD KEY `nameRef_parentRef` (`nameRef`,`parentRef`),
  ADD KEY `nameRef_placeRef` (`nameRef`,`placeRef`),
  ADD KEY `nameRef_category_year` (`nameRef`,`category`,`year`) USING BTREE;
MODIFY `id` int(8) UNSIGNED NOT NULL AUTO_INCREMENT;

The table is 55GB in total, 36GB of indexes.

On my local server (which uses SSD) it can execute the following query in 0.001 seconds:

SELECT SQL_NO_CACHE placeRef, incidence FROM Geo1
    WHERE nameRef = 1
      AND category = "country";

However on a remote server (which uses HDD) it takes about 0.1-0.6 seconds. This server has 32GB RAM. Here is the mysql.cnf.

The EXPLAIN result on both servers are identical.

Any ideas as to why this is so slow?

score 2 · Answer 1 · edited Jun 15 '20 at 09:05

There are two problems

PROBLEM #1 : MyISAM

MyISAM does not cache data (stored in the .MYD) which means data must be read for disk every time. Indexes may be cached (read once from .MYI), but not data.

See my old post in What are the main differences between InnoDB and MyISAM? under the MyISAM subheading

PROBLEM #2 : HDD vs SSD

An HDD must use spindles to move its read/write heads across a disk

An SSD is a random read/write device. It zeros in on blocks to disk faster than HDD.

Rick James · Answer 2 · 2020-02-04T21:01:39.713

Rule of Thumb: A disk hit on HDD takes about 10ms (100 hits/second). SSD access might be 10 times that fast.

"0.1-0.6" on HDD -- perhaps 10 to 60 blocks needed to be fetched. That might be only one for the index, then several for the data. Perhaps there are at least 60 rows in the resultset?

You say that the slow version is "remote". This might add a little to the time.

0.001 seconds usually indicates that the resultset was fetched from the Query cache. But you have SSD and SQL_NO_CACHE, so that is ruled out. The conclusion is that all the necessary blocks happened to be in memory (key_buffer for .MYI or OS for .MYD)

SELECT SQL_NO_CACHE placeRef, incidence FROM Geo1
    WHERE nameRef = 1
      AND category = "country";

A "covering index" might speed up the query significantly:

INDEX(category, nameRef,   -- in either order
      placeRef, incidence) -- in either order

Then the query would be performed in the key_buffer since everything needed is in the INDEX.

You really should switch to InnoDB, though I don't see that it would make any difference for this query.

Do both servers have 32GB of RAM? If not, that would factor into the caching.

Query on large database much slower on HDD

2 Answers2

PROBLEM #1 : MyISAM

PROBLEM #2 : HDD vs SSD