* Elisenda [...] > The explain select says as follows: [...] I re-formatted the query and the EXPLAIN output for readability:
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. 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.) 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? > 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? 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. <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. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
