date:20080623

Problem with CREATE TABLE/DROP TABLE

2008-06-23 Thread Gwynne Raskind

I'm having the issue with CREATE TABLE described by Bug #30513 (http://bugs.mysql.com/bug.php?id=30513 
). To summarize, a table which previously existed, and then is dropped  
by DROP TABLE IF EXISTS, becomes randomly unable to be recreated. Here  
is my comment on that bug:


Having this same issue using MySQL 5.1.24-rc and 5.1.25-rc and an  
InnoDB table. Only solution I found was to dump and recreate my  
database, which is a ridiculous inconvenience since I'm having the  
issue with a test table I need to drop and recreate often. Did NOT  
have this issue before upgrading from 5.0.51. There is NO stray .frm  
file in the database directory, and the InnoDB tablespace/table  
monitors show no errors. No unusual entries appear in the MySQL error  
log. The table in question has the structure:


CREATE TABLE TestData (
nameVARCHAR(64) NOT NULL,
dateFormat  VARCHAR(32) NOT NULL,
loginForOne INT(1) UNSIGNED NOT NULL,
loginForTwo INT(1) UNSIGNED NOT NULL,
indexText   MEDIUMTEXT  NOT NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET 'utf8';

It is correct that the table has no indexes. I tried `-quoting the  
table name and changing engines and character sets to no avail.  
Changing the table's name only resulted in the same thing starting to  
happen again with the same table. The only special thing about the  
table is that it's at the end of a batch file.


Has anyone else had this problem, and more especially, does anyone  
know a useable workaround for it? I'm at my wits' end, and downgrading  
to 5.0.51 isn't a viable option for my environment; this isn't a  
production system and I'm using some 5.1-specific features as of my  
upgrade.


-- Gwynne, Daughter of the Code
"This whole world is an asylum for the incurable."


--
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

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 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]

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: 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: Very large temporary file(s)

2008-06-23 Thread Jerry Schwartz

 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 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. 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: 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 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] > [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]
>> >>> 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: 
>>   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
>>

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.

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]
 >> 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: 
   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

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 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] > [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: 
>>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;
>>
>>
>>  
>> ++

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 (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: 
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|

++-+++-

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 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: 
>> 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 |  | 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 |  |


 ++-+++-+-+-+-

 -

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 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: 
 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 |  | 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]

Problem with CREATE TABLE/DROP TABLE

Re: mysql 5.0.51b and ssl

mysql 5.0.51b and ssl

Re: Very large temporary file(s)

MySQL University session on June 26: Patch Management with Quilt

Re: improve performance of this sql

Re: improve performance of this sql

Re: improve performance of this sql

Re: improve performance of this sql

Re: improve performance of this sql

Re: improve performance of this sql

11 matches

Site Navigation

Mail list logo

Footer information