Re: improve performance of this sql

2008-06-23 Thread Ananda Kumar
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

Re: improve performance of this sql

2008-06-23 Thread Jocelyn Fournier
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

Re: improve performance of this sql

2008-06-23 Thread Ananda Kumar
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

Re: improve performance of this sql

2008-06-23 Thread Jocelyn Fournier
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

Re: improve performance of this sql

2008-06-23 Thread Ananda Kumar
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

Re: improve performance of this sql

2008-06-23 Thread Jocelyn Fournier
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.

Re: improve performance of this sql

2008-06-23 Thread Ananda Kumar
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

MySQL University session on June 26: Patch Management with Quilt

2008-06-23 Thread Stefan Hinz
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.

Re: Very large temporary file(s)

2008-06-23 Thread Jerry Schwartz
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,

mysql 5.0.51b and ssl

2008-06-23 Thread kalin m
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:

Re: mysql 5.0.51b and ssl

2008-06-23 Thread kalin m
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