SQL algorithm to as near to linear time as possible and tweaking of select statement

Question

I am using MySQL version 5.5 on Ubuntu.
My database tables are setup as follows:

DDLs:

    CREATE TABLE 'asx' (
        'code' char(3) NOT NULL,
        'high' decimal(9,3),
        'low' decimal(9,3),
        'close' decimal(9,3),
        'histID' int(11) NOT NULL AUTO_INCREMENT,
        PRIMARY KEY ('histID'),
        UNIQUE KEY 'code' ('code')
    )

    CREATE TABLE 'asxhist' (
       'date' date NOT NULL,
       'average' decimal(9,3),
       'histID' int(11) NOT NULL,
       PRIMARY KEY ('date','histID'),
       KEY 'histID' ('histID'),
       CONSTRAINT 'asxhist_ibfk_1' FOREIGN KEY ('histID') REFERENCES 'asx' ('histID') 
       ON UPDATE CASCADE
    )

t1:

| code  | high   | low    | close  | histID (primary key)| 
| asx   | 10.000 | 9.500  | 9.800  | 1      
| nab   | 42.000 | 41.250 | 41.350 | 2      

t2: 

| date      | average | histID (foreign key) |
| 2013-01-01| 10.000  | 1                    |
| 2013-01-01| 39.000  | 2                    |
| 2013-01-02| 9.000   | 1                    |
| 2013-01-02| 38.000  | 2                    |
| 2013-01-03| 9.500   | 1                    |
| 2013-01-03| 39.500  | 2                    |
| 2013-01-04| 11.000  | 1                    |
| 2013-01-04| 38.500  | 2                    |

I am attempting to complete a select query that produces this as a result:

| code | high   | low    | close  | asxhist.average | 
| asx  | 10.000 | 9.500  | 9.800  | 11.000, 9.5000  |
| nab  | 42.000 | 41.250 | 41.350 | 38.500,39.500   |

Where the most recent information in table 2 is returned with table 1 in a csv format. I have managed to get this far:

    SELECT code, high, low, close, 
    (SELECT GROUP_CONCAT(DISTINCT t2.average ORDER BY date DESC SEPARATOR ',') FROM t2 
    WHERE t2.histID = t1.histID)
    FROM t1;

Unfortunately this returns all values associated with hID. I'm taking a look at xaprb.com's firstleastmax-row-per-group-in-sql solution but I have been banging my head all day and the slight wooziness seems to be dimming my ability to comprehend how I should use it to my benefit. How can I limit the results to the most 5 recent values and considering the tables will eventually be megabytes in size, try and remain in O(n²) or less? (Or can I?)

Answer 1

Temporary work around using SUBSTRING_INDEX and not a feasible solution for huge data

SELECT code, high, low, close,
    (SELECT SUBSTRING_INDEX(GROUP_CONCAT(asxhist.average), ',', 3)
        FROM asxhist
        WHERE asxhist.histID = asx.histID
        ORDER BY date DESC)
FROM asx;

From what I gather Limit option in GROUP_CONCAT is still under feature-request.

Also on stackoverflow hack MySQL GROUP_CONCAT