ernest wrote:
> Tobin Harris wrote:
>
> SELECT tableA.*, tableB.*, Count(DISTINCT tableA.session_id_initial) AS CountOfDistinctSII,
> SUM(tableA.visit_duration) AS CountOfVisitDuration,
> SUM(CASE WHEN session_state='n' THEN 1 ELSE 0 END) AS CountOfSessions,
> Count(tableA.pid) AS CountOfPID
> FROM tableB
> INNER JOIN tableA ON tableB.pid_id = tableA.pid
> WHERE
> (((tableA.datestamp)>='06-01-2005') AND
> ((tableA.datestamp)<='07-01-2005'))
> GROUP BY tableB.fullname
Some comments:
1) leave out the tableA.* and tableB.* columns. With the exception of tableB.fullname, this data is meaningless (see
http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html)
2) Is the tableA.datestamp defined as datatype DATE? If yes, you need the literals '2005-01-06' and '2005-01-07'. If no, the date-comparisons will not work
3) Use WHERE tableA.datestamp BETWEEN '2005-01-06' AND '2005-01-07'
> After running EXPLAIN, I got the following:
>
> table type possible_keys key key_len ref rows Extra
> tableB ALL NULL NULL NULL NULL 117 Using temporary; Using filesort
> tableA ALL NULL NULL NULL NULL 434836 Using where
Your query isn't using any indexes. You need an index on tableA.datestamp and on tableB.fullname:
ALTER TABLE tableA ADD INDEX (datestamp);
ALTER TABLE tableB ADD INDEX (fullname);
--
felix
Please use
BBCode to format your messages in this forum.