TokuDB sorting time different between ASC vs DESC

Question

This is MariaDB + TokuDB 7.1 community downloaded from Tokutek. Please accept my ignorance if this is normal behavior but I have a question about sorting results. I'm experiencing huge time difference in sorting between the two sort directions - ascending and descending:

SELECT sql_no_cache id, createts, deleted
FROM sort_test
WHERE createts > '2000098'
ORDER BY createts asc

+---------+----------+---------+
| id      | createts | deleted |
+---------+----------+---------+
| 1999999 |  2000099 |    NULL |
| 2000000 |  2000100 |    NULL |
+---------+----------+---------+
2 rows in set (0.00 sec)

SELECT sql_no_cache id, createts, deleted
FROM sort_test
WHERE createts > '2000098'
ORDER BY createts desc

+---------+----------+---------+
| id      | createts | deleted |
+---------+----------+---------+
| 2000000 |  2000100 |    NULL |
| 1999999 |  2000099 |    NULL |
+---------+----------+---------+
2 rows in set (0.55 sec)

Below I present my simplified test case. Here is the table:

CREATE TABLE `sort_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createts` int(11) DEFAULT NULL,
  `deleted` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_createts` (`createts`)
) ENGINE=TokuDB

Here I populate the table with 2 million rows using this procedure:

delimiter ;;

drop procedure if exists sort_test_populate;;

create procedure sort_test_populate()
begin
DECLARE int_val INT DEFAULT 1;
myloop : LOOP
  if (int_val > 2000000) THEN
    LEAVE myloop;
  end if;
  insert into sort_test (id, createts) values (int_val, int_val+100);
  set int_val = int_val +1;
end loop;
end;;

call sort_test_populate();;
Query OK, 1 row affected (28 min 2.80 sec)

Here are my test queries again:

SELECT sql_no_cache id, createts, deleted
FROM sort_test
WHERE createts > '2000098'
ORDER BY createts asc

2 rows in set (0.00 sec)

SELECT sql_no_cache id, createts, deleted
FROM sort_test
WHERE createts > '2000098'
ORDER BY createts desc

2 rows in set (0.55 sec)

And here is the "explain extended" result, it's identical for both queries:

+------+-------------+-----------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id   | select_type | table     | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+------+-------------+-----------+-------+---------------+--------------+---------+------+------+----------+-------------+
|    1 | SIMPLE      | sort_test | range | idx_createts  | idx_createts | 5       | NULL |    2 |   100.00 | Using where |
+------+-------------+-----------+-------+---------------+--------------+---------+------+------+----------+-------------+

Please note that this is not my exact data I'm working with, that would be too much to include here. I just wanted to create some test data to demonstrate the problem. My question is - why it's behaving like this and how to make the descending order query faster?

If I use `WHERE createts < 103` instead then the performance is excellent for both ASC and DESC sorting. — user1765811, Feb 05 '14 at 20:43
I suspect that you're hitting a node boundary issue with the index where TokuDB must visit two nodes to retrieve your results. — Marcus Adams, Feb 05 '14 at 21:23
Have you tried the MySQL version of TokuDB to see if the same issue occurs? — tmcallaghan, Feb 06 '14 at 00:53
I've just installed the mysql version of TokuDB 7.1 from Tokutek and the problem does not occur. I get equally good test query times (0.00 sec) for both ASC and DESC sorting. Also, the problem does not occur at all if using myisam tables in either version (mysql or mariadb). — user1765811, Feb 07 '14 at 12:50

score 1 · Accepted Answer · answered Feb 07 '14 at 18:46

1

This is a known bug with Index Condition Pushdown (ICP). The workaround is to disable ICP by setting the optimizer_switch either globally or within the session executing this query.

mysql> SET optimizer_switch='index_condition_pushdown=off';

(full disclosure, I'm an employee at Tokutek, makers of TokuDB)

answered Feb 07 '14 at 18:46

tmcallaghan

1,292
2
10
20

Yes, that workaround fixes my test case queries and also my real life queries where I was experiencing the same problem. – user1765811 Feb 07 '14 at 20:36

TokuDB sorting time different between ASC vs DESC

1 Answers1