Query on large database much slower on HDD 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?
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.