Re: MySQL Performance or Just me? :(
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
Subject
Views
Written By
Posted
2300
July 27, 2005 11:53PM
1606
July 28, 2005 01:08AM
1710
July 28, 2005 08:13AM
1631
July 28, 2005 08:32AM
1589
July 28, 2005 05:50PM
1521
July 29, 2005 02:00AM
Re: MySQL Performance or Just me? :(
1740
July 29, 2005 05:28AM
1234
July 29, 2005 06:52AM
1450
July 29, 2005 07:11AM
1854
July 29, 2005 07:36AM
1672
July 29, 2005 08:43AM
1583
July 29, 2005 12:30PM
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.