List:General Discussion« Previous MessageNext Message »
From:Patricio A. Bruna Date:May 22 2007 6:52pm
Subject:Re: Problem with compex query
View as plain text  
Juan Eduardo, 

1) The versions are the same. 
2) What you meant with " indexes for the query"? 
3) Im goint to try that. 
4) Good Idea. 

Thanks. 

----- Mensaje Original ----- 
De: "Juan Eduardo Moreno" <juaneduardomc@stripped> 
Para: "Patricio A. Bruna" <pbruna@stripped> 
Cc: mysql@stripped 
Enviados: martes 22 de mayo de 2007 13H31 (GMT-0400) America/Santiago 
Asunto: Re: Problem with compex query 

Hi Patricio, 

Your explain is scary.....No use of index...Please, ask to programers in order to create
some index in the schema. 

Development Server has the same version of MySQL production environment?. 4.0.18 ? 

Resume: 

1) Please check the version of mysql ( prod and development) 
2) Please use indexes for the query. 
3) For the session in Websphere please ask to programers in order to set the enviroment
only for the query. Use explicit code for that. 
SET SESSION SQL_BIG_SELECTS=1; 
SET SESSION SQL_MAX_JOIN_SIZE=DEFAULT; 
4) In order to improve the insert, try to commit every ( for example) 10000 records. Ask
to programers for provide some cursor or something like that. 


Regards, 
Juan 


On 5/22/07, Patricio A. Bruna < pbruna@stripped > wrote: 

Juan Eduardo, 

Great to hear about you :) 
One thing you must know is that i can run this query from a mysql client, without the
insert part. 
The problem only happens when is run from the J2EE (Websphere - drp) application. 

I run the query as you asked, here are some results: 

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

| drp_id_sku | drp_id_deposito | drp_volumen | 1 | drp_dia | drp_mes | drp_anno |
drp_fecha_dia_cargado | 
+------------+-----------------+-------------+---+---------+---------+----------+-----------------------+

| 161 | 35 | 1.60000 | 1 | 12 | 5 | 2007 | 2007-05-12 00:00:00 | 
| 161 | 20 | 1.50000 | 1 | 2 | 5 | 2007 | 2007-05-02 00:00:00 | 
| 161 | 22 | 0.20000 | 1 | 11 | 5 | 2007 | 2007-05-11 00:00:00 | 
| 161 | 13 | 0.20000 | 1 | 7 | 5 | 2007 | 2007-05-07 00:00:00 | 
| 161 | 16 | 2.20000 | 1 | 9 | 5 | 2007 | 2007-05-09 00:00:00 | 
| 161 | 35 | 4.00000 | 1 | 3 | 5 | 2007 | 2007-05-03 00:00:00 | 
| 161 | 16 | 24.00000 | 1 | 4 | 5 | 2007 | 2007-05-04 00:00:00 | 
| 161 | 2 | 0.20000 | 1 | 9 | 5 | 2007 | 2007-05-09 00:00:00 | 
| 163 | 35 | 16.60000 | 1 | 11 | 5 | 2007 | 2007-05-11 00:00:00 | 
| 163 | 36 | 2.20000 | 1 | 4 | 5 | 2007 | 2007-05-04 00:00:00 | 
| 163 | 16 | -2.40000 | 1 | 8 | 5 | 2007 | 2007-05-08 00:00:00 | 
| 163 | 35 | 8.80000 | 1 | 2 | 5 | 2007 | 2007-05-02 00:00:00 | 
| 163 | 32 | 13.00000 | 1 | 8 | 5 | 2007 | 2007-05-08 00:00:00 | 
| 163 | 34 | 7.60000 | 1 | 7 | 5 | 2007 | 2007-05-07 00:00:00 | 

And the EXPLAIN: 

+-------+------+---------------+------+---------+------+-------+-----------------------+ 
| table | type | possible_keys | key | key_len | ref | rows | Extra | 
+-------+------+---------------+------+---------+------+-------+-----------------------+ 
| d | ALL | NULL | NULL | NULL | NULL | 37 | Using temporary | 
| md | ALL | NULL | NULL | NULL | NULL | 32 | Using where | 
| vv | ALL | NULL | NULL | NULL | NULL | 12694 | Using where | 
| s | ALL | NULL | NULL | NULL | NULL | 104 | | 
| ms | ALL | NULL | NULL | NULL | NULL | 184 | Using where; Distinct | 
+-------+------+---------------+------+---------+------+-------+-----------------------+ 
5 rows in set (0.00 sec) 


Any ideas? 

----- "Juan Eduardo Moreno" < juaneduardomc@stripped > escribió: 
> Hi Patricio, 
> 
> Some options are to prevent programmers/users make a "bad" queries 
> into 
> the database' SQL_BIG_SELECTS = 0 | 1 
> 
> The documentation say : 
> "If set to 0, MySQL will abort if a SELECT is attempted that probably 
> will 
> take a very long time. This is useful when an inadvisable WHERE 
> statement 
> has been issued. A big query is defined as a SELECT that probably will 
> have 
> to examine more than max_join_size rows. The default value for a new 
> connection is 1 (which will allow all SELECT statements)." 
> 
> For testing try this : 
> 
> 1) 
> 
> SET SESSION SQL_BIG_SELECTS=1; 
> SET SESSION SQL_MAX_JOIN_SIZE=DEFAULT; 
> Run the query; 
> 
> 2) Send your results and explain of query ( explain select ....) 
> 
> Regards, 
> Juan 
> 
> 
> On 5/22/07, Patricio A. Bruna < pbruna@stripped > wrote: 
> > 
> > Friends, 
> > im having troubles with the following query: 
> > 
> > ------------------------------------------------------- 
> > INSERT drp_volumen_venta_diaria_deposito (drp_id_sku, 
> drp_id_deposito, 
> > drp_volumen, drp_es_cct, drp_dia, drp_mes, drp_anno, 
> drp_fecha_dia_cargado ) 
> > SELECT DISTINCT s.drp_id_sku, d.drp_id_deposito, vv.drp_volumen, 1, 
> > vv.drp_dia, vv.drp_mes, vv.drp_anno,vv.drp_fecha_dia_cargado 
> > FROM drp_volumen_venta_diaria_tmp vv, drp_sku s, drp_deposito d, 
> > drp_mapeo_sku ms, drp_mapeo_deposito md 
> > WHERE vv.drp_codigo_sku = ms.drp_cod_sku_odyssey AND 
> ms.drp_cod_sku_sap = 
> > s.drp_codigo_sku 
> > AND REPLACE(UCASE(TRIM(vv.drp_codigo_deposito )),' ','')= 
> > REPLACE(UCASE(TRIM(md.drp_alias_deposito_odyssey)),' ','') AND 
> > REPLACE(UCASE(TRIM(md.drp_alias_deposito_sap)),' ','') = 
> > REPLACE(UCASE(TRIM( d.drp_alias_deposito)),' ','') 
> > AND 
> > CONCAT(vv.drp_anno,if(LENGTH(vv.drp_mes)=1,CONCAT('0',vv.drp_mes), 
> > vv.drp_mes) 
> > , if(LENGTH(vv.drp_dia)=1,CONCAT('0', vv.drp_dia),vv.drp_dia) ) >= ' 
> > 20070501 ' 
> > AND 
> > CONCAT(vv.drp_anno,if(LENGTH(vv.drp_mes)=1,CONCAT('0',vv.drp_mes), 
> > vv.drp_mes) 
> > , if(LENGTH( vv.drp_dia)=1,CONCAT('0',vv.drp_dia),vv.drp_dia) ) <= ' 
> > 20070515 '; 
> > 
> > ------------------------------------------------------------- 
> > 
> > I run this query in 2 servers, devel and production, which have the 
> same 
> > data. 
> > I run the query in devel without problems, but in production is not 
> > working and give me this error: 
> > 
> > " The SELECT would examine more rows than MAX_JOIN_SIZE. Check your 
> WHERE 
> > and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the 
> SELECT is ok 
> > " 
> > 
> > The value of MAX_JOIN_SIZE is set at: 4294967295 and 
> SET_SQL_BIG_SELECTS 
> > is 1. 
> > MySQL version is 4.0.18 over Red Hat 3. 
> > 
> > 
> > any idea why this isnt working? 
> > 
> > thanks 



Thread
Problem with compex queryPatricio A. Bruna22 May
  • Re: Problem with compex queryJuan Eduardo Moreno22 May
Re: Problem with compex queryPatricio A. Bruna22 May
  • Re: Problem with compex queryJuan Eduardo Moreno22 May
    • Re: Problem with compex queryPatricio A. Bruna22 May
      • Re: Problem with compex queryJuan Eduardo Moreno22 May