Roger, > * Elisenda > [...] >> The explain select says as follows: > [...] > > I re-formatted the query and the EXPLAIN output for readability:
Sorry for not re-formatted the query, I've learn it for next time. Well, I've learn a lot with your lessons. > > 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 key key_len ref rows > FASE ref Participa 12 const,const 1157 > CA ref Centro 7 FASE.SQL_ID_CE 1 > PP ref PP_ID 7 FASE.PR_PP_ID_Coord 1 > CE ref Centro 7 CA.CA_ID_CE 10 > AU ref AU_AULA 256 FASE.AU_PR_Aula 264 > > (I removed the possible_keys and Extra columns) > > The first thing the EXPLAIN output tells us is in what order the server will > access the tables. In this case the FASE table is read first, then the CA > and PP tables are read based on the columns SQL_ID_CE and PR_PP_ID_Coord > from FASE (the 'ref' column), then the CE table is read based on the value > of CA.CA_ID_CE, and finally the AU table is read based on FASE.AU_PR_Aula. So, the good way to write joins will be as follows, doesn't it? FASE.SQL_ID_CE=CA.CA_ID_CE AND FASE.PR_PP_ID_COORD=PP.PP_ID_PP AND CA.CA_ID_CE=CE.CE_ID_CE AND FASE.AU_PR_AULA=AU.AU_AULA > > The 'rows' column hows approximately how many rows the server will have to > read. It is a goal to make/keep these numbers low, I don't know if you did a > EXPLAIN before you created you indexes, in that case you will see that the > numbers in the 'rows' column was higher, possibly as high as the row count > of the respective tables. A way to calculate how 'heavy' a select query is, > is to multiply these numbers. In the case above, the multiplum is > 1157*1*1*10*264 = 3054480. In other words, the server must examine > approximately 3 million rows to produce your result. (Note that this is an > estimate, based on statistics stored in the server. Running OPTIMIZE TABLE > will update these statistics, and this may also change the servers preferred > plan.) So, when explain select says this "CE ref Centro 7 CA.CA_ID_CE 10" , it isn't a good result for me, isn't it? Because CA it is supposed to have one record for each CE or the other way round. I have to review CA and CE. > The 'ref' column for FASE says 'const,const'. This means the index used > (Participa) is a combined index used to match two constants, presumably the > SQL_ID_PY and PR_FLAG. Is the number 1157 close to correct? Yes, it is correct. I have 1.157 record which match SQL_ID_PY=P081 and PR_flag=1 > >> 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. > > I don't understand... How many FASE records with PR_flag=1 and > SQL_ID_PY='P081'? You say when you join FASE and CA on those criteria you > get 253 rows, but you should get 753? I mean that in my database I have 753 records that match this critera (SQL_ID_PY=P081 and PR_flag=1 and CA_ID_Idioma=6) but the result of mysql gives me only 253. > > In general, if you get 'too few' rows on a multi-table join like the one you > are doing here, it could be because some of the tables you join to does not > have a corresponding row for the criteria. If that is the case, and you > still want those rows to show up in the result, you can use LEFT JOIN for > those tables. > > <URL: http://www.mysql.com/doc/en/JOIN.html > > >> 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? > > I don't know. Check each table separately, use SELECT COUNT(*) FROM ... > WHERE ... to check how many rows match any given criteria. Try to use the > output of EXPLAIN SELECT to manually do what the server will be doing, and > see if you get any unexpected results. Run OPTIMIZE TABLE to refresh the > index statistics. I will do it. > > <URL: http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html > > > For further speed improvements: Your AU_AULA and CA.Centro indexes could be > replaced by combined indexes, (AU_AULA,AU_NIVEL) and > (CA_ID_CE,CA_ID_IDIOMA_A) respectively. Your column CA.CA_ID_IDIOMA_A is > defined as an integer, in your WHERE clause you should check for integer 6, > not string '6'. This also applies to FASE.PR_FLAG. The AU.AU_NIVEL column > was not mentioned in your previous table description, so I don't know if > it's numeric or a string. If it is numeric, you should not use quotes on the > constant. It will work, but the server must convert from string to integer, > this take some time, using a constant of a type matching the column type is > faster. It is a great discovery. As you can see I use quotes in every constant. About AU_NIVEL, I defined it as AU_NIVEL char(6), because the data has always a dot in it (13.14, 14.15,...) I will create this combined indexes. And I will try again. Thank you very much for your help and patients. Elisenda > > -- > Roger > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
