Query with complicated use of concat, round, and ceil functions slower on InfiniDB than MyISAM

[wweeks]

Performance issue, there is a non-linear component to adding expressions:

selected between 1 and 8 identical column expressions and trended the time. The
first took 3.25 seconds, the eight took ~40 incremental seconds over the 7th
expession. If we can do one expression in ~3 seconds, then 8 expressions
against the same column should take ~24 seconds.

Expression 1: max(CEIL( (bid - 0) / 86400000 ) - 1 )
1 row in set (3.25 sec) ~ 3 seconds
1 row in set (9.97 sec) ~ 7 addl. seconds
1 row in set (20.70 sec) ~ 10 addl. seconds
1 row in set (36.11 sec) ~ 14 addl. seconds
1 row in set (55.41 sec) ~ 19 addl. seconds
1 row in set (1 min 20.96 sec) ~ 25 addl. seconds
1 row in set (1 min 50.60 sec) ~ 30 addl. seconds
1 row in set (2 min 30.09 sec) ~ 40 addl. seconds

Expression 2: max(bid -0)
1 row in set (3.26 sec) ~ 3 seconds
1 row in set (4.44 sec) ~ 1 addl. seconds
1 row in set (8.10 sec) ~ 4 addl. seconds
1 row in set (13.35 sec) ~ 5 addl. seconds
1 row in set (19.85 sec) ~ 6 addl. seconds
1 row in set (27.91 sec) ~ 8 addl. seconds
1 row in set (36.66 sec) ~ 9 addl. seconds
1 row in set (47.91 sec) ~ 11 addl. seconds

Without expression, the curve is relatively flat: 1 agggregation of 1 column is
.82 seconds, 2 aggregations of 1 column is 1.14, 3 is 1.5 and 4 is 1.57
seconds.

max(CEIL( (bid - 0) / 86400000 ) - 1 ) c1,
. . .
max(CEIL( (bid - 0) / 86400000 ) - 1 ) c8
from trades ;

mysql> desc trades;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| dt_time | bigint(20) | YES | | NULL | |
| bid | decimal(8,4) | YES | | NULL | |
| ask | decimal(8,4) | YES | | NULL | |
| trade | decimal(8,4) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> SELECT max(CEIL( (bid - 0) / 86400000 ) - 1 ) c1
    -> from trades ;
+------+
| c1 |
+------+
| 0 |
+------+
1 row in set (3.25 sec)

mysql>
mysql> SELECT max(CEIL( (bid - 0) / 86400000 ) - 1 ) c1,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c2
    -> from trades ;
+------+------+
| c1 | c2 |
+------+------+
| 0 | 0 |
+------+------+
1 row in set (9.97 sec)

mysql>
mysql> SELECT max(CEIL( (bid - 0) / 86400000 ) - 1 ) c1,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c2,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c3
    -> from trades ;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 0 | 0 | 0 |
+------+------+------+
1 row in set (20.70 sec)

mysql>
mysql> SELECT max(CEIL( (bid - 0) / 86400000 ) - 1 ) c1,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c2,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c3,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c4
    -> from trades ;
+------+------+------+------+
| c1 | c2 | c3 | c4 |
+------+------+------+------+
| 0 | 0 | 0 | 0 |
+------+------+------+------+
1 row in set (36.11 sec)

mysql>
mysql> SELECT max(CEIL( (bid - 0) / 86400000 ) - 1 ) c1,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c2,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c3,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c4,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c5
    -> from trades ;
+------+------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+------+------+------+------+------+
| 0 | 0 | 0 | 0 | 0 |
+------+------+------+------+------+
1 row in set (55.41 sec)

mysql>
mysql> SELECT max(CEIL( (bid - 0) / 86400000 ) - 1 ) c1,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c2,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c3,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c4,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c5,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c6
    -> from trades ;
+------+------+------+------+------+------+
| c1 | c2 | c3 | c4 | c5 | c6 |
+------+------+------+------+------+------+
| 0 | 0 | 0 | 0 | 0 | 0 |
+------+------+------+------+------+------+
1 row in set (1 min 20.96 sec)

mysql>
mysql> SELECT max(CEIL( (bid - 0) / 86400000 ) - 1 ) c1,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c2,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c3,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c4,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c5,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c6,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c7
    -> from trades ;
+------+------+------+------+------+------+------+
| c1 | c2 | c3 | c4 | c5 | c6 | c7 |
+------+------+------+------+------+------+------+
| 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+------+------+------+------+------+------+------+
1 row in set (1 min 50.60 sec)

mysql>
mysql> SELECT max(CEIL( (bid - 0) / 86400000 ) - 1 ) c1,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c2,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c3,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c4,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c5,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c6,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c7,
    -> max(CEIL( (bid - 0) / 86400000 ) - 1 ) c8
    -> from trades ;
+------+------+------+------+------+------+------+------+
| c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 |
+------+------+------+------+------+------+------+------+
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+------+------+------+------+------+------+------+------+
1 row in set (2 min 30.09 sec)

[wweeks (wweeks)]

Resolved in 1.0.3.