Bug #15774 slow / increase memory / crashing / OS freezing

• Submitted: 15 Dec 2005 13:39
• Modified: 16 Dec 2005 10:18
• Reporter: Olivier LABORDE
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0
• OS: Windows (Windows XP PRO Sp2)
• CPU Architecture: Any

[15 Dec 2005 13:39] Olivier LABORDE

Description:
The next query with 3 IN imbricate make slow OS and increase the memory :
SELECT * from x_art WHERE
 code_art in(SELECT distinct code_art FROM x_art WHERE
marque in (SELECT distinct code_marque FROM x_client_marque WHERE no_cli=100883 AND soc='PRE'))

How to repeat:
Imbricate 3 select :

select  xxx from T where yyy IN (select distinct yyy from P where zzz IN (select distinct zzz from Q ))

Suggested fix:
recursivity ??

[15 Dec 2005 13:45] Aleksey Kishkin

Olivier, could you  please to provide output of 'explain' of that query?

[15 Dec 2005 14:03] Olivier LABORDE

The query is created automatically with 2 subquery :

Main Query #1 :

"SELECT distinct *  FROM systeme WHERE soc="+quote(pf_codesoc)+" and libelle IN ( select valeur from x_art_critere where soc="+quote(pf_codesoc)+...
" AND nom_var='SYST' AND code_art IN ( "+Req_ArticlesClient(u_concepteur,pf_codecli,pf_codesoc)+" ))"

Query #2 : Req_ArticlesClient 

"SELECT distinct code_art FROM x_art WHERE Soc="+quote(pf_soc)+" and archive!='1' AND (concepteur='*' OR concepteur="+quote(pf_concepteur)+...
	") AND ( marque='TOUT' or marque IN ("+Req_MarqueClient(pf_nocli,pf_soc)+"))"

Query #3 : Req_MarqueClient 

"SELECT distinct code_marque FROM x_client_marque WHERE no_cli="+pf_nocli+" AND soc="+quote(pf_soc)

The result query is :
SELECT distinct *  FROM systeme WHERE libelle IN ( select valeur from x_art_critere where nom_var='SYST' AND code_art IN ( SELECT distinct code_art FROM x_art WHERE Soc='PRE' and archive!='1' AND (concepteur='*' OR concepteur='FA') AND ( marque='TOUT' or marque IN (SELECT distinct code_marque FROM x_client_marque WHERE no_cli=10044 AND soc='PRE')) ))

With SQLServer : the query is Ok and the result is good.

[15 Dec 2005 15:25] Aleksey Kishkin

NO, I meant mysql has command 'explain' that shows a query plan.

Please run

explain SELECT * from x_art WHERE
 code_art in(SELECT distinct code_art FROM x_art WHERE
marque in (SELECT distinct code_marque FROM x_client_marque WHERE no_cli=100883
AND soc='PRE'))

and copy/paste output here. (I cannot do it, because query plan depends on data statistic in your tables)

[15 Dec 2005 16:27] Olivier LABORDE

I created index on code_art , code_marque 

SELECT * from x_art WHERE
 code_art in(SELECT distinct code_art FROM x_art WHERE
marque in (SELECT distinct code_marque FROM x_client_marque WHERE
no_cli=100883
AND soc='PRE'))

and it's good !

[16 Dec 2005 10:12] Valeriy Kravchuk

So, does this good result mean that there is no bug to describe here and we may close this report?

[16 Dec 2005 10:18] Olivier LABORDE

This bug can be avoided with INDEX. But, it would be interressant to correct this problem.