Re: MySQL Performance or Just me? :(

[Ernest Semerda]

Felix Geerinckx wrote:
> Ernest Semerda wrote:
>
> > performance hasent changed :(
>
> Weird. Could you post the result of (after running
> ANALYZE TABLE table) for both tables
>
> DESCRIBE table \G
> SHOW INDEX FROM table \G
>
> and the explain of your query again.
>


(a) SQL Query:

SELECT tableA.pid, tableB.pid_url, tableB.pid_fullname, tableB.pid_area1, tableB.pid_area2, 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 BETWEEN '2005-06-01' AND '2005-07-01' GROUP BY tableB.pid_fullname


(b) EXPLAIN returns:

table type possible_keys key key_len ref rows Extra
tableB ALL NULL NULL NULL NULL 117 Using temporary; Using filesort
tableA ALL datestamp NULL NULL NULL 434836 Using where


(c) ANALYZE TABLE Query:

Table Op Msg_type Msg_text
tableA analyze status Table is already up to date

Table Op Msg_type Msg_text
tableB analyze status Table is already up to date


(d) DESCRIBE Query:

- tableA

Field Type Null Key Default Extra
ID int(11) PRI NULL auto_increment
...
datestamp date MUL 0000-00-00
...

- tableB

Field Type Null Key Default Extra
ID int(11) PRI NULL auto_increment
...
pid_fullname varchar(25) MUL
...


(e) SHOW INDEX Query:

- tableA

tableA 0 PRIMARY 1 ID A 434836 NULL NULL BTREE
tableA 0 ID 1 ID A 434836 NULL NULL BTREE
tableA 1 ID_2 1 ID A 434836 NULL NULL BTREE
tableA 1 datestamp 1 datestamp A 37 NULL NULL BTREE

- tableB

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
tableB 0 PRIMARY 1 ID A 117 NULL NULL BTREE
tableB 1 ID 1 ID A 117 NULL NULL BTREE
tableB 1 pid_fullname 1 pid_fullname A 117 NULL NULL BTREE