* 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]
