Group by Query Optimization

[TiMoch TiMoch]

Hi

I work on a web server logging system. I'm trying to count the number of unique access (session count) for a particular virtual host grouped by a session related info (channel). The problem I have is with a sort file that grows over 1.6G and fills up the temporary partition. I don't understand why MySQL takes so much space since the data I am working on is quite a small subset.

This might seem obscure so here are the (simplified) tables :

tRequest -> (request id, timestamp, session id, virtual host id) there is one for each request so the table is big (14.000.000 lines)
tQSess -> (session id, channel id) one per user and per visit (~ 900.000 lines)
tChannel -> (channel id, channel name) just a very few (50 lines)
tVHost -> (vhost id, vhost name) just a very few (50 lines)

the references between the tables are as follow:

tVHost <- (vhost id) tRequest -> tQSess (session id) -> tChannel (channel id)

here is the request I used

SELECT SUBSTRING(r1.req_t_date FROM 1 FOR 8) AS Date,
IF(c.cha_s_name IS NULL,"No Channel",c.cha_s_name) AS Channel,
COUNT(DISTINCT r1.ses_i_id) as NBAccess
FROM tRequest r1
LEFT JOIN tVHost vh ON r1.vho_i_id = vh.vho_i_id
LEFT JOIN tQSess s ON r1.ses_i_id = s.ses_i_id
LEFT OUTER JOIN tChannel c ON s.cha_i_id = c.cha_i_id
WHERE req_t_date BETWEEN 20041205000000 AND 20041211000000
AND vho_s_vhost = 'some.vhost.com'
GROUP BY Date, Channel
ORDER BY Date, Channel;

The optimizer uses a temporary table and a file sort to do the query. The temporary table takes 60M (~ 1.000.000 lines from tRequest) but the sort file fills up the temporary disk and the request fails.

I tried creating a temporary table myself exporting only the data I actually need but I can't get it to work unless I remove the DISTINCT.

If you find anything I did wrong or if you have an explanation, I'd greatly apreciate it.

Thanks