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 <[EMAIL PROTECTED] > 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" < [EMAIL PROTECTED]> 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 <[EMAIL PROTECTED] > 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
