MySQL Forums
Forum List  »  Optimizer & Parser

Group by Query Optimization
Posted by: TiMoch TiMoch
Date: January 03, 2005 04:02AM


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 = ''
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.


Options: ReplyQuote

Written By
Group by Query Optimization
January 03, 2005 04:02AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.