Roger, Your help was fantastic. It seems that it goes better. At the end I achieve some result but not all I need.
The explain select says as follows: EXPLAIN Select FASE.PR_Date_Visita_2, CE.CE_Centro, CE.CE_Domicilio, CE.CE_CP, CE.CE_Poblacion, CE.CE_Capital, CE.CE_PROV, CE.CE_CCAA,CA.CA_Horario, PP.PP_Contacto, PP.PP_Cargo, AU.AU_A_M, AU.AU_A_F >From FASE,CE,CA,PP,AU 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 FASE.SQL_ID_CE=CE.CE_ID_CE AND FASE.SQL_ID_CE=CA.CA_ID_CE AND CE.CE_ID_CE=CA.CA_ID_CE AND FASE.PR_PP_ID_COORD=PP.PP_ID_PP AND FASE.AU_PR_AULA=AU.AU_AULA\ table type possible_keys key key_len ref rows Extra FASE ref Proyecto,Folleto,Solicitud,Participa,Seguimiento,Ganador,Solicitud_CCAA,Soli citud_PROV,TipoSL_CCAA,TipoSL_PROV,SG_Recibibido_CCAA,SG_Recibibido_PROV,PR_ Aula,SL_Categoria_CCAA,Centro Participa 12 const,const 1157 Using where CA ref Centro,IDIOMA_A Centro 7 FASE.SQL_ID_CE 1 Using where PP ref PP_ID PP_ID 7 FASE.PR_PP_ID_Coord 1 Using where CE ref Centro Centro 7 CA.CA_ID_CE 10 Using where AU ref Nivel_FASE,AU_AULA,Au_Nivel AU_AULA 256 FASE.AU_PR_Aula 264 Using where What I know is that I have 753 records which match FASE.PR_flag=1 and FASE.SQL_ID_PY=P081 and CA.CA_ID_Idioma_A=6 and my result is 253 records. Does it have to be with my query? Or does it have to be with data in mysql, I mean I didnšt insert them allright? eli > * Elisenda >> The problem is that it 's too slow and the result doesn't appear. >> I am going to try to explain the query. > > Please do that using the EXPLAIN SELECT statement: > > <URL: http://www.mysql.com/doc/en/EXPLAIN.html > > > This will show what index is beeing used on the different joins, and > approximately how many rows the server must handle to produce your result. I > suspect that in this case there are no index on some of the columns beeing > used for the joins, whih means the server must scan the entire table > multiple times. This will often result in a query that appears to 'hang', no > result is returned. The server is actually working on the result, but it > will take 'forever', you will normally kill your connection before you > recieve anything. > > More below... > >> 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) > > There seems to be only primary keys on these tables? No other index defined? > If that is the case, this is probably the reason of your problem. Put an > index on any column used to join other tables, the so-called foreign keys. > >> 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, > > You can create a combined index on SQL_ID_PY and PR_FLAG: > > CREATE INDEX SQL_ID_PY_PR_FLAG_INDEX ON FASE (SQL_ID_PY,PR_FLAG) > >> I only want records that in AU have AU.AU_NIVEL= '13.14' and in CA have >> CA.CA_ID_IDIOMA_A= '6'. > > Then probably both AU.AU_NIVEL and CA.CA_ID_IDIOMA_A should be indexed. > >> 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. > > Then all columns related to SQL_ID_CE should have an index. Probably also > some of the other fields mentioned above: AU.AU_Aula, fase.AU_PR_Aula, > AU.AU_ID_CE, CA.CA_ID_CE, CE.CE_ID_CE, Fase.SQL_ID_CE, PP.PP_ID_CE, > Fase.PR_PP_ID_Coord, PP.PP_ID_PP. > > MySQL will not use more than one index per table per select statement, but > which index to use may vary, depending on the criteria and the distribution > of your data. In what order the tables are read, will also vary. Don't be > afraid of indexing "too many" columns, you can easily remove any unused > index after you have identified which you really need. The EXPLAIN SELECT > statement will let you identify the actual index used, but you may need to > test with various data, because of the internal join optimizer behaviour > mentioned above, the index used may change depending on your criteria/data. > > By the way, why don't you use the primary keys? It is very common to use the > primary keys for some of the joins when joining many tables. For instance, > you join the PP table using PP_ID_PP and Fase.PR_PP_ID_Coord, is it supposed > to return multiple PP rows for each Fase row? If you only except one, i.e. > PP_ID_PP is unique in the PP table, then you should have a UNIQUE index on > it, or promote it to primary key, or maybe use the primary key in place of > this column? > >> I don't know if I explain myself or it is too boring to continue reading. > > I'm not bored. :) > >> It will be fantastic if some can help me. I don't know if I am doing >> something wrong or what. > > I think you only need indexing. Run EXPLAIN SELECT first, save the output > (or post it here), put on some indexes, run EXPLAIN SELECT again, and see > the difference. When all the numbers in the 'rows' column of the explain > select output multiplied together is a relatively low number, your query > should be fast... please include the result of EXPLAIN SELECT if you have > more questions/problems. > > -- > Roger > >
