I have the following table:

  `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

  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;

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?

  • 1
    How many rows in the result set?
    – Rick James
    Commented Feb 4, 2020 at 20:18
  • @RickJames - On average the query returns about 40 rows. Max is about 2,400. In the query I provide there are a huge number of results for nameRef = 1, maybe 50,000; and then the category = "country" narrows it down to XX rows on average. Commented Feb 6, 2020 at 0:12
  • (Thanks; my suggested 4-column index still stands as probably optimal.)
    – Rick James
    Commented Feb 6, 2020 at 0:16

2 Answers 2


There are two problems


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


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.

  • Thanks. I am converting to InnoDB to see if there is a difference, but I am at 160,000 seconds so far... Problem is I took out a 12 month server contract and now it appears it may not work... Commented Feb 3, 2020 at 16:07
  • It is up to the OS to cache data blocks (.MYD); so it is not always a disk hit.
    – Rick James
    Commented Feb 4, 2020 at 20:18

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.

  • My local server has 16GB; and the remote has 32GB. By remote I just meant not local. I was testing the query via the mysql command on SSH. I thought about adding such an index. I will try. I have been converting the table to InnoDB, but so far it's not complete after 4-5 days. Will likely have to get a different server, but the host has not replied in 2-3 days. Commented Feb 6, 2020 at 0:16
  • @KohjahBreese - Because of the size of the table, the smaller machine may need more disk hits.
    – Rick James
    Commented Feb 6, 2020 at 0:18
  • @KohjahBreese - 4-5 days for an ALTER TABLE of this size is unreasonable. Please provide specifics on the conversion.
    – Rick James
    Commented Feb 6, 2020 at 0:19
  • It's ALTER TABLE Geo1 ENGINE = INNODB. The state is copy to tmp table. Currently at 5 days 5 hours. I'll be moving to an SSD server tomorrow. Commented Feb 7, 2020 at 2:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.