Re: Some help with a complex query
* Elisenda [...] 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 Those are the exact same criteria as you had, only in a different order, isn't it? The order of these criteria within the WHERE clause does not matter, the server will select the optimal way to join, which is what is expressed in the EXPLAIN SELECT output. You also changed CE.CA_ID_CE=CA.CE_ID_CE to CA.CA_ID_CE=CE.CE_ID_CE, this is the same thing, it makes no difference. [...] So, when explain select says this CE ref Centro 7CA.CA_ID_CE10 , it isn't a good result for me, isn't it? That depends... Because CA it is supposed to have one record for each CE or the other way round. ...then it is not good. You would have expected a 1 in the last column. I have to review CA and CE. yes. :) [...] 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. I still don't get it. When you say in my database, which database are you talking about? How do you know you have 753 when mysql (=database?) says you have 253? And what do you mean by records that match, the CA_ID_Idioma column is from a different table, isn't it? [...] Thank you very much for your help and patients. You're welcome. :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some help with a complex query
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 7FASE.SQL_ID_CE 1 PP ref PP_ID 7FASE.PR_PP_ID_Coord 1 CE ref Centro 7CA.CA_ID_CE10 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 7CA.CA_ID_CE10 , 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
Re: Some help with a complex query
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 keykey_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 7FASE.SQL_ID_CE 1 Using where PP ref PP_ID PP_ID 7FASE.PR_PP_ID_Coord 1 Using where CE ref Centro Centro 7CA.CA_ID_CE 10Using where AU ref Nivel_FASE,AU_AULA,Au_Nivel AU_AULA256 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
Re: Some help with a complex query
* 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 7FASE.SQL_ID_CE 1 PP ref PP_ID 7FASE.PR_PP_ID_Coord 1 CE ref Centro 7CA.CA_ID_CE10 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]
Re: Some help with a complex query
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]
Re: Some help with a complex query
* 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Some help with a complex query
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. 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. 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 Thank you fro your help in advanced. Eli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some help with a complex query
* 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]
Re: Some help with a complex query
it would help alot if you dump the table structure for us - Original Message - From: Elisenda To: [EMAIL PROTECTED] Sent: Tuesday, November 11, 2003 7:10 PM Subject: Some help with a complex query 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. 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. 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 Thank you fro your help in advanced. Eli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
