Concat and substr functions need to be optimized
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
InfiniDB Community |
In Progress
|
Undecided
|
Unassigned |
Bug Description
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/
1000000001|
1000000002|
1000000003|
1000000004|
1000000005|
1000000006|
1000000007|
1000000008|
1000000009|
1000000010|
[# tail /usr/local/
2011587383|
2011587384|
2011587385|
2011587386|
2011587387|
2011587388|
2011587389|
2011587390|
2011587391|
2011587392|
Ran the following two queries with both tables.
Query 1:
select round(dt_
max(bid), max(trade),
min(ask),
-- substr(
-- substr(
count(*)
from trades group by 1 order by 1;
Query 2:
select round(dt_
max(bid), max(trade),
min(ask),
substr(
substr(
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
Changed in infinidb: | |
status: | New → In Progress |