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

score 2 · Accepted Answer · answered Jun 13 '14 at 11:30

2

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.

answered Jun 13 '14 at 11:30

tmcallaghan

1,292
2
10
20

Thank you for your answer... where I can know if Tokutek is working on it or if there is any alternative solution available? – rasputino Jun 13 '14 at 14:47
You'll see progress via the link I provided. A workaround would be to export the data to a flat file and load it using "LOAD DATA INFILE ...", which I'd only recommend if the table is large. I'm curious as to why you'd be putting that many rows into a temporary table. – tmcallaghan Jun 14 '14 at 15:25
Is a stored procedure that extracts information for a report. I was testing TokuDB beacuse I was thinking in change all the database to TokuDB (a 100GB database) and everything worked really good and faster but this report. – rasputino Jun 15 '14 at 21:45
As I can see in https://tokutek.atlassian.net/browse/DB-379, the problem is fixed for the next 7.5.0 version, anyone knows when could be this release published in mariadb? – rasputino Sep 18 '14 at 10:48
I assume you mean MariaDB 10.x. Not sure when they plan on taking a new cut of TokuDB, you should ask them in their IRC channel or on their dev mailing list. – tmcallaghan Sep 19 '14 at 13:39

Creating temporary table from TokuDB query too slow

1 Answers1