Create new columns
Hi all, I'm trying to create new columns in a table with 308.000 records and 135 columns. It last 14 minuts to create one column. I make it trough MySQL4XManager. I don't think is a problem of MySQL4XManager. I guess is something about two many records and to many columns. Am I wrong? If I am wrong what can I do? Thank you in advanced. Elisenda Sala _ SetFile DATABASE FACTORY - Aplicaciones a Medida en FileMaker Pro ( Windows y Macintosh ) [EMAIL PROTECTED]www.setfile.netTEL 93 238 56 00 SetFile - FSA Partners http://www2.filemaker.fr/spain/developers/fsa_partners.html _ -- 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
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
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]
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]
