0

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?

  • 1
    How many rows in the result set? – Rick James Feb 4 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. – Kohjah Breese Feb 6 at 0:12
  • (Thanks; my suggested 4-column index still stands as probably optimal.) – Rick James Feb 6 at 0:16
2

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.

|improve this answer|||||
  • 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... – Kohjah Breese Feb 3 at 16:07
  • It is up to the OS to cache data blocks (.MYD); so it is not always a disk hit. – Rick James Feb 4 at 20:18
2

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.

|improve this answer|||||
  • 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. – Kohjah Breese Feb 6 at 0:16
  • @KohjahBreese - Because of the size of the table, the smaller machine may need more disk hits. – Rick James Feb 6 at 0:18
  • @KohjahBreese - 4-5 days for an ALTER TABLE of this size is unreasonable. Please provide specifics on the conversion. – Rick James Feb 6 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. – Kohjah Breese Feb 7 at 2:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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