Concat and substr functions need to be optimized

[wweeks]

The addition of concat or substr to a query in InfiniDB is very expensive. Here is an example from a server with 8 cores comparing InfiniDB times vs MyISAM times.

Created the trades table shown below in MyISAM and InfiniDB and loaded each with 50 million rows.

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 | |
+---------+--------------+------+-----+---------+-------+

Sample rows:
# head /usr/local/Calpont/data/bulk/data/import/trades.tbl
1000000001|7.3014|7.2955|7.2955|
1000000002|6.9540|6.9506|6.9506|
1000000003|7.1176|7.1167|7.1167|
1000000004|9.5666|9.5582|9.5582|
1000000005|7.3394|7.3335|7.3335|
1000000006|1.1519|1.1485|1.1485|
1000000007|10.5299|10.5290|10.5290|
1000000008|7.1543|7.1459|7.1459|
1000000009|7.0604|7.0545|7.0545|
1000000010|6.4769|6.4735|6.4735|
[# tail /usr/local/Calpont/data/bulk/data/import/trades.tbl
2011587383|1.2257|1.2248|1.2248|
2011587384|1.2534|1.2450|1.2450|
2011587385|1.1960|1.1901|1.1901|
2011587386|1.3125|1.3091|1.3091|
2011587387|1.1238|1.1229|1.1229|
2011587388|1.2020|1.1936|1.1936|
2011587389|1.1357|1.1298|1.1298|
2011587390|1.1781|1.1747|1.1747|
2011587391|1.1665|1.1656|1.1656|
2011587392|1.1811|1.1727|1.1727|

Ran the following two queries with both tables.

Query 1:
select round(dt_time/86000000),
     max(bid), max(trade),
     min(ask),
-- substr(min(concat(dt_time, ask)),11,10),
-- substr(max(concat(dt_time, ask)),11,10),
     count(*)
     from trades group by 1 order by 1;

Query 2:
select round(dt_time/86000000),
     max(bid), max(trade),
     min(ask),
     substr(min(concat(dt_time, ask)),11,10),
     substr(max(concat(dt_time, ask)),11,10),
     count(*)
     from trades group by 1 order by 1;

The additional time for query2 was disproportionally expensive in InfiniDB relative to the additional time in MyISAM.

 MyISAM InfiniDB
Query 1 95.42 4.42
Query 2 145.96 145.95