Hello Daniel, maybe you can post the EXPLAIN output (plus possibly the definition of the tables/views that participate in this query), so that anyone can help you. From just the query there's little to analyse.
Regards Alexander Schröder SAP AG Sitz der Gesellschaft/Registered Office: Walldorf, Germany Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), Shai Agassi, Léo Apotheker, Werner Brandt, Claus Heinrich, Gerhard Oswald, Peter Zencke Vorsitzender des Aufsichtsrats/Chairperson of the SAP Supervisory Board: Hasso Plattner Registergericht/Commercial Register Mannheim No HRB 350269 Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Kenntnisnahme des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt. Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank. This e-mail may contain trade secrets or privileged, undisclosed, or otherwise confidential information. If you have received this e-mail in error, you are hereby notified that any review, copying, or distribution of it is strictly prohibited. Please inform us immediately and destroy the original transmittal. Thank you for your cooperation. -----Original Message----- From: Daniel Castro [mailto:[EMAIL PROTECTED] Sent: Mittwoch, 27. Juni 2007 20:30 To: maxdb@lists.mysql.com Subject: Space for results table exhausted Hi, I have the folowing query: SELECT cnv.cnv_reg_ans , gih.gih_dias , gih.gih_senha , pac.pac_nome , gih.gih_numero , gih.gih_atendente , gih.gih_qtde , pac.pac_mcnv , pac.pac_dt_valid , pac.pac_cartao_sus , psv.psv_nome , psv.psv_uf , psv.psv_crm , psv.psv_cpf , emp_a.emp_cgc , gih.gih_dthr_ini , cfg.cfg_ind_homecare , cap.cap_pac_reg , cap.cap_num , cap.cap_cid_cod , cap.cap_tiss_tipo_doenca , cap.cap_tiss_tempo_doenca , cap.cap_tiss_escala_doenca , cap.cap_tiss_tipo_acidente , cap.cap_int_carater , cap.cap_int_clinc_cir , cap.cap_int_hosp , gih.gih_dthr_fim , gih.gih_dthr_lib , gih.gih_obs , gih.gih_qtde_solic , ( SELECT pln.pln_cod_conv FROM pln WHERE pln.pln_cnv_cod = cap.cap_cnv_codAND pln.pln_cod = pac.pac_pln_cod AND pln.pln_cnv_cod = pac.pac_cnv ) as plano, ( SELECT pln.pln_nome FROM pln WHERE pln.pln_cnv_cod = cap.cap_cnv_cod AND pln.pln_cod = pac.pac_pln_cod AND pln.pln_cnv_cod = pac.pac_cnv ) as plano_nome, psv.psv_conselho , ( SELECT MIN ( d.esp_cbo_s ) FROM esp d, esm m WHERE m.esm_esp = d.esp_cod AND m.esm_default = 'S' AND m.esm_med = gih.gih_psv_solic ) as esp_medico_solic, emp_a.emp_nome_fantasia , emp_a.emp_cnes , emp_b.emp_cgc , emp_b.emp_nome_fantasia , emp_b.emp_cnes , ( SELECT o.emp_tiss_cod_oper FROM emp o WHERE o.emp_cod = cnv.cnv_emp_cod ) as emp_cod_oper, ( SELECT cid.cid_nome FROM cid WHERE cid.cid_cod = cap.cap_cid_cod ) as cid_nome, cap.cap_obs , cnv.cnv_nome , ( SELECT cle.cle_cod_tiss FROM cle WHERE cle_cod = gih.gih_cle_cod ) as padrao_autorizado, cap.cap_tiss_indic_clinica , ( SELECT min ( pdg.pdg_cid ) FROM pdg WHERE pdg.pdg_pac = gih.gih_pac_regAND pdg.pdg_hsp = gih.gih_hsp_num AND pdg.pdg_hsp > 0 AND pdg.pdg_principal <> 'S' ) as cid_cod_2, ( SELECT max ( pdg.pdg_cid ) FROM pdg WHERE pdg.pdg_pac = gih.gih_pac_regAND pdg.pdg_hsp = gih.gih_hsp_num AND pdg.pdg_hsp > 0 AND pdg.pdg_principal <> 'S' ) as cid_cod_3, ( SELECT min ( pdg.pdg_cid ) FROM pdg WHERE pdg.pdg_pac = gih.gih_pac_regAND pdg.pdg_hsp = gih.gih_hsp_num AND pdg.pdg_hsp > 0 AND pdg.pdg_principal <> 'S' and pdg_cid not in ( SELECT min ( b.pdg_cid ) FROM pdg b WHERE b.pdg_pac= gih.gih_pac_reg AND b.pdg_hsp = gih.gih_hsp_num AND b.pdg_hsp > 0 AND b.pdg_principal <> 'S' ) ) as cid_cod_4 FROM pac , gih , cnv , emp emp_a , psv , cfg , cap , gcc , str , emp emp_b WHERE ( cap.cap_psv_assist = psv.psv_cod ) and ( cap.cap_cnv_cod = cnv.cnv_cod ) and ( cap.cap_pac_reg = pac.pac_reg ) and ( gih.gih_pac_reg = cap.cap_pac_reg ) and ( gih.gih_cap_num = cap.cap_num ) and ( emp_a.emp_cod = cap.cap_emp_cod_hosp ) and ( gcc.gcc_cod = cap.cap_gcc_cod ) and ( str.str_cod = gcc.gcc_str_cod ) and ( str.str_emp_cod = emp_b.emp_cod ) and ( ( cap.cap_pac_reg = 57849 ) and ( cap.cap_num = 1 ) and ( gih.gih_hsp_num_aut is null ) and ( gih.gih_num_prorrog is null ) No matter how hard I try I get the 'space for results table exhausted' error. I have a lot of free space (2Gb) in the dabase. I tried to create a lot of indexes, but there is no big table in this query. The largest table has 80.000 rows. I read in this article : http://lists.mysql.com/maxdb/12612 that the outer join above can be the problem. can you help me? Best regards, -- Daniel Castro - דניאל MSN: [EMAIL PROTECTED] Skype: danielti2005 ICQ : 316727989 Jabber: danielti Linux User: 387864 AOL User: Danielti2005 Sola Scriptura, Sola Gratia, Sola Fide, Solus Christus, Soli Deo Gloria
