MySQL Forums
Forum List  »  Performance

Re: 100% cpu usage on single cpu core
Posted by: Gus Lopez
Date: December 23, 2009 04:49PM

SORRY LEFT OUT THE \G

EXPLAIN SELECT ...\G -- clues of inefficiencies

SELECT 1.
<code>
EXPLAIN select max(query1.totalItems) from (select PUB_A_ID , count(B_ID) as totalItems from PUB_A_B group by PUB_A_ID) query1 \G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2115813
Extra:
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: PUB_A_B
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 17035682
Extra: Using temporary; Using filesort
2 rows in set (6 min 1.46 sec)
</code>

SELECT 2.
<code>
mysql> explain select PUB_A_ID, count(B_ID) from PUB_A_B group by PUB_A_ID \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: PUB_A_B
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 17035682
Extra: Using temporary; Using filesort
1 row in set (0.00 sec)

</code>

SELECT 3.
<code>
mysql> explain select NUMTERMS from PUB WHERE PUB_ID='TAG' \G ;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: PUB
type: ref
possible_keys: PUB_PUB_ID_index
key: PUB_PUB_ID_index
key_len: 35
ref: const
rows: 1
Extra: Using where
1 row in set (0.02 sec)
</code>

SELECT 4.
<code>
mysql> explain select PUB_A_ID, V_I from PUB_A_B \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: PUB_A_B
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 17035682
Extra:
1 row in set (0.00 sec)
</code>

Options: ReplyQuote


Subject
Views
Written By
Posted
9499
December 15, 2009 04:53AM
3175
December 17, 2009 12:27AM
3054
December 23, 2009 04:34PM
Re: 100% cpu usage on single cpu core
2706
December 23, 2009 04:49PM
2609
December 23, 2009 06:07PM
3397
December 23, 2009 08:52PM
2473
December 23, 2009 09:53PM
2847
December 24, 2009 03:48PM


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.