Let's start by dissecting the original query, based on the provided example data:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Stocks
    (`id` int, `ST_StockCode` int, `SM_StockCode` int, `ST_ItemSize` int)
;

INSERT INTO Stocks
    (`id`, `ST_StockCode`, `SM_StockCode`, `ST_ItemSize`)
VALUES
    (1, 679783, 678649, 7.00),
    (2, 679789, 688622, 7.00),
    (3, 679792, 679793, 8.00),
    (4, 679792, 686376, 8.00),
    (5, 679793, 679792, 7.00),
    (6, 679793, 686376, 8.00),
    (7, 679795, 679796, 8.00),
    (8, 679796, 679795, 7.00),
    (9, 679797, 617114, 7.00),
    (10, 679797, 627339, 7.00)
;

Query 1:

SELECT 
        t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize`
    FROM
        `stocks` AS t1,
        `stocks` AS t2
    WHERE
        t1.`ST_StockCode` = t2.`SM_StockCode`
    GROUP BY t1.`ST_StockCode`
    ORDER BY t1.`id` ASC

Results:

| ST_STOCKCODE | SM_STOCKCODE | ST_ITEMSIZE |
|--------------|--------------|-------------|
|       679792 |       679792 |           7 |
|       679793 |       679793 |           8 |
|       679795 |       679795 |           7 |
|       679796 |       679796 |           8 |

First thing to note is that, this being an inner join, the two values ST_StockCode and SM_StockCode are always identical. In the following examples I therefore mention only one of them as it is easy to add the missing one back in.

Second, this query is using a non SQL Standard extension to the GROUP BY clause. The query groups by ST_StockCode and then references two other columns without an aggregate. MySQL will return for those columns the first value it comes across. It does not check if the values are distinct and you might get different results each execution. If we assume that the original query writer was aware of this behavior we can do this instead:

Query 2:

SELECT SM_StockCode, MIN(ST_ItemSize) ST_ItemSize
  FROM stocks AS t1
 GROUP BY SM_StockCode;

Results:

| SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       617114 |           7 |
|       627339 |           7 |
|       678649 |           7 |
|       679792 |           7 |
|       679793 |           8 |
|       679795 |           7 |
|       679796 |           8 |
|       686376 |           8 |
|       688622 |           7 |

This returns for each SM_StockCode the minimum ST_ItemSize (instead of a random one). However, it returns potentially more rows then the original query because there might be SM_StockCodes that do not have a matching ST_StockCode (as in the provided example data). However, that is easily fixed:

Query 3:

SELECT SM_StockCode, MIN(ST_ItemSize) ST_ItemSize
  FROM stocks AS t1
 WHERE EXISTS(SELECT 1 FROM stocks AS t2 WHERE t2.ST_StockCode = t1.SM_StockCode)
 GROUP BY SM_StockCode;

Results:

| SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       679792 |           7 |
|       679793 |           8 |
|       679795 |           7 |
|       679796 |           8 |

Now only SM_StockCodes that have a matching ST_StockCodes are returned. (You have to duplicate the SM_StockCode column if you really need that value twice.)

With the query written this way, the indexing strategy becomes fairly obvious:

Create an index on ST_StockCode to support the EXISTS lookup and create a second index on SM_StockCode, ST_ItemSize to support the GROUP BY.

If you are sure that every SM_StockCode has a matching ST_StockCode (for example because a foreign key has been declared between the two, or because two of the analysts swear that this is always the case) you can shorten the query even more:

Query 4:

SELECT DISTINCT SM_StockCode, ST_ItemSize
  FROM stocks;

Results:

| SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       678649 |           7 |
|       688622 |           7 |
|       679793 |           8 |
|       686376 |           8 |
|       679792 |           7 |
|       679796 |           8 |
|       679795 |           7 |
|       617114 |           7 |
|       627339 |           7 |

However, as in the example data excerpt of 10 random rows this condition is not given, the above result does not match in this case.

Take advantage of the index for each table doing something like this:

(SELECT  MIN(`time`) `time` FROM  `eventlog` WHERE userid =  '9819') UNION ALL
(SELECT  MIN(`time`) FROM  `screenshots` WHERE userid =  '9819')     UNION ALL
(SELECT  MIN(`time`) FROM  `sitelog` WHERE userid =  '9819')         UNION ALL
(SELECT  MIN(`time`) FROM  `keylog` WHERE userid =  '9819')          UNION ALL
(SELECT  MIN(`time`) FROM  `webcamcaps` WHERE userid =  '9819')
ORDER BY  `time` ASC 
LIMIT 1;

or

(SELECT  `time` FROM  `eventlog` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT  `time` FROM  `screenshots` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT  `time` FROM  `sitelog` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT  `time` FROM  `keylog` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
UNION ALL
(SELECT  `time` FROM  `webcamcaps` WHERE userid =  '9819' ORDER BY time ASC LIMIT 1)
ORDER BY  `time  ASC 
LIMIT 1;

The main idea is to get each subquery to return 1 row.

Give it a Try !!!