I'm sorry I didn't explain anything. The problem is that it 's too slow and the result doesn't appear. I am going to try to explain the query.
Fields from Table FASE: (300.000 records) ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, SQL_ID_PY char(6), SQL_ID_CE char(6), PR_flag INT, PR_Date_Visita_2 Date, AU_PR_Aula varchar(255) (it a field that contain SQL_ID_PY_SQL_ID_CE_PR) Field from Table CE (30.000 records) CE_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, CE_ID_CE char(6), CE_Centro varchar(32), CE_Domicilio varchar(32), CE_CP varchar(5), CE_Poblacion varchar(30), CE_ID_Capital char(2), CE_Capital varchar(30), CE_ID_PROV char(2), CE_PROV varchar(15), CE_ID_CCAA char(2), CE_CCAA varchar(15) Field from Table CA (30.000 records) CA_ID INT NOT NULL PRIMARY KEY, CA_ID_User char(6), CA_ID_CE char(6), CA_Centro varchar(32), CA_ID_Idioma_A INT, CA_Horario varchar(30) Fields from table AU (700.000 records) AU_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, AU_ID_CE char(6), AU_ID_PY char(6), AU_ID_FASE INT, AU_A_M INT, AU_A_F INT, AU_Aula varchar(32) (it a field that contain AU_ID_PY_AU_ID_CE_PR) Fields from table PP (200.000 records) PP_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, PP_ID_PP char(6), PP_ID_CE char(6), PP_Contacto char(50), PP_ID_Cargo char(6), PP_Cargo char(32) I select from Fase some records. From fase I only want records (SQL_ID_CE) that have FASE.SQL_ID_PY='P081' AND FASE.PR_FLAG= '1'. From this selection, I only want records that in AU have AU.AU_NIVEL= '13.14' and in CA have CA.CA_ID_IDIOMA_A= '6'. In WHERE I write AU.AU_Aula= fase.AU_PR_Aula AND AU.AU_ID_CE = CA.CA_ID_CE AND CE.CE_ID_CE = CA.CA_ID_CE AND CE.CE_ID_CE = Fase.SQL_ID_CE AND CE.CE_ID_CE = PP.PP_ID_CE AND Fase.PR_PP_ID_Coord = PP.PP_ID_PP Main relation in all tables is SQL_ID_CE. I don't know if I explain myself or it is too boring to continue reading. It will be fantastic if some can help me. I don't know if I am doing something wrong or what. > * Elisenda >> I have a query which tries to select different fields from 5 different >> tables. >> >> In WHERE part I have write all the conditions and relationships. >> Perhaps two many..... > > Joining 5 tables should not be a problem, but having indexes on the relevant > columns may be essential, especially on large tables. > >> The main table for me is FASE. From this table I try to find all the other >> information. >> >> I guess I'm doing something wrong but I don't know what. > > What is the problem? Do you get an error message, does it return unexpected > results, or is it just too slow? > >> SELECT >> >> CE.CE_CENTRO, >> CE.CE_DOMICILIO, >> CE.CE_CP, >> CE.CE_POBLACION, >> CE.CE_PROV, >> PP.PP_CONTACTO, >> PP.PP_CARGO, >> CA.CA_HORARIO, >> AU.AU_A_M, >> AU.AU_A_F, >> FASE.PR_DATE_VISITA_1 >> >> FROM AU, CA, CE,FASE,PP >> >> WHERE >> >> FASE.SQL_ID_PY='P081' AND >> FASE.PR_FLAG= '1' AND >> CA.CA_ID_IDIOMA_A= '6' AND >> AU.AU_NIVEL= '13.14' AND >> AU.AU_Aula= fase.AU_PR_Aula AND >> AU.AU_ID_CE = CA.CA_ID_CE AND >> CE.CE_ID_CE = CA.CA_ID_CE AND >> CE.CE_ID_CE = Fase.SQL_ID_CE AND >> CE.CE_ID_CE = PP.PP_ID_CE AND >> Fase.PR_PP_ID_Coord = PP.PP_ID_PP > > It's difficult to suggest changes without knowing what the problem is... :) > I can however safely suggest that you use a consistent letter casing on your > table names... is it FASE, Fase or fase? On some MySQL servers this will > make a difference, on others it may not. (I think mysql on windows is case > insensitive by default, but this may be changed at compile-time, iirc.) > > Please tell us what the problem is, and if it's about efficiency, post the > output of "EXPLAIN SELECT <your_query>", that should get us started. :) > > -- > Roger > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
