Creating temporary table from TokuDB query too slow

Question

I have this table in one server:

CREATE TABLE `mh` (
  `M` char(13) NOT NULL DEFAULT '',
  `F` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `D` char(6) DEFAULT NULL,
  `A` int(11) DEFAULT NULL,
  `DC` char(13) DEFAULT NULL,
  `S` char(22) DEFAULT NULL,
  `S0` int(11) DEFAULT NULL,
  PRIMARY KEY (`F`,`M`),
  KEY `IDX_S` (`S`),
  KEY `IDX_M` (`M`),
  KEY `IDX_A` (`M`,`A`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1;

And the same table but using MyISAM engine in another similar server.

When I execute this query:

CREATE TEMPORARY TABLE temp
(S VARCHAR(22) PRIMARY KEY)
AS
(
    SELECT S, COUNT(S) AS HowManyS
    FROM mh
    WHERE A = 1 AND S IS NOT NULL
    GROUP BY S
);

The table has 120 millions of rows. The server using TokuDB executes the query in 3 hours... the server using MyISAM in 22 minutes.

The query using TokuDB shows a "Queried about 38230000 rows, Fetched about 303929 rows, loading data still remains" status.

Why TokuDB query duration take so long? TokuDB is a really good engine, but I don't know what I'm doing wrong with this query

The servers are using a MariaDB 5.5.38 server

Answer 1

TokuDB is not currently using it's bulk-fetch algorithm on this statement, as noted in https://github.com/Tokutek/tokudb-engine/issues/143. I've added a link to this page so it is considered as part of the upcoming effort.