Re: improve performance of this sql
explain select * from (select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP C where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID =
A.CLUSTER_ID ) A limit 40\G;
*** 1. row ***
id: 1
select_type: PRIMARY
table: derived2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3231
Extra:
*** 2. row ***
id: 2
select_type: DERIVED
table: B
type: ref
possible_keys: PRIMARY,KD_KW_KI_IDX_0806120615
key: KD_KW_KI_IDX_0806120615
key_len: 767
ref:
rows: 720
Extra: Using where; Using index
*** 3. row ***
id: 2
select_type: DERIVED
table: C
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.B.kr_id
rows: 1
Extra: Using index
*** 4. row ***
id: 2
select_type: DERIVED
table: A
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.C.cluster_id
rows: 1
Extra:
4 rows in set (0.03 sec)
ERROR:
No query specified
On 6/22/08, Moon's Father [EMAIL PROTECTED] wrote:
Can you show us the proper view of your sql statement by adding \G.
On Sat, Jun 21, 2008 at 1:57 PM, Ananda Kumar [EMAIL PROTECTED] wrote:
The below query performance in 10 sec when there are no other activity on
db
, but when any insert or LOAD DATA Index creation happens it takes close
to
80 sec. Any ways to improve the performance of this sql.
innodb_buffer=11GB , key_buffer=3 GB, we have totally 16GB
EXPLAIN select * from (select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS,
A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A,
R_DATA B, ER_MAP C where B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID
and
C.CLUSTER_ID = A.CLUSTER_ID ) A limit
40;
++-+++-+-+-+-
--+--+--+
| id | select_type | table | type | possible_keys
|
key | key_len | ref
| rows | Extra|
++-+++-+-+-+-
--+--+--+
| 1 | PRIMARY | derived2 | ALL| NULL
|
NULL| NULL| NULL
| 3278 | |
| 2 | DERIVED | B | ref| PRIMARY,KD_KW_KI_IDX_0805230323
|
KD_KW_KI_IDX_0805230323 | 767 |
| 1524 | Using where; Using index |
| 2 | DERIVED | C | ref| PRIMARY
|
PRIMARY | 10 |
reh.B.kr_id |1 | Using index |
| 2 | DERIVED | A | eq_ref | PRIMARY
|
PRIMARY | 10 |
reh.C.cluster_id |1 | |
++-+++-+-+-+-
--+--+--+
--
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
Re: improve performance of this sql
Hi,
AFAIK, to optimize your query you should have :
1 index on B.KEYWORDS. (I assume it's the KD_KW_KI_IDX_0806120615 index ?)
1 index on C.KR_ID. Your index is not UNIQUE here, is this expected ?
1 index unique on A.CLUSTER_ID (it's already the case)
BTW, why are you using a derived table here ?
You could write directly :
select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP C where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID =
A.CLUSTER_ID limit 40
Regards,
Jocelyn Fournier
Ananda Kumar a écrit :
explain select * from (select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP C where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID =
A.CLUSTER_ID ) A limit 40\G;
*** 1. row ***
id: 1
select_type: PRIMARY
table: derived2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3231
Extra:
*** 2. row ***
id: 2
select_type: DERIVED
table: B
type: ref
possible_keys: PRIMARY,KD_KW_KI_IDX_0806120615
key: KD_KW_KI_IDX_0806120615
key_len: 767
ref:
rows: 720
Extra: Using where; Using index
*** 3. row ***
id: 2
select_type: DERIVED
table: C
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.B.kr_id
rows: 1
Extra: Using index
*** 4. row ***
id: 2
select_type: DERIVED
table: A
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.C.cluster_id
rows: 1
Extra:
4 rows in set (0.03 sec)
ERROR:
No query specified
On 6/22/08, Moon's Father [EMAIL PROTECTED] wrote:
Can you show us the proper view of your sql statement by adding \G.
On Sat, Jun 21, 2008 at 1:57 PM, Ananda Kumar [EMAIL PROTECTED] wrote:
The below query performance in 10 sec when there are no other activity on
db
, but when any insert or LOAD DATA Index creation happens it takes close
to
80 sec. Any ways to improve the performance of this sql.
innodb_buffer=11GB , key_buffer=3 GB, we have totally 16GB
EXPLAIN select * from (select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS,
A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A,
R_DATA B, ER_MAP C where B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID
and
C.CLUSTER_ID = A.CLUSTER_ID ) A limit
40;
++-+++-+-+-+-
--+--+--+
| id | select_type | table | type | possible_keys
|
key | key_len | ref
| rows | Extra|
++-+++-+-+-+-
--+--+--+
| 1 | PRIMARY | derived2 | ALL| NULL
|
NULL| NULL| NULL
| 3278 | |
| 2 | DERIVED | B | ref| PRIMARY,KD_KW_KI_IDX_0805230323
|
KD_KW_KI_IDX_0805230323 | 767 |
| 1524 | Using where; Using index |
| 2 | DERIVED | C | ref| PRIMARY
|
PRIMARY | 10 |
reh.B.kr_id |1 | Using index |
| 2 | DERIVED | A | eq_ref | PRIMARY
|
PRIMARY | 10 |
reh.C.cluster_id |1 | |
++-+++-+-+-+-
--+--+--+
--
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improve performance of this sql
Hi Jo,
Yes there is a combined index on (keywords,kr_id) on B,
c.kr_id is a primary key.
Let me talk to my dev and check why they are using derived. Thanks for
noticing this.
On 6/23/08, Jocelyn Fournier [EMAIL PROTECTED] wrote:
Hi,
AFAIK, to optimize your query you should have :
1 index on B.KEYWORDS. (I assume it's the KD_KW_KI_IDX_0806120615 index ?)
1 index on C.KR_ID. Your index is not UNIQUE here, is this expected ?
1 index unique on A.CLUSTER_ID (it's already the case)
BTW, why are you using a derived table here ?
You could write directly :
select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP C where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID =
A.CLUSTER_ID limit 40
Regards,
Jocelyn Fournier
Ananda Kumar a écrit :
explain select * from (select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP C where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID =
A.CLUSTER_ID ) A limit 40\G;
*** 1. row ***
id: 1
select_type: PRIMARY
table: derived2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3231
Extra:
*** 2. row ***
id: 2
select_type: DERIVED
table: B
type: ref
possible_keys: PRIMARY,KD_KW_KI_IDX_0806120615
key: KD_KW_KI_IDX_0806120615
key_len: 767
ref:
rows: 720
Extra: Using where; Using index
*** 3. row ***
id: 2
select_type: DERIVED
table: C
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.B.kr_id
rows: 1
Extra: Using index
*** 4. row ***
id: 2
select_type: DERIVED
table: A
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.C.cluster_id
rows: 1
Extra:
4 rows in set (0.03 sec)
ERROR:
No query specified
On 6/22/08, Moon's Father [EMAIL PROTECTED] wrote:
Can you show us the proper view of your sql statement by adding \G.
On Sat, Jun 21, 2008 at 1:57 PM, Ananda Kumar [EMAIL PROTECTED] wrote:
The below query performance in 10 sec when there are no other activity on
db
, but when any insert or LOAD DATA Index creation happens it takes
close
to
80 sec. Any ways to improve the performance of this sql.
innodb_buffer=11GB , key_buffer=3 GB, we have totally 16GB
EXPLAIN select * from (select A.LEAF_CATEG_ID, A.CLUSTER_ID,
A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS,
A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A,
R_DATA B, ER_MAP C where B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID
and
C.CLUSTER_ID = A.CLUSTER_ID ) A limit
40;
++-+++-+-+-+-
--+--+--+
| id | select_type | table | type | possible_keys
|
key | key_len | ref
| rows | Extra|
++-+++-+-+-+-
--+--+--+
| 1 | PRIMARY | derived2 | ALL| NULL
|
NULL| NULL| NULL
| 3278 | |
| 2 | DERIVED | B | ref|
PRIMARY,KD_KW_KI_IDX_0805230323
|
KD_KW_KI_IDX_0805230323 | 767 |
| 1524 | Using where; Using index |
| 2 | DERIVED | C | ref| PRIMARY
|
PRIMARY | 10 |
reh.B.kr_id |1 | Using index |
| 2 | DERIVED | A | eq_ref | PRIMARY
|
PRIMARY | 10 |
reh.C.cluster_id |1 | |
++-+++-+-+-+-
--+--+--+
--
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
Re: improve performance of this sql
Hi,
Oops, indeed, C is a primary key :)
But what's weird is MySQL is using a ref type for the join between B and
C, and not an eq_ref.
Could you check B.KR_ID and C.KR_ID are of the same data type ?
Regards,
Jocelyn
Ananda Kumar a écrit :
Hi Jo,
Yes there is a combined index on (keywords,kr_id) on B,
c.kr_id is a primary key.
Let me talk to my dev and check why they are using derived. Thanks for
noticing this.
On 6/23/08, *Jocelyn Fournier* [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Hi,
AFAIK, to optimize your query you should have :
1 index on B.KEYWORDS. (I assume it's the KD_KW_KI_IDX_0806120615
index ?)
1 index on C.KR_ID. Your index is not UNIQUE here, is this expected ?
1 index unique on A.CLUSTER_ID (it's already the case)
BTW, why are you using a derived table here ?
You could write directly :
select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP C where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID =
A.CLUSTER_ID limit 40
Regards,
Jocelyn Fournier
Ananda Kumar a écrit :
explain select * from (select A.LEAF_CATEG_ID, A.CLUSTER_ID,
A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP C
where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID =
A.CLUSTER_ID ) A limit 40\G;
*** 1. row ***
id: 1
select_type: PRIMARY
table: derived2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3231
Extra:
*** 2. row ***
id: 2
select_type: DERIVED
table: B
type: ref
possible_keys: PRIMARY,KD_KW_KI_IDX_0806120615
key: KD_KW_KI_IDX_0806120615
key_len: 767
ref:
rows: 720
Extra: Using where; Using index
*** 3. row ***
id: 2
select_type: DERIVED
table: C
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.B.kr_id
rows: 1
Extra: Using index
*** 4. row ***
id: 2
select_type: DERIVED
table: A
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.C.cluster_id
rows: 1
Extra:
4 rows in set (0.03 sec)
ERROR:
No query specified
On 6/22/08, Moon's Father [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Can you show us the proper view of your sql statement by
adding \G.
On Sat, Jun 21, 2008 at 1:57 PM, Ananda Kumar
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote:
The below query performance in 10 sec when there are no
other activity on
db
, but when any insert or LOAD DATA Index creation
happens it takes close
to
80 sec. Any ways to improve the performance of this sql.
innodb_buffer=11GB , key_buffer=3 GB, we have totally 16GB
EXPLAIN select * from (select A.LEAF_CATEG_ID,
A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS,
A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE,
A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A,
R_DATA B, ER_MAP C where B.KEYWORDS IN ('CAMERA') and
B.KR_ID = C.KR_ID
and
C.CLUSTER_ID = A.CLUSTER_ID ) A limit
40;
++-+++-+-+-+-
--+--+--+
| id | select_type | table | type | possible_keys
|
key | key_len | ref
| rows | Extra|
Re: improve performance of this sql
B has single column KR_ID as primary key, where as C has combined primary
key (kr_id,cluster_id) and data type on both tables for KR_ID are same.
On 6/23/08, Jocelyn Fournier [EMAIL PROTECTED] wrote:
Hi,
Oops, indeed, C is a primary key :)
But what's weird is MySQL is using a ref type for the join between B and C,
and not an eq_ref.
Could you check B.KR_ID and C.KR_ID are of the same data type ?
Regards,
Jocelyn
Ananda Kumar a écrit :
Hi Jo,
Yes there is a combined index on (keywords,kr_id) on B,
c.kr_id is a primary key.
Let me talk to my dev and check why they are using derived. Thanks for
noticing this.
On 6/23/08, *Jocelyn Fournier* [EMAIL PROTECTED] mailto:
[EMAIL PROTECTED] wrote:
Hi,
AFAIK, to optimize your query you should have :
1 index on B.KEYWORDS. (I assume it's the KD_KW_KI_IDX_0806120615
index ?)
1 index on C.KR_ID. Your index is not UNIQUE here, is this expected ?
1 index unique on A.CLUSTER_ID (it's already the case)
BTW, why are you using a derived table here ?
You could write directly :
select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP C where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID =
A.CLUSTER_ID limit 40
Regards,
Jocelyn Fournier
Ananda Kumar a écrit :
explain select * from (select A.LEAF_CATEG_ID, A.CLUSTER_ID,
A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP C
where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID =
A.CLUSTER_ID ) A limit 40\G;
*** 1. row ***
id: 1
select_type: PRIMARY
table: derived2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3231
Extra:
*** 2. row ***
id: 2
select_type: DERIVED
table: B
type: ref
possible_keys: PRIMARY,KD_KW_KI_IDX_0806120615
key: KD_KW_KI_IDX_0806120615
key_len: 767
ref:
rows: 720
Extra: Using where; Using index
*** 3. row ***
id: 2
select_type: DERIVED
table: C
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.B.kr_id
rows: 1
Extra: Using index
*** 4. row ***
id: 2
select_type: DERIVED
table: A
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.C.cluster_id
rows: 1
Extra:
4 rows in set (0.03 sec)
ERROR:
No query specified
On 6/22/08, Moon's Father [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Can you show us the proper view of your sql statement by
adding \G.
On Sat, Jun 21, 2008 at 1:57 PM, Ananda Kumar
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote:
The below query performance in 10 sec when there are no
other activity on
db
, but when any insert or LOAD DATA Index creation
happens it takes close
to
80 sec. Any ways to improve the performance of this sql.
innodb_buffer=11GB , key_buffer=3 GB, we have totally 16GB
EXPLAIN select * from (select A.LEAF_CATEG_ID,
A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS,
A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE,
A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A,
R_DATA B, ER_MAP C where B.KEYWORDS IN ('CAMERA') and
B.KR_ID = C.KR_ID
and
C.CLUSTER_ID = A.CLUSTER_ID ) A limit
40;
++-+++-+-+-+-
--+--+--+
| id | select_type | table | type | possible_keys
Re: improve performance of this sql
Ok, so just to be sure, in C, kr_id is not unique by design (you have
several different cluster_id for the same kr_id) ?
Ananda Kumar a écrit :
B has single column KR_ID as primary key, where as C has combined
primary key (kr_id,cluster_id) and data type on both tables for KR_ID
are same.
On 6/23/08, *Jocelyn Fournier* [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Hi,
Oops, indeed, C is a primary key :)
But what's weird is MySQL is using a ref type for the join between B
and C, and not an eq_ref.
Could you check B.KR_ID and C.KR_ID are of the same data type ?
Regards,
Jocelyn
Ananda Kumar a écrit :
Hi Jo,
Yes there is a combined index on (keywords,kr_id) on B,
c.kr_id is a primary key.
Let me talk to my dev and check why they are using derived.
Thanks for noticing this.
On 6/23/08, *Jocelyn Fournier* [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Hi,
AFAIK, to optimize your query you should have :
1 index on B.KEYWORDS. (I assume it's the KD_KW_KI_IDX_0806120615
index ?)
1 index on C.KR_ID. Your index is not UNIQUE here, is this
expected ?
1 index unique on A.CLUSTER_ID (it's already the case)
BTW, why are you using a derived table here ?
You could write directly :
select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE,
A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP
C where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID =
A.CLUSTER_ID limit 40
Regards,
Jocelyn Fournier
Ananda Kumar a écrit :
explain select * from (select A.LEAF_CATEG_ID, A.CLUSTER_ID,
A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE,
A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B,
ER_MAP C
where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and
C.CLUSTER_ID =
A.CLUSTER_ID ) A limit 40\G;
*** 1. row
***
id: 1
select_type: PRIMARY
table: derived2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3231
Extra:
*** 2. row
***
id: 2
select_type: DERIVED
table: B
type: ref
possible_keys: PRIMARY,KD_KW_KI_IDX_0806120615
key: KD_KW_KI_IDX_0806120615
key_len: 767
ref:
rows: 720
Extra: Using where; Using index
*** 3. row
***
id: 2
select_type: DERIVED
table: C
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.B.kr_id
rows: 1
Extra: Using index
*** 4. row
***
id: 2
select_type: DERIVED
table: A
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.C.cluster_id
rows: 1
Extra:
4 rows in set (0.03 sec)
ERROR:
No query specified
On 6/22/08, Moon's Father [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Can you show us the proper view of your sql statement by
adding \G.
On Sat, Jun 21, 2008 at 1:57 PM, Ananda Kumar
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote:
The below query performance in 10 sec when there
are no
other activity on
db
,
Re: improve performance of this sql
yes, there could be different cluster_id's for the same kr_id.
On 6/23/08, Jocelyn Fournier [EMAIL PROTECTED] wrote:
Ok, so just to be sure, in C, kr_id is not unique by design (you have
several different cluster_id for the same kr_id) ?
Ananda Kumar a écrit :
B has single column KR_ID as primary key, where as C has combined primary
key (kr_id,cluster_id) and data type on both tables for KR_ID are same.
On 6/23/08, *Jocelyn Fournier* [EMAIL PROTECTED] mailto:
[EMAIL PROTECTED] wrote:
Hi,
Oops, indeed, C is a primary key :)
But what's weird is MySQL is using a ref type for the join between B
and C, and not an eq_ref.
Could you check B.KR_ID and C.KR_ID are of the same data type ?
Regards,
Jocelyn
Ananda Kumar a écrit :
Hi Jo,
Yes there is a combined index on (keywords,kr_id) on B,
c.kr_id is a primary key.
Let me talk to my dev and check why they are using derived.
Thanks for noticing this.
On 6/23/08, *Jocelyn Fournier* [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Hi,
AFAIK, to optimize your query you should have :
1 index on B.KEYWORDS. (I assume it's the
KD_KW_KI_IDX_0806120615
index ?)
1 index on C.KR_ID. Your index is not UNIQUE here, is this
expected ?
1 index unique on A.CLUSTER_ID (it's already the case)
BTW, why are you using a derived table here ?
You could write directly :
select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE,
A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP
C where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID
=
A.CLUSTER_ID limit 40
Regards,
Jocelyn Fournier
Ananda Kumar a écrit :
explain select * from (select A.LEAF_CATEG_ID, A.CLUSTER_ID,
A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE,
A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B,
ER_MAP C
where
B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and
C.CLUSTER_ID =
A.CLUSTER_ID ) A limit 40\G;
*** 1. row
***
id: 1
select_type: PRIMARY
table: derived2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3231
Extra:
*** 2. row
***
id: 2
select_type: DERIVED
table: B
type: ref
possible_keys: PRIMARY,KD_KW_KI_IDX_0806120615
key: KD_KW_KI_IDX_0806120615
key_len: 767
ref:
rows: 720
Extra: Using where; Using index
*** 3. row
***
id: 2
select_type: DERIVED
table: C
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.B.kr_id
rows: 1
Extra: Using index
*** 4. row
***
id: 2
select_type: DERIVED
table: A
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 10
ref: reh.C.cluster_id
rows: 1
Extra:
4 rows in set (0.03 sec)
ERROR:
No query specified
On 6/22/08, Moon's Father [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Can you show us the proper view of your sql statement by
adding \G.
On Sat, Jun 21, 2008 at 1:57 PM, Ananda Kumar
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote:
The below query performance in 10
MySQL University session on June 26: Patch Management with Quilt
Hi, this Thursday, Stewart Smith will give a MySQL University session: http://forge.mysql.com/wiki/Patch_Management_With_Quilt (topic: Patch Management with Quilt) Please register for this session by filling in your name on the session Wiki page. Registering is not required but appreciated. That Wiki page also contains a section to post questions. Please use it! MySQL University sessions normally start at 13:00 UTC (summer) or 14:00 UTC (winter); see: http://forge.mysql.com/wiki/MySQL_University for more time zone information. Those planning to attend a MySQL University session for the very first time should probably read the instructions for attendees, http://forge.mysql.com/wiki/Instructions_for_Attendees. Next MySQL University sessions before the summer semester break: July 3, 2008: Advanced LUA Scripting (Giuseppe Maxia) See http://forge.mysql.com/wiki/MySQL_University#Upcoming_Sessions for the complete list. Regards, Stefan -- *** Sun Microsystems GmbHStefan Hinz Sonnenallee 1Manager Documentation, Database Group 85551 Kirchheim-Heimstetten Phone: +49-30-82702940 Germany Fax: +49-30-82702941 http://www.sun.demailto: [EMAIL PROTECTED] Amtsgericht Muenchen: HRB161028 Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very large temporary file(s)
sigh In all of that information, I forgot to post the actual query: INSERT INTO consolidated_customer_data SELECT customers.customer_id, account.account_name, customers.email, customers.email_status, customers.dm_status, customers.status, customers.last_name, customers.first_name, customers.sal, customers.company, customers.address_1, customers.address_2, customers.address_3, customers.country, customers.zip, customers.input_source, customers.interest_category, customers.interest_subcategory, CONCAT(|, GROUP_CONCAT(cust_topics.topic_code SEPARATOR |), |) AS topic_list, stage.stage_name FROM customers JOIN account ON account.account_id = customers.account_id JOIN stage ON customers.stage_id = stage.stage_id LEFT JOIN cust_topics ON customers.customer_id = cust_topics.customer_id GROUP BY customers.customer_id; Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 5.0.51b and ssl
hi all... i just installed openssl 0.9.8h and trying to build mysql 5.0.51b with it on a freebsd 7 machine. i get this: /usr/bin/ld: /usr/local/ssl/lib/libssl.a(t1_srvr.o): relocation R_X86_64_32 can not be used when making a shared object; recompile with -fPIC /usr/local/ssl/lib/libssl.a: could not read symbols: Bad value any ideas? thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 5.0.51b and ssl
from what i understand this is not uncommon. and it goes back to 2005. and it has to do with the fact that the machine identifies itself as amd64 even though it's an intel machine. what's not clear is what needs to be recompiled whit -fPIC?! my guess is openssl needs to. why? are there any flags i can change in the Makefile for the mysql build? does it have to be a 'shared'? static?! thanks... kalin m wrote: hi all... i just installed openssl 0.9.8h and trying to build mysql 5.0.51b with it on a freebsd 7 machine. i get this: /usr/bin/ld: /usr/local/ssl/lib/libssl.a(t1_srvr.o): relocation R_X86_64_32 can not be used when making a shared object; recompile with -fPIC /usr/local/ssl/lib/libssl.a: could not read symbols: Bad value any ideas? thanks... ___ [EMAIL PROTECTED] mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
