Re: reorder records in database
What about: ALTER TABLE tablename ORDER BY fieldname; HTH, Cor - Original Message - From: Rob Wultsch [EMAIL PROTECTED] To: afan pasalic [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, May 15, 2008 8:23 PM Subject: Re: reorder records in database On Thu, May 15, 2008 at 11:38 AM, afan pasalic [EMAIL PROTECTED] wrote: is there built in function to reset order_no or I have to create php script for it? There is not a built in way to do this. Using a user defined variable it is not all that hard to deal with though mysql drop table if exists t1; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE `t1` ( - `col1` int, - `col2` int - ); Query OK, 0 rows affected (0.03 sec) mysql insert into t1 values(0,1),(3,2),(5,3),(7,4),(9,5),(11,6),(15,7); Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql mysql SET @incr =0; Query OK, 0 rows affected (0.00 sec) mysql mysql UPDATE t1 - SET col1 =(SELECT @incr:[EMAIL PROTECTED]) - ORDER BY col1; Query OK, 7 rows affected (0.00 sec) Rows matched: 7 Changed: 7 Warnings: 0 mysql mysql select * from t1; +--+--+ | col1 | col2 | +--+--+ |1 |1 | |2 |2 | |3 |3 | |4 |4 | |5 |5 | |6 |6 | |7 |7 | +--+--+ 7 rows in set (0.00 sec) -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why is explain.key-len not size of key field ?
Hi All, I have a dictionary table like: CREATE TABLE IF NOT EXISTS `mydictionary` ( `EN` varchar(36) default NULL, `DE` varchar(36) default NULL, `ES` varchar(36) default NULL, `FR` varchar(36) default NULL, `IT` varchar(36) default NULL, `NL` varchar(36) default NULL, `PT` varchar(36) default NULL, KEY `EN` (`EN`), KEY `DE` (`DE`), KEY `ES` (`ES`), KEY `FR` (`FR`), KEY `IT` (`IT`), KEY `NL` (`NL`), KEY `PT` (`PT`) ) ENGINE=MyISAM DEFAULT CHARSET = UTF8; When I run the query: EXPLAIN SELECT `EN`, `FR` FROM `mydictionary` WHERE `FR` Like comp% ORDER BY `FR`; EXPLAIN says: select type = SIMPLE, type = range, key = FR, key-len = 111, ref = NULL, extra = using where Any idea why key-len is 111 ? Thanks, Cor
Re: How to know the maximum length of a field
Hi Charles, to get max . # characters: SELECT MAX(CHAR_LENGTH(fieldname)) AS `MaxChars`; to get max. # bytes: SELECT MAX(LENGTH(fieldname)) AS `MaxBytes`; HTH, Cor - Original Message - From: Charles Lambach [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 29, 2008 1:21 PM Subject: How to know the maximum length of a field Hi. I've got a table with some fields, which I created with VARCHAR(100). I want to optimize this table, and I want to modify the length of these fields from VARCHAR(100) to VARCHAR(maximum_length), where maximum_length is the length of the record with the longest field. I could create a Perl script to do it, but I wonder if there's a mySQL command which does it automatically. Thank you very much, --Charles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why is explain.key-len not size of key field ?
- Original Message - From: Rob Wultsch [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, April 29, 2008 2:24 PM Subject: Re: why is explain.key-len not size of key field ? On Tue, Apr 29, 2008 at 5:12 AM, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi All, I have a dictionary table like: CREATE TABLE IF NOT EXISTS `mydictionary` ( `EN` varchar(36) default NULL, `DE` varchar(36) default NULL, `ES` varchar(36) default NULL, `FR` varchar(36) default NULL, `IT` varchar(36) default NULL, `NL` varchar(36) default NULL, `PT` varchar(36) default NULL, KEY `EN` (`EN`), KEY `DE` (`DE`), KEY `ES` (`ES`), KEY `FR` (`FR`), KEY `IT` (`IT`), KEY `NL` (`NL`), KEY `PT` (`PT`) ) ENGINE=MyISAM DEFAULT CHARSET = UTF8; When I run the query: EXPLAIN SELECT `EN`, `FR` FROM `mydictionary` WHERE `FR` Like comp% ORDER BY `FR`; EXPLAIN says: select type = SIMPLE, type = range, key = FR, key-len = 111, ref = NULL, extra = using where Any idea why key-len is 111 ? Thanks, Cor Your using a multi byte character set. Rob Wultsch [EMAIL PROTECTED] Thanks Rob, So key-len is expressed in bytes and not in characters. But that means that each normal character (a..z) is 3 bytes in UTF8 ? TIA, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why is explain.key-len not size of key field ?
- Original Message - From: Rob Wultsch [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, April 29, 2008 2:44 PM Subject: Re: why is explain.key-len not size of key field ? On Tue, Apr 29, 2008 at 7:39 AM, C.R.Vegelin [EMAIL PROTECTED] wrote: - Original Message - From: Rob Wultsch [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, April 29, 2008 2:24 PM Subject: Re: why is explain.key-len not size of key field ? On Tue, Apr 29, 2008 at 5:12 AM, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi All, I have a dictionary table like: CREATE TABLE IF NOT EXISTS `mydictionary` ( `EN` varchar(36) default NULL, `DE` varchar(36) default NULL, `ES` varchar(36) default NULL, `FR` varchar(36) default NULL, `IT` varchar(36) default NULL, `NL` varchar(36) default NULL, `PT` varchar(36) default NULL, KEY `EN` (`EN`), KEY `DE` (`DE`), KEY `ES` (`ES`), KEY `FR` (`FR`), KEY `IT` (`IT`), KEY `NL` (`NL`), KEY `PT` (`PT`) ) ENGINE=MyISAM DEFAULT CHARSET = UTF8; When I run the query: EXPLAIN SELECT `EN`, `FR` FROM `mydictionary` WHERE `FR` Like comp% ORDER BY `FR`; EXPLAIN says: select type = SIMPLE, type = range, key = FR, key-len = 111, ref = NULL, extra = using where Any idea why key-len is 111 ? Thanks, Cor Your using a multi byte character set. Rob Wultsch [EMAIL PROTECTED] Thanks Rob, So key-len is expressed in bytes and not in characters. But that means that each normal character (a..z) is 3 bytes in UTF8 ? TIA, Cor Max memory usage is 3 bytes in MySQL. I am not an expert on this subject. Take a look at the following: http://en.wikipedia.org/wiki/UTF-8 http://www.xaprb.com/blog/2006/04/17/max-key-length-in-mysql/ -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) The 2nd link gave the answer stating: MySQL must be pessimistic and assume the worst-case scenario of every character requiring 3 bytes. Thanks Rob. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
data truncation warnings by special characters
Hi List, I get strange Data truncated for column Description warnings when loading a tab separated file with special characters. The definition of the target table is: CREATE TEMPORARY TABLE tmp ( Code CHAR(8) NOT NULL, Description TEXT NOT NULL, KEY Code (Code) ) ENGINE = MyISAM CHARSET=UTF8; The Load Into statement I use is: LOAD DATA INFILE 'D:/Input/Data.txt' INTO TABLE tmp FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 0 LINES; The truncation warnings are caused by words like PURÉES. This leads to a truncted Description field like: Jams, Jellies, Pur. Any idea how to solve this ? Thanks in advance, Cor
Re: data truncation warnings by special characters
Hi Jerry, Sorry, I should have mentioned that I use Windows XP with MySQL 5.0.15nt. The script giving errors now is used once a year, and last year without any warning. Since last year all I changed was adding in my.ini: # in [client] part default-character-set=utf8 # in [mysqld] part default-character-set=utf8 character-set-server = utf8 collation-server = utf8_general_ci When I undo these changes, and stop and start mysql, the errors remain. The Data.txt file (from an external source) looks okay with Wordpad. TIA, Cor - Original Message - From: Jerry Schwartz [EMAIL PROTECTED] To: 'C.R.Vegelin' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, April 18, 2008 2:30 PM Subject: RE: data truncation warnings by special characters -Original Message- From: C.R.Vegelin [mailto:[EMAIL PROTECTED] Sent: Friday, April 18, 2008 8:42 AM To: mysql@lists.mysql.com Subject: data truncation warnings by special characters Hi List, I get strange Data truncated for column Description warnings when loading a tab separated file with special characters. [JS] This sounds like it is related to the problems I've been having. I think I've come to an understanding of my issues, but I am not using LOAD DATA INFILE. I'll be posting what I have learnt, once I get a chance, but I don't know if it will help you. What is the source of your input data? Windows? Linux? The definition of the target table is: CREATE TEMPORARY TABLE tmp ( Code CHAR(8) NOT NULL, Description TEXT NOT NULL, KEY Code (Code) ) ENGINE = MyISAM CHARSET=UTF8; The Load Into statement I use is: LOAD DATA INFILE 'D:/Input/Data.txt' INTO TABLE tmp FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 0 LINES; The truncation warnings are caused by words like PURÉES. This leads to a truncted Description field like: Jams, Jellies, Pur. Any idea how to solve this ? Thanks in advance, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data truncation warnings by special characters [SOLVED]
Thanks Chris, Well the script in question is a MySQL script to load a MySQL database. Currently I use only PHP only for database retrieval. But after trial and error I managed to find a 'work around', as follows: a) CREATE TABLE tmp with ENGINE = MyISAM CHARSET = LATIN1; b) LOAD DATA INFILE statement; = no truncation warnings c) ALTER TABLE tmp CONVERT TO CHARACTER SET UTF8; Thanks again and a nice weekend. Cor - Original Message - From: Chris W [EMAIL PROTECTED] To: MYSQL General List mysql@lists.mysql.com Sent: Friday, April 18, 2008 8:38 PM Subject: Re: data truncation warnings by special characters I have some php code I use to import data that is a bit more flexible and robust than the load data statement in MySQL If you use php I can share the code with you. C.R.Vegelin wrote: Hi Jerry, Sorry, I should have mentioned that I use Windows XP with MySQL 5.0.15nt. The script giving errors now is used once a year, and last year without any warning. Since last year all I changed was adding in my.ini: # in [client] part default-character-set=utf8 # in [mysqld] part default-character-set=utf8 character-set-server = utf8 collation-server = utf8_general_ci When I undo these changes, and stop and start mysql, the errors remain. The Data.txt file (from an external source) looks okay with Wordpad. TIA, Cor - Original Message - From: Jerry Schwartz [EMAIL PROTECTED] To: 'C.R.Vegelin' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, April 18, 2008 2:30 PM Subject: RE: data truncation warnings by special characters -Original Message- From: C.R.Vegelin [mailto:[EMAIL PROTECTED] Sent: Friday, April 18, 2008 8:42 AM To: mysql@lists.mysql.com Subject: data truncation warnings by special characters Hi List, I get strange Data truncated for column Description warnings when loading a tab separated file with special characters. [JS] This sounds like it is related to the problems I've been having. I think I've come to an understanding of my issues, but I am not using LOAD DATA INFILE. I'll be posting what I have learnt, once I get a chance, but I don't know if it will help you. What is the source of your input data? Windows? Linux? The definition of the target table is: CREATE TEMPORARY TABLE tmp ( Code CHAR(8) NOT NULL, Description TEXT NOT NULL, KEY Code (Code) ) ENGINE = MyISAM CHARSET=UTF8; The Load Into statement I use is: LOAD DATA INFILE 'D:/Input/Data.txt' INTO TABLE tmp FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 0 LINES; The truncation warnings are caused by words like PURÉES. This leads to a truncted Description field like: Jams, Jellies, Pur. Any idea how to solve this ? Thanks in advance, Cor -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT ascending incremental values
Hi Waynn, Try: SET @row := 0; SELECT @row := @row + 1 AS Rank, UserId, count(*) as NumActions from Actions group by UserId order by NumActions desc limit 10; HTH, Cor - Original Message - From: Waynn Lue [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, February 22, 2008 10:38 AM Subject: SELECT ascending incremental values I'm doing a top 10 list from a database based on a group by and order by, something like select UserId, count(*) as NumActions from Actions group by UserId order by NumActions desc limit 10; Is there any way to put a separate column so I get results like Rank, UserId, NumActions 1, 123, 43 2, 9844, 40 3, 88, 34 4, 144240, 20 etc.? Waynn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to combine MYISAM to MERGE tables ...
- Original Message - From: mos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 18, 2008 6:48 PM Subject: Re: Best way to combine MYISAM to MERGE tables ... At 11:33 AM 2/18/2008, you wrote: Hi All, I am working with MYISAM tables split by year, like: data2003, data2004, data2005, data2006, data2007, data2008, all having the same definitions. To speed up the query process, I also defined MERGE tables, like: CREATE TABLE data20032004 ... ENGINE=MERGE UNION (data2003, data2004); CREATE TABLE data20032005 ... ENGINE=MERGE UNION (data2003, data2004, data2005); CREATE TABLE data20032006 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006); CREATE TABLE data20032007 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007); CREATE TABLE data20032008 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007, data2008); CREATE TABLE data20042005 ... ENGINE=MERGE UNION (data2004, data2005); etc. etc. CREATE TABLE data20072008 ... ENGINE=MERGE UNION (data2007, data2008); For example, if a user query applies to 2003..2006, my app selects the belonging MERGE table data20032006, excluding tables data2007 and data2008 for faster results. Is this assumption correct ? Or are there better ways of doing this ? I'm using: mySQL version 5.0.15-NT TIA, Cor Cor, If the date is indexed, I don't think you're going to have a problem referencing a merge table Data_All based on all the tables. That's what I do with 25 tables (15 million rows) and it is quite fast. It also of course uses the query cache so subsequent queries are instant. Mike Thanks Mike, In my app I can't to use Year as Indexed field, because my app has multi-column keys with (5) higher selectivity fields. Key on Year would be an option, if MySQL search engine could use N separate keys. By the way, I'm using about 120 million rows in 10 tables. I will keep your experience in mind, thanks. Regards, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best way to combine MYISAM to MERGE tables ...
Hi All, I am working with MYISAM tables split by year, like: data2003, data2004, data2005, data2006, data2007, data2008, all having the same definitions. To speed up the query process, I also defined MERGE tables, like: CREATE TABLE data20032004 ... ENGINE=MERGE UNION (data2003, data2004); CREATE TABLE data20032005 ... ENGINE=MERGE UNION (data2003, data2004, data2005); CREATE TABLE data20032006 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006); CREATE TABLE data20032007 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007); CREATE TABLE data20032008 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007, data2008); CREATE TABLE data20042005 ... ENGINE=MERGE UNION (data2004, data2005); etc. etc. CREATE TABLE data20072008 ... ENGINE=MERGE UNION (data2007, data2008); For example, if a user query applies to 2003..2006, my app selects the belonging MERGE table data20032006, excluding tables data2007 and data2008 for faster results. Is this assumption correct ? Or are there better ways of doing this ? I'm using: mySQL version 5.0.15-NT TIA, Cor
Re: Single Column Indexes Vs. Multi Column
Sebastian, MySQL uses only one index for searching. As far as I know this applies to all MySQL versions, right ? Regards, Cor - Original Message - From: Sebastian Mendel [EMAIL PROTECTED] To: Michael Stearne [EMAIL PROTECTED]; MySQL List mysql@lists.mysql.com Sent: Thursday, January 10, 2008 6:57 AM Subject: Re: Single Column Indexes Vs. Multi Column Michael Stearne schrieb: For a query like: SELECT id FROM properties WHERE `Country` = 'USA' AND Type='Residential' Is an multi-column index that is (Country, Type) better or worse or the same as a single index Country and another single index Type. better two single indexes depending on your MySQL version will not be used. -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: charset
Hi, Try the format below: ALTER DATABASE mybase DEFAULT CHARACTER SET = latin1; ALTER DATABASE mybase DEFAULT COLLATE = latin1_swedish_ci; HTH ... - Original Message - From: Mário Gamito [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Monday, April 09, 2007 4:19 PM Subject: charset Hi, I have this database with utf-8 charset. How can i change it to latin1 ? I've seen the MySQL pages and run in the MySQL prompt: ALTER DATABASE telbit CHARACTER SET latin1 COLLATE latin1_swedish_ci; but i get an error saying that i have an error in my syntax. Any help would be appreciated. Warm Regards -- :wq! Mário Gamito -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Illegal mix of collations ...
I get Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'. in a query like (SELECT ...) UNION (SELECT * INTO OUTFILE ...) to build a CSV file. The database has default charset UTF8 and collation utf8_general_ci and all query tables have default charset UTF8 and collation utf8_general_ci MySQL client says for show variables character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_results utf8 character_set_serverutf8 character_set_system utf8 collation_connectionutf8_general_ci collation_database utf8_general_ci collation_server utf8_general_ci I'm using MySQL 5.0.15. Any idea why I get this Illegal mix error and how to solve it ? TIA, Cor
question about Queries per second avg
Hi List, Using printf( System status: %s\n, mysqli_stat($link)); in a PHP script, says: Queries per second avg: 0.051. This means that a query takes about 20 seconds ? But the query result is given immediately. How should I interpret Queries per second avg ? I'm using version 5.0.15 NT. Regards, Cor
Re: question about Queries per second avg
Thanks Christophe, Question rephrased: how do you explain 0.05 q/s = 20 s/q with an immediate response ? Cor - Original Message - From: Christophe Gregoir [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, April 06, 2007 8:35 AM Subject: Re: question about Queries per second avg C.R.Vegelin wrote: ... How should I interpret Queries per second avg ? How about as 'queries per second on average' :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT EMPTY, like NOT NULL
Have a look at the HAVING clause ...
- Original Message -
From: js [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, February 12, 2007 2:48 PM
Subject: NOT EMPTY, like NOT NULL
Hi list,
A silly question.
Is it possible to prevent empty value('') from appearing in a field?
I can solve this by using subquery or trigger,
but it's a little bit painful.
The simpler the better :)
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
running sum with a @variable
I want to calc a running sum with @variables. Using the command line client, I enter: SET @row := 0, @runsum := 0; followed by: SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country` , ROUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1` , @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) AS `RunSum` FROM data2006 AS db LEFT JOIN mycountries ON db.Country = mycountries.ID WHERE ... GROUP BY db.Country; Results are: Row Country Q1RunSum 1 Germany 9090 2 France 6060 3 Norway 2424 etc. I expect the RunSum for Germany 60, France 150, Norway 174 etc. Whay am I doing wrong ? Any help is appreciated ! Regards, Cor
Re: running sum with a @variable
Thanks Lars, Dusan, I found out that the problem is caused by an ORDER BY clause, left out in my example because I had no idea this would be the problem. It works fine with LEFT JOIN and GROUP BY. However, I need the ORDER BY ... Any more suggestions to work around ? Thanks, Cor - Original Message - From: Lars Schwarz [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 06, 2007 12:58 PM Subject: Re: running sum with a @variable oops, sorry, forget the last post i made. it's not related to the group by. it should work like that from my point of view. a stripped down version of this works for me (tried without the left join) On 2/6/07, Lars Schwarz [EMAIL PROTECTED] wrote: i suppose this to be working when you leave the group by? On 2/6/07, C.R.Vegelin [EMAIL PROTECTED] wrote: I want to calc a running sum with @variables. Using the command line client, I enter: SET @row := 0, @runsum := 0; followed by: SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country` , ROUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1` , @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) AS `RunSum` FROM data2006 AS db LEFT JOIN mycountries ON db.Country = mycountries.ID WHERE ... GROUP BY db.Country; Results are: Row Country Q1RunSum 1 Germany 9090 2 France 6060 3 Norway 2424 etc. I expect the RunSum for Germany 60, France 150, Norway 174 etc. Whay am I doing wrong ? Any help is appreciated ! Regards, Cor -- Lars Schwarz Gottorpstrasse 20 26122 Oldenburg T 0441 2171 354 0 F 0441 2171 354 0 M 0179 512 4628 -- Lars Schwarz Gottorpstrasse 20 26122 Oldenburg T 0441 2171 354 0 F 0441 2171 354 0 M 0179 512 4628 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: making graphs with MySQL data
Thanks El Cuy, Joshua, Ryan, Let me be more specific about making graphs with MySQL data. Currently my project is in development phase, using MS Access as front-end and MySQL as back-end with MyODBC. My next step is to replace the MS Access front-end by PHP (because I don't see any use of MS Access for a website). However, I don't have any experience with PHP yet. Before moving to PHP, I would like to test some chart possibilities, with bar charts, line charts etc. I am thinking of a charting engine as a runtime component, that makes charts depending on passed parameters, such as: a) the result of a MySQL query or the query itself b) parameters to specify the charttype, title etc. Ryan wrote that MS Access/MyODBC could do it, but how ? Joshua wrote that MS Excel (and OO Calc) could do it, but then I have to instruct MS Excel how to chart per query ? To give an idea of my chart needs: http://www.ecb.int/stats/exchange/eurofxref/html/eurofxref-graph-usd.en.html Regards, Cor - Original Message - From: El Cuy Volador [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Saturday, January 13, 2007 1:29 AM Subject: Re: making graphs with MySQL data C.R.Vegelin escribió: Dear List, I have a MySQL database (V5.0.x) and I need to make graphs. Does anyone know about good utilities to make graphs ? I would appreciate your expertise or links. TIA, Cor A very good one is BIRT (http://www.eclipse.org/birt/phoenix/) to be deployed in a Tomcat or any other Java server. You can see some tutorials and video tutorials in the examples section of the page. Very easy to use. If you are using PHP, an option is jpgraph (http://www.aditus.nu/*jpgraph*/). Not very complete, and quite difficult to deploy. A commercial solution is Fusion Charts (http://www.infosoftglobal.com/FusionCharts/). Try to be a little bit more explicit about the language you are using ant the kind of graphs you need to generate. Hope this links help you, Regards, Alvaro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
making graphs with MySQL data
Dear List, I have a MySQL database (V5.0.x) and I need to make graphs. Does anyone know about good utilities to make graphs ? I would appreciate your expertise or links. TIA, Cor
how to get (lapse) time in microseconds ?
Hi List, I need the lapse time in microseconds. I have tried various things, like: SELECT TIME_FORMAT(CURTIME(), '%f'); SELECT TIME_FORMAT(NOW(), '%f'); SELECT MICROSECOND(CURTIME()); but all I get is 0. What am I doing wrong ? TIA, Cor
Re: how to get (lapse) time in microseconds ?
Thanks Chris,
You're right, I reversed the arguments. However,
SELECT TIME_FORMAT('%f',CURTIME());
gives normal time format like 17:37:47
SELECT TIME_FORMAT('%f',NOW());
gives normal date/time format.
SELECT MICROSECOND(CURTIME());
gives 0.
I need microseconds to get the query runtime.
But a format like 0.05 sec is also right for me.
Maybe any other ideas ?
- Original Message -
From: Chris White [EMAIL PROTECTED]
To: C.R.Vegelin [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Friday, January 05, 2007 3:58 PM
Subject: Re: how to get (lapse) time in microseconds ?
C.R.Vegelin wrote:
Hi List,
I need the lapse time in microseconds.
I have tried various things, like:
SELECT TIME_FORMAT(CURTIME(), '%f');
SELECT TIME_FORMAT(NOW(), '%f');
Your arguments are reversed. It's:
SELECT TIME_FORMAT('%f',CURTIME());
SELECT TIME_FORMAT('%f',NOW());
SELECT MICROSECOND(CURTIME());
Don't know about this one though.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SUM() of 1 and NULL is 1 ?
Hi List, I need to SUM() on months from a table like: CREATE TABLE `data` ( `Jan` float default NULL, ... ) ENGINE=MyISAM; # V 5.0.15 Months may have NULL values, like: INSERT INTO data (Jan) VALUES (1), (NULL); However, when I use SELECT SUM(Jan) AS Jan, the returned value is 1 in stead of NULL. How to get a result NULL when in such a case ? TIA, Cor
Re: SUM() of 1 and NULL is 1 ?
Thanks Visolve, Peter, This is a serious matter, because: - within a row: 1 + NULL = NULL - across rows with SUM(): 1 + NULL = 1 I know the manual says that group functions ignore NULL values (12.10.1), but it also says: Conceptually, NULL means a missing unknown value (3.3.4.6). IMHO a NULL with any value should always add to NULL. I was hoping for an option / setting to change NULL behaviour. Well, I will try the suggested alternatives. Thanks, Cor
Why incomplete Cardinalities with MERGE tables ?
I have some base tables, called data2004, data2005 etc. They all have the following structure: CREATE TABLE IF NOT EXISTS `data200X` ( F1 int unsigned NOT NULL default '0', F2 smallint unsigned NOT NULL default '0', F3 smallint unsigned NOT NULL default '0', F4 tinyint unsigned NOT NULL default '0', F5 tinyint unsigned NOT NULL default '0', ... PRIMARY KEY (F1,F2,F3,F4,F5), KEY F2 (F2), KEY F3 (F3), KEY F4 (F4) ) ENGINE=MyISAM; For all these MyISAM tables SHOW INDEX gives ALL cardinalities, like: KeyName Column Cardinality Primary F19837 Primary F2220333 Primary F3 3892565 Primary F411677695 Primary F511677695 F2 F2 24 F3 F3241 F4 F4 31 I defined a MERGE table with 3 base tables, like: CREATE TABLE IF NOT EXISTS `data0406` ( F1 int unsigned NOT NULL default '0', F2 smallint unsigned NOT NULL default '0', F3 smallint unsigned NOT NULL default '0', F4 tinyint unsigned NOT NULL default '0', F5 tinyint unsigned NOT NULL default '0', ... KEY Combi (F1,F2,F3,F4,F5), KEY F2 (F2), KEY F3 (F3), KEY F4 (F4) ) ENGINE=MERGE UNION=(data2004, data2005, data2006); For this MERGE table SHOW INDEX gives NOT ALL cardinalities: KeyName Column Cardinality Primary F1 30143 Primary F2 686726 Primary F3 12589987 Primary F4 NULLabsent Primary F5 NULLabsent F2 F2 75 F3 F3 725 F4 F4 96 When defining a MERGE table with 4 base tables, like: ENGINE=MERGE UNION=(data2003, data2004, data2005, data2006); the SHOW INDEX gives even less cardinalities: KeyName Column Cardinality Primary F1 x Primary F2 xx Primary F3 NULLabsent Primary F4 NULLabsent Primary F5 NULLabsent F2 F2 xx F3 F3 xxx F4 F4 xx Any ideas ? I am using MySQL 5.0.15 NT TIA, Cor
Re: boolean search on phrase*
Hi Brigitte,
I have tried some REGEXP expressions to get right truncated phrases.
Apparently the following query gives me the right truncated result:
SELECT Description FROM products WHERE Description REGEXP 'olive oil';
This query does include products with olive oil and olive oils,
and excludes products with fatty oils ... (excl. olive).
Unfortunately, it is slower than MATCH ... AGAINST ...,
probably because it's not using the fulltext key on the Description field.
Thanks, Cor
- Original Message -
From: Brigitte Silins [EMAIL PROTECTED]
To: C.R.Vegelin [EMAIL PROTECTED]
Sent: Thursday, October 12, 2006 2:58 PM
Subject: Re: boolean search on phrase
Hello:
I may be going out on a limb, as I missed part of the thread;
nevertheless, have you tried regular expressions?
As in REGEXP 'oils?' where the ? matches zero or one of the previous
characters. (or REGEXP 'oils{0,1} )
http://dev.mysql.com/doc/refman/4.1/en/regexp.html
http://dev.mysql.com/doc/refman/5.1/en/regexp.html
Regards,
Brigitte
C.R.Vegelin wrote:
Hi ViSolve,
I have tried various combinations with + and *,
and with single and/or double quotes.
But unfortunately I can't get the proper results.
I get the impression that it's not possible ...
Thanks anyway, Cor
- Original Message - From: Visolve DB Team
[EMAIL PROTECTED]
To: C.R.Vegelin [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 11:05 AM
Subject: Re: boolean search on phrase*
Hi,
Try with + and * fulltext boolean operators.
For instance,
MATCH (Description) AGAINST('+olive oil*' IN BOOLEAN MODE)
Thanks,
ViSolve DB Team.
- Original Message - From: C.R.Vegelin [EMAIL PROTECTED]
To: Visolve DB Team [EMAIL PROTECTED];
mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 4:08 PM
Subject: Re: boolean search on phrase*
Thanks ViSolve,
So far I have tried the next alternatives, not giving me what I need:
a) ... MATCH (Description) AGAINST('olive oil' IN BOOLEAN MODE)
giving only olive oil but not olive oils
b) ... MATCH (Description) AGAINST('olive oil' IN BOOLEAN MODE)
giving olive oil and olive oils BUT also fatty oils ... (excl.
olive)
c) ... MATCH (Description) AGAINST(olive oil IN BOOLEAN MODE)
giving the same results as b)
Any more ideas ?
TIA, Cor
- Original Message - From: Visolve DB Team
To: C.R.Vegelin ; mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 9:54 AM
Subject: Re: boolean search on phrase*
Hi
The Boolen Search will itself satisfy your query. If you enclose the
phrase within double quote (''), then the characters matches only rows
that contain the phrase literally, as it was typed.
Try removing quotes.
Thanks
ViSolve DB Team.
- Original Message - From: C.R.Vegelin
[EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 2:42 PM
Subject: boolean search on phrase*
Hi All,
I want a boolean search on a phrase.
For example on olive oil, but it should return also olive oils etc.
Now I use the following:
SELECT Description FROM products
WHERE MATCH (Description ) AGAINST('olive oil' IN BOOLEAN MODE);
This works fine, but it does NOT return rows with olive oils.
I tried the following:
... MATCH (Description ) AGAINST('olive oil*' IN BOOLEAN MODE);
but that doesn't work.
Any idea will be appreciated.
TIA, Cor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
boolean search on phrase*
Hi All,
I want a boolean search on a phrase.
For example on olive oil, but it should return also olive oils etc.
Now I use the following:
SELECT Description FROM products
WHERE MATCH (Description ) AGAINST('olive oil' IN BOOLEAN MODE);
This works fine, but it does NOT return rows with olive oils.
I tried the following:
... MATCH (Description ) AGAINST('olive oil*' IN BOOLEAN MODE);
but that doesn't work.
Any idea will be appreciated.
TIA, Cor
Re: boolean search on phrase*
Thanks ViSolve,
So far I have tried the next alternatives, not giving me what I need:
a) ... MATCH (Description) AGAINST('olive oil' IN BOOLEAN MODE)
giving only olive oil but not olive oils
b) ... MATCH (Description) AGAINST('olive oil' IN BOOLEAN MODE)
giving olive oil and olive oils BUT also fatty oils ... (excl. olive)
c) ... MATCH (Description) AGAINST(olive oil IN BOOLEAN MODE)
giving the same results as b)
Any more ideas ?
TIA, Cor
- Original Message -
From: Visolve DB Team
To: C.R.Vegelin ; mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 9:54 AM
Subject: Re: boolean search on phrase*
Hi
The Boolen Search will itself satisfy your query. If you enclose the phrase
within double quote (''), then the characters matches only rows that contain
the phrase literally, as it was typed.
Try removing quotes.
Thanks
ViSolve DB Team.
- Original Message -
From: C.R.Vegelin [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 2:42 PM
Subject: boolean search on phrase*
Hi All,
I want a boolean search on a phrase.
For example on olive oil, but it should return also olive oils etc.
Now I use the following:
SELECT Description FROM products
WHERE MATCH (Description ) AGAINST('olive oil' IN BOOLEAN MODE);
This works fine, but it does NOT return rows with olive oils.
I tried the following:
... MATCH (Description ) AGAINST('olive oil*' IN BOOLEAN MODE);
but that doesn't work.
Any idea will be appreciated.
TIA, Cor
Re: boolean search on phrase*
Hi ViSolve,
I have tried various combinations with + and *,
and with single and/or double quotes.
But unfortunately I can't get the proper results.
I get the impression that it's not possible ...
Thanks anyway, Cor
- Original Message -
From: Visolve DB Team [EMAIL PROTECTED]
To: C.R.Vegelin [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 11:05 AM
Subject: Re: boolean search on phrase*
Hi,
Try with + and * fulltext boolean operators.
For instance,
MATCH (Description) AGAINST('+olive oil*' IN BOOLEAN MODE)
Thanks,
ViSolve DB Team.
- Original Message -
From: C.R.Vegelin [EMAIL PROTECTED]
To: Visolve DB Team [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 4:08 PM
Subject: Re: boolean search on phrase*
Thanks ViSolve,
So far I have tried the next alternatives, not giving me what I need:
a) ... MATCH (Description) AGAINST('olive oil' IN BOOLEAN MODE)
giving only olive oil but not olive oils
b) ... MATCH (Description) AGAINST('olive oil' IN BOOLEAN MODE)
giving olive oil and olive oils BUT also fatty oils ... (excl.
olive)
c) ... MATCH (Description) AGAINST(olive oil IN BOOLEAN MODE)
giving the same results as b)
Any more ideas ?
TIA, Cor
- Original Message -
From: Visolve DB Team
To: C.R.Vegelin ; mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 9:54 AM
Subject: Re: boolean search on phrase*
Hi
The Boolen Search will itself satisfy your query. If you enclose the
phrase within double quote (''), then the characters matches only rows
that contain the phrase literally, as it was typed.
Try removing quotes.
Thanks
ViSolve DB Team.
- Original Message -
From: C.R.Vegelin [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, October 12, 2006 2:42 PM
Subject: boolean search on phrase*
Hi All,
I want a boolean search on a phrase.
For example on olive oil, but it should return also olive oils etc.
Now I use the following:
SELECT Description FROM products
WHERE MATCH (Description ) AGAINST('olive oil' IN BOOLEAN MODE);
This works fine, but it does NOT return rows with olive oils.
I tried the following:
... MATCH (Description ) AGAINST('olive oil*' IN BOOLEAN MODE);
but that doesn't work.
Any idea will be appreciated.
TIA, Cor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
list of words in fulltext key index
Hi List, I have a table with a FULLTEXT KEY column, and I would like to get a list of all the FULLTEXT KEY words, eg: acetic acid acidified acrylic ... Any idea how to make such a list ? TIA, Cor
Re: Way too slow Load Data Infile
Hi Mike, Try the following: ALTER TABLE tblname DISABLE KEYS; LOAD DATA INFILE ... ALTER TABLE tblname ENABLE KEYS; hth, Cor - Original Message - From: mos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, July 29, 2006 4:50 AM Subject: Re: Way too slow Load Data Infile I ran a file monitor and it appears MySQL has been updating the table's index for the past several hours as part of the Load Data Infile process. Is there any way to speed this up? I'm using MySIAM tables in v4.1.10. Here are the settings I'm using. Is there anything in there that will speed up the re-indexing? TIA Mike +-+-+ | Variable_name | Value | +-+-+ | back_log| 50 | | basedir | u:\mysql\ | | bdb_cache_size | 8388600 | | bdb_home| | | bdb_log_buffer_size | 0 | | bdb_logdir | | | bdb_max_lock| 1 | | bdb_shared_data | OFF | | bdb_tmpdir | | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 33554432 | | character_set_client| latin1 | | character_set_connection| latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server| latin1 | | character_set_system| utf8 | | character_sets_dir | u:\mysql\share\charsets/ | | collation_connection| latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server| latin1_swedish_ci | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | u:\mysql_data\ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | OFF | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | expire_logs_days| 0 | | flush | OFF | | flush_time | 1800 | | ft_boolean_syntax | + -()~*:| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit| 20 | | ft_stopword_file| (built-in) | | group_concat_max_len| 1024 | | have_archive| NO | | have_bdb| DISABLED | | have_compress | YES | | have_crypt | NO | | have_csv| NO | | have_example_engine | NO | | have_geometry | YES | | have_innodb | DISABLED | | have_isam | NO | | have_ndbcluster | NO | | have_openssl| NO | | have_query_cache| YES | | have_raid | NO | | have_rtree_keys | YES | | have_symlink| YES | | init_connect| | | init_file | | | init_slave | | | innodb_additional_mem_pool_size | 1048576 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 8388608 | | innodb_data_file_path | | | innodb_data_home_dir| | | innodb_fast_shutdown| ON | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8 | | interactive_timeout | 28800 | | join_buffer_size| 33550336 | | key_buffer_size | 67108864 | | key_cache_age_threshold | 300 | | key_cache_block_size| 1024 | | key_cache_division_limit| 100 | | language| u:\mysql\share\english\ | | large_files_support | ON | | license | GPL | | local_infile
is INNER join so much slower than LEFT join ?
Hi List, I have the 2 MyISAM tables using mySQL version 5.0.15-NT: Table countries: `ID` smallint unsigned NOT NULL default '0', `Code` char(2) default NULL, `Name` char(30) default NULL, ... PRIMARY KEY (`ID`) Table data `Country1` smallint unsigned NOT NULL default '0', `Country2` smallint unsigned NOT NULL default '0', ... KEY `Country1` (`Country1`), KEY `Country2` (`Country2`) When I run then next query with LEFT join is takes approx 1 minute. UPDATE data AS db LEFT JOIN countries AS c1 ON db.Country1=c1.ID LEFT JOIN countries AS c2 ON db.Country2=c2.ID SET db.Expr = ...; But when I run it with INNER join is takes more than 2 hours !!! In both cases the query applies to 9.571.220 rows matched with 0 changed. Any idea why INNER join is so much slower ? TIA, Cor
Re: MS Access gives error no. -7776.
Hi CPK, I suppose you've looked at the manual in: http://dev.mysql.com/doc/refman/5.0/en/myodbc-examples.html It you still have problems, try just the linked table, without forms. If you can view the linked MySQL tables in MS Access, then the problem has to do with the forms, and not with the connection. HTH, Cor - Original Message - From: C K [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, July 02, 2006 2:29 PM Subject: MS Access gives error no. -7776. Dear Friends, I have connected MS Access to MySQL 5.0.17 with MyODBC 3.51.12 with MS Jet SP 8. It is giving error -7776 (There is no message for this error) while jumping from a form to a subform having two different tables for these tow forms as recordsource. Can any one please help me. It's urgent. Thanks in advance CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: number of rows in EXPLAIN for unpacked vs packed tables
Hi Dan, The contents of both tables is identically. I checked both tables with SHOW INDEX FROM ... and the cardinalities are exactly the same. Nevertheless, thanks for mentioning that those numbers are estimates. Regards, Cor - Original Message - From: Dan Buettner [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, June 30, 2006 4:36 PM Subject: Re: number of rows in EXPLAIN for unpacked vs packed tables Cor - Those numbers are an estimate, not a hard number, of how many rows MySQL thinks it will have to exmaine to find what you're looking for. They could be different because your compressed table is brand-new and all the indexes are clean and up to date, whereas your original table has possibly been inserted, updated, and deleted from, causing the key information to be less accurate. You could try running a CHECK TABLE tablename EXTENDED on the old one, which should update all the key info, and then checking your EXPLAIN results again. Be aware it could take a while for a large table and will lock the table for the duration. If your indexes are different on the compressed table that would make a difference too, as MySQL might well be basing its estimate off a different index. Dan On 6/30/06, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi All, I compressed a MyISAM table successfully with MYISAMPACK, followed by MYISAMCHK. Both tables (MyISAM + Compressed ) have exactly the same number of rows with Count(*). But when I give a SELECT query with EXPLAIN on both tables, I get different number of rows. For example: EXPLAIN SELECT ... FROM data ... shows 1983 rows, but the same EXPLAIN SELECT ... FROM pack ... shows 2064 rows. Any idea why ? Regards, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
number of rows in EXPLAIN for unpacked vs packed tables
Hi All, I compressed a MyISAM table successfully with MYISAMPACK, followed by MYISAMCHK. Both tables (MyISAM + Compressed ) have exactly the same number of rows with Count(*). But when I give a SELECT query with EXPLAIN on both tables, I get different number of rows. For example: EXPLAIN SELECT ... FROM data ... shows 1983 rows, but the same EXPLAIN SELECT ... FROM pack ... shows 2064 rows. Any idea why ? Regards, Cor
Re: Math problem
Hi Karl, Your question: can I add a $ when you select a view. I suggest to include $ sign in the field alias, like: Select title_id, ytd_sales * price AS `Turnover $` From titles; HTH, Cor - Original Message - From: Karl Larsen [EMAIL PROTECTED] To: Chris W [EMAIL PROTECTED] Cc: MYSQL General List mysql@lists.mysql.com Sent: Thursday, June 22, 2006 10:04 PM Subject: Re: Math problem Chris W wrote: Karl Larsen wrote: I'm trying to multiply numbers one of which is money. The money looks like this: SELECT price FROM titles; | price | ++ | $20.00 | | $19.99 | | $7.99 | | $19.99 | | $11.95 | | $19.99 | | $14.99 | | $11.95 | | $22.95 | | $2.99 | | $10.95 | | $7.00 | | $2.99 | | $20.95 | | NULL | | $19.99 | | $21.59 | | NULL | ++ 18 rows in set (0.01 sec) When I use SELECT title_id, ytd_sales * price From titles; I get: | title_id | ytd_sales | price * ytd_sales | +--+---+---+ | PC | 4095 | 0 | | BU1032 | 4095 | 0 | | PS | 3336 | 0 | | PS | 4072 | 0 | | BU | 3876 | 0 | | MC | 2032 | 0 | | TC | 4095 | 0 | | TC4203 | 15096 | 0 | | PC1035 | 8780 | 0 | | BU2075 | 18722 | 0 | | PS2091 | 2045 | 0 | | PS2106 | 111 | 0 | | MC3021 | 22246 | 0 | | TC3218 | 375 | 0 | | MC3026 | NULL | NULL | | BU7832 | 4095 | 0 | | PS1372 | 375 | 0 | | PC | NULL | NULL | +--+---+---+ 18 rows in set (0.04 sec) It appears that mysys 4.1 does not know how to multiply a dollar amount to another number. Has anyone else seen this problem? What does a show create table give for the price column? I bet it is varchar. The only way to make it work then would be to trim off the dollar sign and cast it to a float or double. It's a char(20) and NULL in the table titles. I removed the $ and reloaded and it now works properly. I suspect an ealier version of mysql had some way to do this. I'm learning that you store a simple number. But you can add a $ when you select a view. Karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: About the leftmost index prefixes using nounique index
Hi Gabriel, Can you tell the benefits of a composite index, compared to 4 individual indices in this case ? Suppose I need to select on the fields b, c or d. Then I also need also indices on fields b, c and d. Together with the composite index on (a,b,c,d), there is a lot of redundancy in the indices. Regards, Cor - Original Message - From: Gabriel PREDA [EMAIL PROTECTED] To: Takanobu Kawabe [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, June 21, 2006 8:47 AM Subject: Re: About the leftmost index prefixes using nounique index Basically it says that if you have an index let's say INDEX_1 on columns: INDEX_1 : a, b, c, d MySQL will act as if you had setup indexes on: INDEX_1_1 : a, b, c INDEX_1_2 : a, b INDEX_1_1 : a A query like: SELECT a FROM table_name WHERE a 9; - will use the index SELECT a, b, c FROM table_name WHERE d 9; - will use the index Hope this clears up things ! -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index on MERGE table
Hi Eugene, I suppose you have read: http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html Especially the paragraph starting with: The order of indexes in the MERGE table and its underlying tables should be the same. HTH, Cor - Original Message - From: Eugene Kosov [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, June 21, 2006 2:58 PM Subject: Index on MERGE table Hi everyone! I have a bunch of MyISAM tables and one MERGE table. All have same structure. It seems to me indecies on MERGE table aren't fine. When I fetch rows by indexed field I get empty result set. Index size showed by 'SHOW TABLE STATUS' is 0. SHOW INDEXES also shows somthing strange. Index cardinality is 81 while table contains over than 12 millions of different values. I've tried recreating indecies but this didn't help. I couldn't find anything relevant in documentation. How should I create index on MERGE table? Thanks in advance! -- Regards, Eugene Kosov. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to convert strings to 'proper case' ?
Thanks Rhino, Johan, Melvin, In my application I don't have stuff like A.b. Mcdonald etc. But descriptions like SUGARS AND SUGAR CONFECTIONERY. This data is loaded as uppercase from a CSV textfile into a MySQL table. Currently I use the MS Access function StrConv(Description,3) to set propercase, giving: Sugars And Sugar Confectionery, followed by a script for stopwords like: SET @from := ' And ', @to := ' and '; UPDATE products SET Description = REPLACE(Description, @from, @to); to get Sugars and Sugar Confectionery. The reason for my question was to avoid using MS Access. I will certainly take a closer look at your suggestions ! Regards, Cor - Original Message - From: Rhino [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, May 09, 2006 2:42 PM Subject: Re: How to convert strings to 'proper case' ? I'd be surprised if things actually turned out to be quite as simple as you describe. For example, let's say that your column actually contained book titles or names of people. Would you really want to see any of the following in your column: - A Diplomatic History Of The Un? (more likely: A Diplomatic History of the UN) - A.b. Mcdonald? (more likely: A. B. McDonald) - The Life And Times Of King George Iii? (more likely: The Life and Times of King George III) In any case, I don't think a simple SQL UPDATE will do what you want to do, at least not very easily. You'll almost certainly want some real programming statements to do the string manipulation that you need. You haven't said whether your data is already in tables or whether you are planning to load the data into new tables. You also haven't said whether the data is in all upper case, all lower case or in some form of mixed case. If the data is not already in tables, I'd be inclined to change the case of the data with a scripting language that was appropriate for your operating system and then load the corrected data into the tables. For instance, on Linux, I might write a bash shell script to reformat the data, which is presumably sitting in a flat file somewhere, then load the reformatted data into the tables. This gives you the option of choosing from several different scripting languages, some of which you may already know fluently. That could save you a lot of time. If the data is already in tables, you could unload it to a flat file, fix it with a shell script, and then reload it to the database. Or, you could write a User Defined Function (UDF) or Stored Procedure (SP) in order to update the existing values. Then you could call the procedure or function to do the necessary work at any time you found data with the wrong case. If you write an SP, you could pass the table name and column name to the procedure. Then, the procedure could do a loop that operated on every value in that column of the table. For each row, it could read the existing value, create a revised value using string manipulation techniques, then update the current value with the revised value. Creating the revised value would likely be the only tricky part and even that might not be very hard if it really were only necessary to convert the first letter of each word to a capital. If the code actually had to handle more complex cases like the ones I put at the beginning of this note, the code would be more complicated; it might even be impossible if the language you were using for the SP or UDF didn't have many string manipulation techniques. In that case, you might need to choose a different language or you could go back to unloading the data from the database, manipulating it outside MySQL, and then reloading it. That's all I'm going to say for the moment but if you decide to try a UDF or SP and can state what programming languages you are willing to use for the code - and whether the conversion is really as simple as capitalizing just the first letters of the words - I might be able to give you more specific suggestions. Unfortunately, I don't have a current version of MySQL and can't really install one so I can only talk hypothetically, based on UDFs and SPs that I've written in DB2 and on what I've seen in the MySQL manuals. I can't actually write you a simple UDF or SP for MySQL that would do at least the basic parts of the conversion you want. Maybe someone else on this mailing list has an example that you could have which is actually known to work in MySQL. Otherwise, you might only have hypothetical guidelines and manual articles to guide you as you try to write your UDF or SP. That can be time-consuming if you've never done any coding like that before. But it could be fun too if you are in the right frame of mind! -- Rhino - Original Message - From: C.R.Vegelin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 09, 2006 1:41 AM Subject: How to convert strings to 'proper case' ? Hi List, I want to convert strings to proper
How to convert strings to 'proper case' ?
Hi List, I want to convert strings to proper-case, where only the 1st char of each word is uppercase, such as: This Is An Example. Any idea how to do this with MySQL 5.0.15 ? Thanks, Cor
Re: Help on VB and ODBC
Hi Manuel, I had a similar problem a few months ago and I found 'somewhere' in the documentation a post by Matthew van Os on May 27 2005, saying: The CursorLocation should be of the type adUseClient instead of adUseServer. The adUseServer returns a -1 as recordcount. The adUseClient returns the actual recordcount. Please also take a look at: http://dev.mysql.com/doc/refman/5.1/en/programs-known-to-work-with-myodbc.html HTH, Cor - Original Message - From: Manuel Betanzos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, May 04, 2006 4:01 PM Subject: Help on VB and ODBC Hi everybody, Please I need help on this. I am beginning a new application. I am doing it with VB 6.0 - MySQL 5.0 - MyODBC 3.51 on Windows Is there any parameter i need to establish in order to be able to work with RecordSets in the Client side , actually i can't because if i do, when i change any value i receive the following message: Error '-2147217900 (80040e14) en tiempo de ejecucion: [MySQL][ODBC 3.51 Driver][mysqld-5.0.15]You have an error in your SQL syntax; check the manual that corresponds to your MySQL Server version for the right syntax to use near'. ´catpartes´ SET ´condicion´ =2 WHERE ´CveCia´ =1 and ´Almacen´ ='01' and ´NoParte´ at line 1 If i change the CursorLocation to 3 - adUseClient i don't receive the above message and the information is updated correctly . But with the CursorLocation 2 - adUseServer the DataList, DataCombo and DataGrid dont work. Thanks in advance. - Do You Yahoo!? La mejor conexión a Internet y 2GB extra a tu correo por $100 al mes. http://net.yahoo.com.mx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Top N selections + rest row
Hi All, Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. Regards, Cor
Re: Top N selections + rest row
Thanks Martijn, Barry, I was wondering whether it could be done in a single query. I want users to decide how many countries they want, and show world sales on top of report followed by the N countries. This to enable relative country shares, both for reporting and graphs. For example, Top-10 countries + Rest in a pie graph. So I need one additional row in the Top-N query. Regards, Cor - Original Message - From: Martijn Tonies [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 25, 2006 11:06 AM Subject: Re: Top N selections + rest row Hi, Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. What about just removing the LIMIT clause? Or, alternatively, do a skip of the first 25 rows? (check the docs for that) Do you want to get this in a single result, or additional result? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Top N selections + rest row
Thanks Shawn, According: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html: When you use ROLLUP, you cannot also use an ORDER BY clause to sort the results. ... Regards, Cor - Original Message - From: Shawn Green [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED]; Martijn Tonies [EMAIL PROTECTED]; mysql@lists.mysql.com; [EMAIL PROTECTED] Sent: Tuesday, April 25, 2006 1:18 PM Subject: Re: Top N selections + rest row --- C.R.Vegelin [EMAIL PROTECTED] wrote: Thanks Martijn, Barry, I was wondering whether it could be done in a single query. I want users to decide how many countries they want, and show world sales on top of report followed by the N countries. This to enable relative country shares, both for reporting and graphs. For example, Top-10 countries + Rest in a pie graph. So I need one additional row in the Top-N query. Regards, Cor - Original Message - From: Martijn Tonies [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 25, 2006 11:06 AM Subject: Re: Top N selections + rest row Hi, Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. What about just removing the LIMIT clause? Or, alternatively, do a skip of the first 25 rows? (check the docs for that) Do you want to get this in a single result, or additional result? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com Have you considered using the WITH ROLLUP modifier? Select Country , Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country WITH ROLLUP Order By Sales DESC LIMIT 25; http://dev.mysql.com/doc/refman/5.0/en/select.html Shawn Green __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Top N selections + rest row
Hi Joerg, All, I would like to have something like: Country Type20042005 -- GermanySales13357 19843 Belgium Sales12224 16767 France Sales15443 16602 Un. States Sales11995 14332 Japan Sales14234 13364 Rest Sales17663 12563 -- if a user requires a Top-5 selection for 2005, and where Totals of both 2004 and 2005 are 100%. Regards, Cor - Original Message - From: Joerg Bruehe [EMAIL PROTECTED] To: Shawn Green [EMAIL PROTECTED]; C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, April 25, 2006 2:17 PM Subject: Re: Top N selections + rest row Hi Shawn, Cor, all! Shawn Green wrote: --- C.R.Vegelin [EMAIL PROTECTED] wrote: Thanks Martijn, Barry, I was wondering whether it could be done in a single query. I want users to decide how many countries they want, and show world sales on top of report followed by the N countries. This to enable relative country shares, both for reporting and graphs. For example, Top-10 countries + Rest in a pie graph. So I need one additional row in the Top-N query. Regards, Cor Have you considered using the WITH ROLLUP modifier? Select Country , Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country WITH ROLLUP Order By Sales DESC LIMIT 25; http://dev.mysql.com/doc/refman/5.0/en/select.html I checked it here: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html It seems that WITH ROLLUP is not adequate for Cor's needs, see this quote: | LIMIT can be used to restrict the number of rows returned to the | client. LIMIT is applied after ROLLUP, so the limit applies against | the extra rows added by ROLLUP. Cor, what about a UNION? Untested: ( Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25 ) UNION ( SELECT World, Sum(Sales) AS Sales From myTable Where Year=2005 ) ; Note the extra parentheses, according to the manual they are needed to ensure that the limit is applied to the first select only. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 VoIP: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to quit a (CLI) script ?
Hi everyone, I use many scripts that are started from the CLI (MySQL 5.0.15). And if errors occur, I want to quit a script. I looked at: http://dev.mysql.com/doc/refman/5.0/en/show-errors.html and found Show Count(*) Errors; and for Select @@error_count; But how to quit a CLI script, if @@error_count 0 ? Any ideas are most welcome. TIA, Cor
Re: how to suppress NULL values in SELECT ... INTO OUTFILE ?
Thanks Jorrit, Yes, it is a combi of Paul's suggestion to use IFNULL() with the ENCLOSED BY '' option. So when using: Select IFNULL(Jan,''), IFNULL(Feb,''), ... Into Outfile ... Fields Terminated By ';' Enclosed By '' Escaped By '' Lines Terminated By '\r\n' ... I do get results like 1;2;;4;;2;9 without NULLs or quotes. Regards, Cor - Original Message - From: Jorrit Kronjee [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, April 02, 2006 11:33 AM Subject: Re: how to suppress NULL values in SELECT ... INTO OUTFILE ? Cor, You can set the enclosing character with the ENCLOSED BY parameter. So something like ENCLOSED BY '' will remove those quotes. - Jorrit C.R.Vegelin wrote: Thanks Paul, Yes, I've tried IFNULL() to map NULL values to empty strings. But then I get 1;2;;4;;2;9 in stead of 1;2;;4;;2;9 So building CSV files with 1;2;;4;;2;9 output is not possible ? Maybe an idea to extend the FIELDS options to enable this ... Regards, Cor - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Saturday, April 01, 2006 6:11 PM Subject: Re: how to suppress NULL values in SELECT ... INTO OUTFILE ? At 13:29 +0100 4/1/06, C.R.Vegelin wrote: Hi everyone, I am struggling to make a CSV file, with rows like: 1;2;;4;;2;9 where NULL values are suppressed in the CSV file. I tried the following alternatives: a) Select ... Into Outfile ... Fields Terminated By ';' Escaped By '' Lines Terminated By '\r\n' ... but this generates output like: 1;2;NULL;4;NULL;2;9 b) Select ... Into Outfile ... Fields Terminated By ';' Lines Terminated By '\r\n' ... but this generates output like: 1;2;\N;4;\N;2;9 Any idea how to get CSV rows like: 1;2;;4;;2;9 ? Thanks for your time and effort. Regards, Cor You could use IFNULL() to map NULL values to the empty string: mysql set @x = null, @y = 1; Query OK, 0 rows affected (0.00 sec) mysql select ifnull(@x,''), ifnull(@y,''); +---+---+ | ifnull(@x,'') | ifnull(@y,'') | +---+---+ | | 1 | +---+---+ 1 row in set (0.00 sec) You'll need to apply this to each column that might contain NULL values. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to suppress NULL values in SELECT ... INTO OUTFILE ?
Hi everyone, I am struggling to make a CSV file, with rows like: 1;2;;4;;2;9 where NULL values are suppressed in the CSV file. I tried the following alternatives: a) Select ... Into Outfile ... Fields Terminated By ';' Escaped By '' Lines Terminated By '\r\n' ... but this generates output like: 1;2;NULL;4;NULL;2;9 b) Select ... Into Outfile ... Fields Terminated By ';' Lines Terminated By '\r\n' ... but this generates output like: 1;2;\N;4;\N;2;9 Any idea how to get CSV rows like: 1;2;;4;;2;9 ? Thanks for your time and effort. Regards, Cor
Re: how to suppress NULL values in SELECT ... INTO OUTFILE ?
Thanks Paul, Yes, I've tried IFNULL() to map NULL values to empty strings. But then I get 1;2;;4;;2;9 in stead of 1;2;;4;;2;9 So building CSV files with 1;2;;4;;2;9 output is not possible ? Maybe an idea to extend the FIELDS options to enable this ... Regards, Cor - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Saturday, April 01, 2006 6:11 PM Subject: Re: how to suppress NULL values in SELECT ... INTO OUTFILE ? At 13:29 +0100 4/1/06, C.R.Vegelin wrote: Hi everyone, I am struggling to make a CSV file, with rows like: 1;2;;4;;2;9 where NULL values are suppressed in the CSV file. I tried the following alternatives: a) Select ... Into Outfile ... Fields Terminated By ';' Escaped By '' Lines Terminated By '\r\n' ... but this generates output like: 1;2;NULL;4;NULL;2;9 b) Select ... Into Outfile ... Fields Terminated By ';' Lines Terminated By '\r\n' ... but this generates output like: 1;2;\N;4;\N;2;9 Any idea how to get CSV rows like: 1;2;;4;;2;9 ? Thanks for your time and effort. Regards, Cor You could use IFNULL() to map NULL values to the empty string: mysql set @x = null, @y = 1; Query OK, 0 rows affected (0.00 sec) mysql select ifnull(@x,''), ifnull(@y,''); +---+---+ | ifnull(@x,'') | ifnull(@y,'') | +---+---+ | | 1 | +---+---+ 1 row in set (0.00 sec) You'll need to apply this to each column that might contain NULL values. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA giving BIG mysql-bin files ...
Hi List, I would appreciate your help on the following. When using LOAD DATA INFILE 'inputfile.txt' into a MyISAM table, it creates mysql-bin.nn files under my database directory with the size of 'inputfile.txt' (about 200 MB). Since I have to load 12 inputfiles, I get about 2.5 GB of mysql-bin files. Question: is this normal ? If not, how can I avoid these mysql-bin files ? When using MySQL Administrator to look at a mysql-bin file, it shows only: 060330 8:29:00 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.15-nt-log' socket: '' port: 3306 Official MySQL binary I use the following script: DELETE FROM myTable; LOAD DATA INFILE 'infile.txt' INTO TABLE myTable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (..., ..., ...) SET ...; The LOAD DATA ... gives: warnings = 0 MySQL version is: MySQL 5.0.15-nt-log on a Windows XP machine. TIA and Regards, Cor
Re: LOAD DATA giving BIG mysql-bin files ...
Thanks Adrian, Dilipkumar, Dhandapani, I changed my.ini file, restarted the server and now it's okay. Regards, Cor - Original Message - From: Adrian Bruce [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, March 30, 2006 9:48 AM Subject: Re: LOAD DATA giving BIG mysql-bin files ... I think this is normal as the binary log will contain a record of all changes made to the data, therefore if you are loading large files regularly- the bin logs will be quite large. If you do not want the binary logging, edit the my.cnf file, comment out the line log-bin (#log-bin) and restart the server. Ade C.R.Vegelin wrote: Hi List, I would appreciate your help on the following. When using LOAD DATA INFILE 'inputfile.txt' into a MyISAM table, it creates mysql-bin.nn files under my database directory with the size of 'inputfile.txt' (about 200 MB). Since I have to load 12 inputfiles, I get about 2.5 GB of mysql-bin files. Question: is this normal ? If not, how can I avoid these mysql-bin files ? When using MySQL Administrator to look at a mysql-bin file, it shows only: 060330 8:29:00 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.15-nt-log' socket: '' port: 3306 Official MySQL binary I use the following script: DELETE FROM myTable; LOAD DATA INFILE 'infile.txt' INTO TABLE myTable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (..., ..., ...) SET ...; The LOAD DATA ... gives: warnings = 0 MySQL version is: MySQL 5.0.15-nt-log on a Windows XP machine. TIA and Regards, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'Into outfile' doesn't include the column names. How can it be done?
Hi Ariel, Maybe this example helps you to create CSV output from MySQL. The first SELECT generates the headerline; the second the data. ( SELECT 'FieldA','FieldB','FieldC', ... ) UNION ( SELECT `FieldA`, `FieldB`, `FieldC`, ... INTO OUTFILE 'D:/MySQL Datafiles/myFile.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM ... ... GROUP BY `FieldA`, `FieldB`, `FieldC`, ... ); Don't forget the braces ( and ). HTH, Cor - Original Message - From: Ariel Sánchez Mora [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, March 06, 2006 8:12 PM Subject: 'Into outfile' doesn't include the column names. How can it be done? When using select into outfile, I can only get the table data, but I can't find how to include the column names. I haven't been able to include the column names into the actual return of the query, and they don't get stored in the .csv Anyone know how to include the column names, table name, other info, as an actual answer from MySQL? I realize that when I use the command line I can see the column names, but this is not the case when using, for example, Delphi, and my real problem, when the outfile is made. I use SQLyog for exporting to csv and they do include the columns, my bet is its some SQL option. The manual doesn't specify, at least in the select syntax part. Ariel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE from monthly to yearly rows
Thanks Peter, I didn't know that MySQL has no UPDATE ... SELECT command. I followed your advice and made the following query UPDATE Data AS db INNER JOIN (SELECT myKey,Year, SUM(IF(Month= 1,Cell,Null)) AS `Jan`, ... SUM(IF(Month=12,Cell,Null)) AS `Dec` FROM Updates GROUP BY myKey, Year) AS sq ON (db.myKey=sq.myKey AND db.Year=sq.Year) SET db.Jan = sq.Jan, ..., db.Dec = sq.Dec; This works fine and fast. Thanks again ! Regards, Cor - Original Message - From: Peter Brawley To: C.R.Vegelin Cc: mysql@lists.mysql.com Sent: Tuesday, February 28, 2006 8:40 PM Subject: Re: UPDATE from monthly to yearly rows Cor, I need to put all available monthly Values from Updates to 1 Data record where MyKey and Year are equal. IOW you want to save the results of the business end of a crosstab (pivot table) query. The crosstab analysis will require a full query. MySQL has an INSERT ... SELECT command, but no UPDATE ... SELECT command, so this will be a two-step. If I understand your description correctly, you want to aggregate by month and report by mykey and year, so your crosstab would look something like this (not tested)... CREATE TEMPORARY TABLE crosstab SELECT d.myKey, d.year, SUM(IF(u.month=1 ,u.value,0)) AS jan, SUM(IF(u.month=2 ,u.value,0)) AS feb, ... etc ... SUM(IF(u.month=12,u.value,0)) AS dec) FROM data AS d INNER JOIN updates AS u USING (myKey) GROUP BY mykey,year; aggregating updates to one row per mykey per year. Then update the data table with something like ... UPDATE Data AS d INNER JOIN crosstab AS c ON d.myKey = c.myKey AND d.year = c.year SET d.Jan = c.jan ... etc ... PB - C.R.Vegelin wrote: Hi List, Please help me with the following problem in MySQL 5.0.15. I have 2 MyISAM tables like: - table Updates with fields myKey, Year, Month, Value where Month has the values 1 .. 12 - table Data with fields myKey, Year, Jan, Feb, ... Dec I need to put all available monthly Values from Updates to 1 Data record where MyKey and Year are equal. I tried the following query: UPDATE Data AS db INNER JOIN Updates AS U ON db.myKey = U.myKey SET db.Jan = IF(U.Month = 1, U.Value, db.Jan), db.Feb = IF(U.Month = 2, U.Value, db.Feb), ... db.Dec = IF(U.Month=12, U.Value,db.Dec); But this query takes only the first available Month in Updates, and ignores the other months per myKey / Year combination. I would appreciate your help. TIA, Cor No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
UPDATE from monthly to yearly rows
Hi List, Please help me with the following problem in MySQL 5.0.15. I have 2 MyISAM tables like: - table Updates with fields myKey, Year, Month, Value where Month has the values 1 .. 12 - table Data with fields myKey, Year, Jan, Feb, ... Dec I need to put all available monthly Values from Updates to 1 Data record where MyKey and Year are equal. I tried the following query: UPDATE Data AS db INNER JOIN Updates AS U ON db.myKey = U.myKey SET db.Jan = IF(U.Month = 1, U.Value, db.Jan), db.Feb = IF(U.Month = 2, U.Value, db.Feb), ... db.Dec = IF(U.Month=12, U.Value,db.Dec); But this query takes only the first available Month in Updates, and ignores the other months per myKey / Year combination. I would appreciate your help. TIA, Cor
Type and Size of JOIN fields
Hello All, More than once I read on this list that problems may occur, because of unequal types and/or sizes of join fields. Suppose a Countries table with primary key ID SmallInt(5), and a Accounts table with CountryID SmallInt(4). Does this have any negative affect, eg. on performance ? By the way, I am using MyISAM tables for MySQL 5.0. Cor
Re: Most efficient way to design this table
Hi Grant, I suggest to change both key fields to Integers. Numeric keys take less space and are faster. Your Product_Feature table then may have: - product_id INT unsigned - feature INT unsigned having a Primary Key of 8 bytes i.s.o. 141 bytes. Thus a smaller index, less disk reads and more speed. HTH, Cor Vegelin - Original Message - From: Grant Giddens [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 17, 2006 2:28 AM Subject: Most efficient way to design this table Hi, I currently have a table with 2 columns, product_id and feature. Each product_id can have multiple features. My two columns are: product_id is type char(13) feature is type varchar(128) In order to make sure I don't have the same feature listed twice for a particular product, I have set the PRIMARY key on product_id and feature. I have lots of products and lots of features. Is design bloating my available key_buffer memory with too much data? Is there a better way to index this data? Thanks, Grant - Yahoo! Photos Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT uses index but is still slow
Hi James, I have found similar - slowdown - effects for queries. However, it is not always clear what causes the lack of speed. For example, I have a table with more than 9 million rows, including a non-unique indexed item myKey (tinyint). The query Select myKey, count(*) from myTable Group By myKey; takes with the CLI about 25 seconds, BUT the second time it takes only 0.01 second ! I think that the 1st query run includes loading indices into memory. I suggest to test your query twice from the CLI. HTH, Cor Vegelin - Original Message - From: James Harvard [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 04, 2006 8:28 PM Subject: SELECT DISTINCT uses index but is still slow I have a query that takes a long time even though it is only examining the index file. Is this normal? I would have thought that MySQL would be much quicker to get the unique values from an index. select distinct date_id from data_table; # returns 89 rows in 23 seconds - simple index on date_id with 2 other indices - just under 40,000,000 rows in table - index file is 730 MB EXPLAIN SELECT gives the following: type = index key = date_id rows = 39726908 extra = using index FWIW the result is identical with 'select date_id from data_table group by date_id;'. (Using version 4.1.15 on Windows, and I can't see anything relevant in the change notes for 4.1.16.) Finally, here's a CREATE TABLE: CREATE TABLE data_table ( is_import tinyint(1) NOT NULL DEFAULT 0, comcode_id mediumint(8) UNSIGNED NOT NULL DEFAULT 0, date_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, country_id smallint(5) UNSIGNED NOT NULL DEFAULT 0, value bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_2 bigint(20) UNSIGNED DEFAULT NULL, c_value bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_2 bigint(20) UNSIGNED DEFAULT NULL, port_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, KEY date_id (date_id), KEY country_id (country_id), KEY comcode_id (comcode_id,date_id) ) ENGINE=MyISAM ROW_FORMAT=FIXED; TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reporting tools for summary data
Hi James, I saw your email about are primary keys always essential ?. And that your app is essentially creating summary reports from large amounts of summary data. May I ask what reporting tool you use for summary data ? TIA, Cor Vegelin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to avoid sorting sorted tables ?
Thanks James, I looked at http://dev.mysql.com/doc/refman/5.0/en/tables-table.html for the table properties in the Information_Schema.Tables. I tried: SELECT table_name, update_time FROM Information_Schema.Tables WHERE table_name LIKE 'section05'; ++-+ | table_name | update_time | ++-+ | section05 | 2005-12-19 12:13:42 | ++-+ However, Update_Time applies also to changes without affecting the physical table order. All the other ..._Time table properties don't specify the last sort_time. I am afraid I have to simulate an IsSorted property myself. Regards, Cor Vegelin - Original Message - From: James Harvard [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, December 19, 2005 10:48 AM Subject: Re: How to avoid sorting sorted tables ? I suppose you could parse out and compare the update_time value from SHOW TABLE STATUS http://dev.mysql.com/doc/refman/4.1/en/show-table-status.html (or maybe there's a simpler way with MySQL 5's new schema database http://dev.mysql.com/doc/refman/5.0/en/information-schema.html ) and compare it with the last time the table was ordered. HTH, James Harvard At 9:29 am + 19/12/05, C.R.Vegelin wrote: Now I use: ALTER TABLE t ORDER BY a, b, c, d, e, f; This works fine, but takes about 13 minutes for 6 million rows. Without inserts, deletes or other sorts, this PK order remains intact. Suppose a table is sorted on PK and I give the above ORDER BY, I would like to have something like Table is already sorted. Similar to OPTIMIZE TABLE t and ANALYZE TABLE t. Does anyone know how to simulate this ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is there any BEEP command ?
Thanks Harrison !!! SELECT char(7); works excellent to simulate a BEEP with MySQL version 5.0.15 under Windows XP Pro. It is a simple but effective feature for running scripts. Regards, Cor - Original Message - From: Harrison Fisk [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, December 16, 2005 4:54 PM Subject: Re: is there any BEEP command ? Hi, On Dec 16, 2005, at 9:23 AM, C.R.Vegelin wrote: I use various script files with queries to update a MySQL base periodically. These scripts are run from the mysql line with the \. command. I would like to have a BEEP command at the end of the script to get a signal that processing the script is finished. Looked in the manual, but couldn't find it. Does such a command exist ? My version is 5.0.15 under Windows XP Pro. Regards, Cor While there is no beep command, you can possibly get it to beep by selecting the character code for a terminal beep (this is what the mysql client uses internally) Try out: SELECT char(7); And see if it makes a beep for you. It should if the mysql client can make the beep on a syntax error. Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/ packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: from MySQL to MS Excel ...
Thanks JR, Shawn, Scott, ... for your replies. I choose to make use of the SELECT ... INTO OUTFILE. This works fine, but I also want a header-line in the CSV file. So I made the following statement: SELECT `ID`, `Code`, `Name` UNION SELECT `ID`, `Code`, `Name` INTO OUTFILE 'D:/MySQL Datafiles/Units.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM Units ORDER BY `ID`; But this makes the header-line a trailer-line in the csv file, like this: 11,kg,KiloGrams 12,g,Grams 13,Ton,Tonne ... ID,Code,Name Any idea how to make a sorted csv file with a real header-line ? TIA, Cor - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Saturday, December 10, 2005 5:17 PM Subject: RE: from MySQL to MS Excel ... There is at least one other option that JR didn't mention... at least some versions of Excel have the menu option Data-Get External Data which allows you to link through ODBC to run queries directly from within Excel. I have barely used it and I have never tried it with MySQL so I can't really explain how to use it or what it's limitations will be but I know that it works through at least two other ODBC drivers. Shawn Green Database Administrator Unimin Corporation - Spruce Pine J.R. Bullington [EMAIL PROTECTED] wrote on 12/10/2005 10:33:18 AM: Here's the skinny -- YES and NO. NO in that it won't export directly, YES in that you have to do a little leg work in order for it to be done. You have 3 options -- ODBC, Code and CSV. ODBC -- Excel has the ability to use ODBC connections to the MySQL database. Run your MySQL query with the HTML flags turned on and then export to a file so that Excel can read it. (Thanks to SGreen for this info from an earlier post). CODE -- If you code it in ASP or PHP, you can get your code to push directly into Excel with field headers and data, and have formatting options because Excel can interpret HTML code. CSV -- Do your MySQL query from the CLI and then use MySQL to export your results to a CSV file. Then open the CSV file in Excel (using the Excel File Open). See ODBC connection above for another option using the HTML flag. HTH, J.R. -Original Message- From: C.R.Vegelin [mailto:[EMAIL PROTECTED] Sent: Saturday, December 10, 2005 9:35 AM To: mysql@lists.mysql.com Subject: from MySQL to MS Excel ... Hi Friends, I am looking for an easy and seamless way to export MySQL query output to MS Excel. At this moment I am using MS Access 2003 as front-end for a MySQL database. With MS Access I can easily send the output of queries on my database to MS Excel. All I need to do is select Tools Office Links Analyze it with Microsoft Office Excel. That's all. This applies to all kinds of MySQL queries, including WITH ROLLUP options. In the manual I found: http://dev.mysql.com/doc/refman/5.0/en/doctoexcel.html But this is too much trouble, and does not allow full functionality of MySQL queries. Question: is it possible to create MS Excel files directly from MySQL ? TIA, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
defined with default NULL, but missing ...
Hi everybody, I defined a field Date_End with default NULL, but am missing it. CREATE TABLE Regions ( Country CHAR(4) NOT NULL, Date_Start CHAR(4) NOT NULL, Date_End CHAR(4) default NULL, # this one ... Description CHAR(50) ) Engine = MyISAM; Regions table is filled with a tab-delimited input table like: 000119972500 France 10001976WORLD 10101976INTRA-EUR LOAD DATA INFILE '../Regions.txt' INTO TABLE Regions FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 0 LINES; SELECT * FROM Regions WHERE Country = 1000; +-++--+--+ | country | date_start | date_end | description | +-++--+--+ | 1000 | 1976 || WORLD | | 1010 | 1976 || INTRA-EUR | | 1011 | 1976 || EXTRA-EUR | etc. Question: why is NULL not shown for date_end ? DELETE FROM Regions WHERE Country = 1000 AND Date_End 2001; Query OK; 33 rows affected; SELECT * FROM Regions WHERE Country = 1000; Empty Set Question: So NULL values are matching Date_End 2001 in the DELETE ??? I am using MySQL version 5.0.15-nt. Help will be appreciated. Cor Vegelin
how to test if strings are numeric ?
Hi everybody, I looked for a function to test whether a string is numeric (having characters 0..9 only). I found in the 5.0 manual Cast() and Convert(), but I don't think these do what I need. Any suggestion what function to use ? Thanks in advance, Cor
drop primary key too slow ?
Hi Friends, I have a MyISAM table with about 10 million rows. When I drop the (multi-column) Primary Key (PK) it takes more than 10 minutes. I thought that any key or index was a kind of table in itself. And if so, dropping the PK could be as fast as a Truncate statement (a second). Am I missing something here ? I am using MySQL version 5.0.15. TIA. Cor
Re: dynamic fieldname to assign to
Thanks, Roger You pointed me to the right direction. The only part I had to change, was using the SET term once, as below. UPDATE Data INNER JOIN Updates ON ... SET Data.Jan = IF(Updates.Month = 1,Updates.Cell,Data.Jan), Data.Feb = IF(Updates.Month = 2,Updates.Cell,Data.Feb), Data.Mar = IF(Updates.Month = 3,Updates.Cell,Data.Mar), etc. And it works much faster than 12 separate queries. Regards, Cor Vegelin - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: C.R.Vegelin [EMAIL PROTECTED] Sent: Thursday, November 17, 2005 11:53 PM Subject: Re: dynamic fieldname to assign to C.R.Vegelin wrote: Hi All, I have a simple problem and hope you can help me. I have an input table Updates with various fields, incl. Month and MonthlyValue. The field Updates.Month ranges from 1 to 12. I also have a target Data table with 12 fields, called Jan, Feb, Mar, ... `Dec`. Depending on Updates.Month the MonthlyValue must be put in the proper Data field. Now I use 12 UPDATE queries, like: UPDATE Data INNER JOIN Updates ON ... SET Data.Jan = Updates.Cell WHERE Updates.Month = 1; ... UPDATE Data INNER JOIN Updates ON ... SET Data.Dec = Updates.Cell WHERE Updates.Month = 12; My question: can it be done in a single query ? Try something like this: UPDATE Data INNER JOIN Updates ON ... SET Data.Jan = IF(Updates.Month = 1,Updates.Cell,Data.Jan), SET Data.Feb = IF(Updates.Month = 2,Updates.Cell,Data.Feb), SET Data.Mar = IF(Updates.Month = 3,Updates.Cell,Data.Mar), ... -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dynamic fieldname to assign to
Hi All, I have a simple problem and hope you can help me. I have an input table Updates with various fields, incl. Month and MonthlyValue. The field Updates.Month ranges from 1 to 12. I also have a target Data table with 12 fields, called Jan, Feb, Mar, ... `Dec`. Depending on Updates.Month the MonthlyValue must be put in the proper Data field. Now I use 12 UPDATE queries, like: UPDATE Data INNER JOIN Updates ON ... SET Data.Jan = Updates.Cell WHERE Updates.Month = 1; ... UPDATE Data INNER JOIN Updates ON ... SET Data.Dec = Updates.Cell WHERE Updates.Month = 12; My question: can it be done in a single query ? Thanks in advance, Cor
