List:General Discussion« Previous MessageNext Message »
From:Juan Eduardo Moreno Date:May 22 2007 7:08pm
Subject:Re: Problem with compex query
View as plain text  
Patricio,

2) What you meant with " indexes for the query"?


A: Create some indexes for a query in order to improve the performance.


Regards,
Juan Eduardo

On 5/22/07, Patricio A. Bruna <pbruna@stripped> wrote:
>
> 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