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
2465
July 27, 2005 11:53PM
1687
July 28, 2005 01:08AM
1794
July 28, 2005 08:13AM
1724
July 28, 2005 08:32AM
1674
July 28, 2005 05:50PM
1605
July 29, 2005 02:00AM
Re: MySQL Performance or Just me? :(
1836
July 29, 2005 05:28AM
1340
July 29, 2005 06:52AM
1556
July 29, 2005 07:11AM
1943
July 29, 2005 07:36AM
1758
July 29, 2005 08:43AM
1664
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.