Re: Two MySql servers, but very different performances for a SELECT JOIN
Tristan Marly schrieb: First, thanks for all your suggestions and for beeing so reactive. @Martin: the explain result was in attachment, but you will have more results in this current mail. @Rob: you are right, the 'show index' shows strange things, cf. below. @Rodolphe: indeed the STRAIGHT_JOIN has been very helpfull, cf. below. @Brent: thanks for this very precise and technical answer. too bad, that all the other list members could not read what was so helpful ... reply to the author instead of to the list is very useful for the audience ... or was it only me who missed their replies (except from Rub Wulsch)? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why doesn't mysql select the correnct index?
Hi. there is a table photo and two queries: mysql show index from photo; mysql show index from photo; +---+++--++---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---+++--++---+-+--++--++-+ | photo | 0 | PRIMARY|1 | photo_id | A |17836101 | NULL | NULL | | BTREE | NULL| | photo | 1 | user_id|1 | user_id| A | 1372007 | NULL | NULL | | BTREE | NULL| | photo | 1 | user_id|2 | banned | A | 1621463 | NULL | NULL | | BTREE | NULL| | photo | 1 | MD5|1 | MD5| A |17836101 | NULL | NULL | | BTREE | NULL| | photo | 1 | category_id|1 | category_id| A | 230 | NULL | NULL | | BTREE | NULL| | photo | 1 | category_id|2 | time | A |17836101 | NULL | NULL | | BTREE | NULL| | photo | 1 | album_id |1 | album_id | A | 575358 | NULL | NULL | | BTREE | NULL| | photo | 1 | album_id |2 | user_id| A | 1981789 | NULL | NULL | | BTREE | NULL| | photo | 1 | album_id_random|1 | album_id | A | 575358 | NULL | NULL | | BTREE | NULL| | photo | 1 | album_id_random|2 | random | A | 8918050 | NULL | NULL | | BTREE | NULL| | photo | 1 | group_id |1 | group_id | A | 12403 | NULL | NULL | | BTREE | NULL| | photo | 1 | group_id |2 | album_id | A | 575358 | NULL | NULL | | BTREE | NULL| | photo | 1 | original_signature |1 | original_signature | A |17836101 | NULL | NULL | | BTREE | NULL| | photo | 1 | file_name |1 | file_name | NULL | 3567220 | NULL | NULL | | FULLTEXT | NULL| +---+++--++---+-+--++--++-+ 14 rows in set (0.00 sec) explain select * FROM photo WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; ++-+---+--+---+--+-+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+---+--+-+ | 1 | SIMPLE | photo | ref | PRIMARY,user_id,album_id,album_id_random,group_id | group_id | 3 | const | 1438 | Using where; Using filesort | ++-+---+--+---+--+-+---+--+-+ 1 row in set (0.00 sec) mysql select * FROM photo WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; Empty set (51.21 sec) mysql explain select * FROM photo use index (user_id) WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; ++-+---+--+---+-+-+-+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
Re: why doesn't mysql select the correnct index?
Changying Li schrieb: Hi. there is a table photo and two queries: mysql show index from photo; mysql show index from photo; +---+++--++---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---+++--++---+-+--++--++-+ | photo | 1 | user_id|1 | user_id| A | 1372007 | NULL | NULL | | BTREE | NULL| | photo | 1 | user_id|2 | banned | A | 1621463 | NULL | NULL | | BTREE | NULL| | photo | 1 | group_id |1 | group_id | A | 12403 | NULL | NULL | | BTREE | NULL| | photo | 1 | group_id |2 | album_id | A | 575358 | NULL | NULL | | BTREE | NULL| +---+++--++---+-+--++--++-+ 14 rows in set (0.00 sec) explain select * FROM photo WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; ++-+---+--+---+--+-+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+---+--+-+ | 1 | SIMPLE | photo | ref | PRIMARY,user_id,album_id,album_id_random,group_id | group_id | 3 | const | 1438 | Using where; Using filesort | ++-+---+--+---+--+-+---+--+-+ 1 row in set (0.00 sec) mysql select * FROM photo WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; Empty set (51.21 sec) mysql explain select * FROM photo use index (user_id) WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; ++-+---+--+---+-+-+-+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+-+-+-+--+-+ | 1 | SIMPLE | photo | ref | user_id | user_id | 4 | const,const | 1694 | Using where; Using filesort | ++-+---+--+---+-+-+-+--+-+ 1 row in set (0.00 sec) mysql select * FROM photo use index (user_id) WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; Empty set (0.00 sec) why does mysql use group_id index ? because in this case group_id would be faster than user_id how to let mysql choose user_id as an index ? what's the mean of 'rows' ? valid rows after applying the WHERE to this index how doese mysql get value of 'rows'? count returned values from index with valid WEHERE I really dont wnat to use 'force index' because I'm using DBIx::Class in perl catalyst framework. why do you want to FORCE INDEX? did you tried an index(user_id, group_id)? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why doesn't mysql select the correnct index?
Sebastian Mendel [EMAIL PROTECTED] writes: Changying Li schrieb: Hi. there is a table photo and two queries: mysql show index from photo; mysql show index from photo; +---+++--++---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---+++--++---+-+--++--++-+ | photo | 1 | user_id|1 | user_id | A | 1372007 | NULL | NULL | | BTREE | NULL | | photo | 1 | user_id|2 | banned | A | 1621463 | NULL | NULL | | BTREE | NULL | | photo | 1 | group_id |1 | group_id | A | 12403 | NULL | NULL | | BTREE | NULL | | photo | 1 | group_id |2 | album_id | A | 575358 | NULL | NULL | | BTREE | NULL | +---+++--++---+-+--++--++-+ 14 rows in set (0.00 sec) explain select * FROM photo WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; ++-+---+--+---+--+-+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+---+--+-+ | 1 | SIMPLE | photo | ref | PRIMARY,user_id,album_id,album_id_random,group_id | group_id | 3 | const | 1438 | Using where; Using filesort | ++-+---+--+---+--+-+---+--+-+ 1 row in set (0.00 sec) mysql select * FROM photo WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; Empty set (51.21 sec) mysql explain select * FROM photo use index (user_id) WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; ++-+---+--+---+-+-+-+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+-+-+-+--+-+ | 1 | SIMPLE | photo | ref | user_id | user_id | 4 | const,const | 1694 | Using where; Using filesort | ++-+---+--+---+-+-+-+--+-+ 1 row in set (0.00 sec) mysql select * FROM photo use index (user_id) WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; Empty set (0.00 sec) why does mysql use group_id index ? because in this case group_id would be faster than user_id but in fact group_id is very slow (51.21 sec), user_id is (0.00 sec) how to let mysql choose user_id as an index ? what's the mean of 'rows' ? valid rows after applying the WHERE to this index the result is empty set, if what you said is true, then the rows must be 0 ? how doese mysql get value of 'rows'? count returned values from index with valid WEHERE I really dont wnat to use 'force index' because I'm using DBIx::Class in perl catalyst framework. why do you want to FORCE INDEX? did you tried an index(user_id, group_id)? because this table is too big. if user_id can resolve this problem, I'll not add one more index . -- Sebastian -- MySQL General Mailing
Re: why doesn't mysql select the correnct index?
Changying Li schrieb: Sebastian Mendel [EMAIL PROTECTED] writes: Changying Li schrieb: Hi. there is a table photo and two queries: mysql show index from photo; mysql show index from photo; +---+++--++---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---+++--++---+-+--++--++-+ | photo | 1 | user_id|1 | user_id| A | 1372007 | NULL | NULL | | BTREE | NULL| | photo | 1 | user_id|2 | banned | A | 1621463 | NULL | NULL | | BTREE | NULL| | photo | 1 | group_id |1 | group_id | A | 12403 | NULL | NULL | | BTREE | NULL| | photo | 1 | group_id |2 | album_id | A | 575358 | NULL | NULL | | BTREE | NULL| +---+++--++---+-+--++--++-+ 14 rows in set (0.00 sec) explain select * FROM photo WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; ++-+---+--+---+--+-+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+---+--+-+ | 1 | SIMPLE | photo | ref | PRIMARY,user_id,album_id,album_id_random,group_id | group_id | 3 | const | 1438 | Using where; Using filesort | ++-+---+--+---+--+-+---+--+-+ 1 row in set (0.00 sec) mysql select * FROM photo WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; Empty set (51.21 sec) mysql explain select * FROM photo use index (user_id) WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; ++-+---+--+---+-+-+-+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+-+-+-+--+-+ | 1 | SIMPLE | photo | ref | user_id | user_id | 4 | const,const | 1694 | Using where; Using filesort | ++-+---+--+---+-+-+-+--+-+ 1 row in set (0.00 sec) mysql select * FROM photo use index (user_id) WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; Empty set (0.00 sec) why does mysql use group_id index ? because in this case group_id would be faster than user_id but in fact group_id is very slow (51.21 sec), user_id is (0.00 sec) ok, at least MySQL does think so how to let mysql choose user_id as an index ? what's the mean of 'rows' ? valid rows after applying the WHERE to this index the result is empty set, if what you said is true, then the rows must be 0 ? no, not the final result, only for this index read about EXPLAIN in the MySQL manual http://dev.mysql.com/doc/refman/5.0/en/using-explain.html -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing - Adding Fields Into MySql From A List
Newbie question! I have a list of field names from another database (not mysql) - like: name phone1 phone2 street city state zip info etc (a bunch more fields) Q: Is there a way I can add these to an existing empty/blank table? Maybe I can use: - phpMyAdmin ? - sql commands with php - loop thru a list of these names? - import field names from CSV? - some other method? I tried a test with php and got NO errors - but no result either ( looked with phpMyAdmin after - the table didn't add or drop the fields... Nothing changed) ?php $sql = 'ALTER TABLE `ztest` ADD `myfield2` VARCHAR(10) NOT NULL;'; $sql = 'ALTER TABLE `ztest` DROP `myfield1`;'; ? I have phpMyAdmin and If there's a way add tables w / php - maybe that would work also If I can just get all the field names in the table as text fields - that would be ok for now - then I can individually change the field type by hand w phpMyAdmin... -- Thanks - RevDave Cool @ hosting4days . com [db-lists] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LEFT JOIN problem
I have a table, eo_name_table, that has exactly 860 unique titles in it. Each record also has a date field, eo_pub_date: +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | eo_name | varchar(255) | | PRI | | | | eo_pub_date | date | YES | | NULL| | +-+--+--+-+-+---+ I have another table, prod, that has many fields in it but the fields of interest are prod_num, prod_title, prod_discont, and prod_published. The other fields are irrelevant Here is the structure of the prod table: +-+---+--+-+-+-- -+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+-- -+ | prod_id | varchar(15) | | PRI | | | | prod_num| mediumint(6) unsigned | YES | MUL | NULL| | | prod_title | varchar(255) | YES | MUL | NULL| | | prod_type | varchar(2)| YES | | NULL| | | prod_vat_pct| decimal(5,2) | YES | | NULL| | | prod_discont| tinyint(1)| YES | | NULL| | | prod_ready | tinyint(1)| YES | | NULL| | | pub_id | varchar(15) | YES | MUL | NULL| | | prod_published | date | YES | | NULL| | | prod_pub_prod_id| varchar(255) | YES | MUL | NULL| | | prod_pub_acct_id| varchar(2)| YES | | NULL| | | prod_pub_doi| date | YES | | NULL| | | prod_pub_resp | date | YES | | NULL| | | prod_pub_prod_url | varchar(255) | YES | | NULL| | | prod_rel_freq | smallint(3) | YES | | NULL| | | prod_content_info | varchar(255) | YES | | NULL| | | prod_info_type | varchar(5)| YES | | NULL| | | prod_language | varchar(50) | YES | | NULL| | | prod_broch_doc | varchar(255) | YES | | NULL| | | prod_samp_doc | varchar(255) | YES | | NULL| | | prod_samp_pgs | varchar(255) | YES | | NULL| | | prod_exec_summ | varchar(255) | YES | | NULL| | | prod_toc_doc| varchar(255) | YES | | NULL| | | prod_e_title_tag| varchar(255) | YES | | NULL| | | prod_meta_tags | varchar(255) | YES | | NULL| | | prod_keywords | varchar(255) | YES | | NULL| | | prod_comments | text | YES | | NULL| | | prod_if_sample_pdf | varchar(255) | YES | | NULL| | | prod_stop_date | date | YES | | NULL| | | prod_hide_web | tinyint(1)| YES | | NULL| | | prod_changed| tinyint(1)| YES | | NULL| | | prod_export | tinyint(1)| YES | | NULL| | | prod_export_pending | tinyint(1)| YES | | NULL| | | prod_scoop_changed | tinyint(1)| YES | | NULL| | | prod_on_scoop | tinyint(1)| YES | | NULL| | | prod_added | datetime | YES | | NULL| | | prod_updated| datetime | YES | | NULL| | +-+---+--+-+-+-- -+ I am trying to break eo_name_table into two sets, based upon matching eo_name_table.eo_pub_date against prod.prod_published. The first query is SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS pub_date, IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num, IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, , Discontinued) AS discont, IF(prod.prod_title IS NOT NULL, prod.prod_title, ) AS match_title FROM eo_name_table LEFT JOIN prod ON eo_name_table.eo_name = prod.prod_title AND eo_name_table.eo_pub_date = prod.prod_published WHERE (prod.prod_discont = 0 OR prod.prod_discont IS NULL) ORDER BY eo_name_table.eo_name; As expected, this gives me exactly 860 rows in the result because the left join should give me (at least) one result row for each row in eo_name_table. Some of these rows, of course, have values for every field. Now I want to find the inverse set: SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS pub_date, IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num, IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, , Discontinued) AS discont, IF(prod.prod_title IS NOT NULL, prod.prod_title, ) AS match_title FROM
Problem attempting to use load data into
Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/ elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; My table is created as such: | elksCurrent | CREATE TABLE `elksCurrent` ( `FName` varchar(40) default NULL, `LName` varchar(40) default NULL, `Add1` varchar(50) default NULL, `Add2` varchar(50) default NULL, `City` varchar(50) default NULL, `State` varchar(20) default NULL, `Zip` varchar(14) default NULL, `XCode` varchar(50) default NULL, `Reason` varchar(20) default NULL, `Record` mediumint(11) NOT NULL auto_increment, PRIMARY KEY (`Record`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 | The error that I'm getting is: | Level | Code | Message| +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 The row it's choking on is this: FIRST NAME LAST NAME ALT ADD ADD CITYST ZIP XCODE Reason Record First Name Last Name 123 Main St Holland MI 49424 1 \t \t \n (Yes I did change the name to protect the innocent! But all data is the correct type in each row) Any Ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 10:29 AM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; My table is created as such: | elksCurrent | CREATE TABLE `elksCurrent` ( `FName` varchar(40) default NULL, `LName` varchar(40) default NULL, `Add1` varchar(50) default NULL, `Add2` varchar(50) default NULL, `City` varchar(50) default NULL, `State` varchar(20) default NULL, `Zip` varchar(14) default NULL, `XCode` varchar(50) default NULL, `Reason` varchar(20) default NULL, `Record` mediumint(11) NOT NULL auto_increment, PRIMARY KEY (`Record`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 | The error that I'm getting is: | Level | Code | Message | +-+--++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 The row it's choking on is this: FIRST NAME LAST NAME ALT ADD ADD CITYST ZIP XCODE Reason Record First Name Last Name 123 Main St Holland MI 49424 1 \t \t \n (Yes I did change the name to protect the innocent! But all data is the correct type in each row) Any Ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] It is probably trying to insert a string of no length into the not null field. Try it with: SET SQL_MODE = ''; -- 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]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 10:47 AM, Rob Wultsch [EMAIL PROTECTED] wrote: It is probably trying to insert a string of no length into the not null field. Try it with: SET SQL_MODE = ''; Above should read into an int field, while the server is in strict mode. -- 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]
Re: LEFT JOIN problem
Hi Jerry, I think the problem is that NULL is not less than or greater than your prod_published date. So you probably have eo_pub_date set to NULL in 56 of your rows. so for eo_name_table.eo_pub_date prod.prod_published or eo_name_table.eo_pub_date = prod.prod_published mysql will rerturn false if eo_name_table.eo_pub_date is NULL for either test. Jerry Schwartz wrote: I have a table, eo_name_table, that has exactly 860 unique titles in it. Each record also has a date field, eo_pub_date: +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | eo_name | varchar(255) | | PRI | | | | eo_pub_date | date | YES | | NULL| | +-+--+--+-+-+---+ I have another table, prod, that has many fields in it but the fields of interest are prod_num, prod_title, prod_discont, and prod_published. The other fields are irrelevant Here is the structure of the prod table: +-+---+--+-+-+-- -+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+-- -+ | prod_id | varchar(15) | | PRI | | | | prod_num| mediumint(6) unsigned | YES | MUL | NULL| | | prod_title | varchar(255) | YES | MUL | NULL| | | prod_type | varchar(2)| YES | | NULL| | | prod_vat_pct| decimal(5,2) | YES | | NULL| | | prod_discont| tinyint(1)| YES | | NULL| | | prod_ready | tinyint(1)| YES | | NULL| | | pub_id | varchar(15) | YES | MUL | NULL| | | prod_published | date | YES | | NULL| | | prod_pub_prod_id| varchar(255) | YES | MUL | NULL| | | prod_pub_acct_id| varchar(2)| YES | | NULL| | | prod_pub_doi| date | YES | | NULL| | | prod_pub_resp | date | YES | | NULL| | | prod_pub_prod_url | varchar(255) | YES | | NULL| | | prod_rel_freq | smallint(3) | YES | | NULL| | | prod_content_info | varchar(255) | YES | | NULL| | | prod_info_type | varchar(5)| YES | | NULL| | | prod_language | varchar(50) | YES | | NULL| | | prod_broch_doc | varchar(255) | YES | | NULL| | | prod_samp_doc | varchar(255) | YES | | NULL| | | prod_samp_pgs | varchar(255) | YES | | NULL| | | prod_exec_summ | varchar(255) | YES | | NULL| | | prod_toc_doc| varchar(255) | YES | | NULL| | | prod_e_title_tag| varchar(255) | YES | | NULL| | | prod_meta_tags | varchar(255) | YES | | NULL| | | prod_keywords | varchar(255) | YES | | NULL| | | prod_comments | text | YES | | NULL| | | prod_if_sample_pdf | varchar(255) | YES | | NULL| | | prod_stop_date | date | YES | | NULL| | | prod_hide_web | tinyint(1)| YES | | NULL| | | prod_changed| tinyint(1)| YES | | NULL| | | prod_export | tinyint(1)| YES | | NULL| | | prod_export_pending | tinyint(1)| YES | | NULL| | | prod_scoop_changed | tinyint(1)| YES | | NULL| | | prod_on_scoop | tinyint(1)| YES | | NULL| | | prod_added | datetime | YES | | NULL| | | prod_updated| datetime | YES | | NULL| | +-+---+--+-+-+-- -+ I am trying to break eo_name_table into two sets, based upon matching eo_name_table.eo_pub_date against prod.prod_published. The first query is SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS pub_date, IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num, IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, , Discontinued) AS discont, IF(prod.prod_title IS NOT NULL, prod.prod_title, ) AS match_title FROM eo_name_table LEFT JOIN prod ON eo_name_table.eo_name = prod.prod_title AND eo_name_table.eo_pub_date = prod.prod_published WHERE (prod.prod_discont = 0 OR prod.prod_discont IS NULL) ORDER BY eo_name_table.eo_name; As expected, this gives me exactly 860 rows in the result because the left join should give me (at least) one result row for each row in eo_name_table. Some of these rows, of course, have
RE: LEFT JOIN problem
From: Bill Newton [mailto:[EMAIL PROTECTED] Sent: Monday, April 14, 2008 2:09 PM To: Jerry Schwartz Cc: 'Mysql' Subject: Re: LEFT JOIN problem Hi Jerry, I think the problem is that NULL is not less than or greater than your prod_published date. So you probably have eo_pub_date set to NULL in 56 of your rows. [JS] I wish it were that simple. There are no rows in eo_name_table where eo_pub_date is NULL. so for eo_name_table.eo_pub_date prod.prod_published or eo_name_table.eo_pub_date = prod.prod_published mysql will rerturn false if eo_name_table.eo_pub_date is NULL for either test. [JS] But it evidently does not. The second one works perfectly, the one above does not. Jerry Schwartz wrote: I have a table, eo_name_table, that has exactly 860 unique titles in it. Each record also has a date field, eo_pub_date: +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | eo_name | varchar(255) | | PRI | | | | eo_pub_date | date | YES | | NULL| | +-+--+--+-+-+---+ I have another table, prod, that has many fields in it but the fields of interest are prod_num, prod_title, prod_discont, and prod_published. The other fields are irrelevant Here is the structure of the prod table: +-+---+--+-+- +-- -+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+- +-- -+ | prod_id | varchar(15) | | PRI | | | | prod_num| mediumint(6) unsigned | YES | MUL | NULL| | | prod_title | varchar(255) | YES | MUL | NULL| | | prod_type | varchar(2)| YES | | NULL| | | prod_vat_pct| decimal(5,2) | YES | | NULL| | | prod_discont| tinyint(1)| YES | | NULL| | | prod_ready | tinyint(1)| YES | | NULL| | | pub_id | varchar(15) | YES | MUL | NULL| | | prod_published | date | YES | | NULL| | | prod_pub_prod_id| varchar(255) | YES | MUL | NULL| | | prod_pub_acct_id| varchar(2)| YES | | NULL| | | prod_pub_doi| date | YES | | NULL| | | prod_pub_resp | date | YES | | NULL| | | prod_pub_prod_url | varchar(255) | YES | | NULL| | | prod_rel_freq | smallint(3) | YES | | NULL| | | prod_content_info | varchar(255) | YES | | NULL| | | prod_info_type | varchar(5)| YES | | NULL| | | prod_language | varchar(50) | YES | | NULL| | | prod_broch_doc | varchar(255) | YES | | NULL| | | prod_samp_doc | varchar(255) | YES | | NULL| | | prod_samp_pgs | varchar(255) | YES | | NULL| | | prod_exec_summ | varchar(255) | YES | | NULL| | | prod_toc_doc| varchar(255) | YES | | NULL| | | prod_e_title_tag| varchar(255) | YES | | NULL| | | prod_meta_tags | varchar(255) | YES | | NULL| | | prod_keywords | varchar(255) | YES | | NULL| | | prod_comments | text | YES | | NULL| | | prod_if_sample_pdf | varchar(255) | YES | | NULL| | | prod_stop_date | date | YES | | NULL| | | prod_hide_web | tinyint(1)| YES | | NULL| | | prod_changed| tinyint(1)| YES | | NULL| | | prod_export | tinyint(1)| YES | | NULL| | | prod_export_pending | tinyint(1)| YES | | NULL| | | prod_scoop_changed | tinyint(1)| YES | | NULL| | | prod_on_scoop | tinyint(1)| YES | | NULL| | | prod_added | datetime | YES | | NULL| | | prod_updated| datetime | YES | | NULL| | +-+---+--+-+- +-- -+ I am trying to break eo_name_table into two sets, based upon matching eo_name_table.eo_pub_date against prod.prod_published. The first query is SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS pub_date, IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num, IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, , Discontinued) AS discont, IF(prod.prod_title IS NOT NULL, prod.prod_title, ) AS match_title
RE: LEFT JOIN problem
I've found yet another oddity with this situation. If I leave the date test off of both JOINs they give the same number of rows, but they give me the wrong number! Neither one of them gives me 860 rows returned. I must not understand how a LEFT JOIN works. By the way, the EXPLAIN for both of my original queries is the same: *** 1. row *** id: 1 select_type: SIMPLE table: eo_name_table type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 860 Extra: Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: prod_title,prod_title_fulltext key: prod_title key_len: 766 ref: giiexpr_db.eo_name_table.eo_name rows: 1 Extra: Using where 2 rows in set (0.05 sec) I have a table, eo_name_table, that has exactly 860 unique titles in it. Each record also has a date field, eo_pub_date: +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | eo_name | varchar(255) | | PRI | | | | eo_pub_date | date | YES | | NULL| | +-+--+--+-+-+---+ I have another table, prod, that has many fields in it but the fields of interest are prod_num, prod_title, prod_discont, and prod_published. The other fields are irrelevant Here is the structure of the prod table: +-+---+--+-+-+-- -+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+-- -+ | prod_id | varchar(15) | | PRI | | | | prod_num| mediumint(6) unsigned | YES | MUL | NULL| | | prod_title | varchar(255) | YES | MUL | NULL| | | prod_type | varchar(2)| YES | | NULL| | | prod_vat_pct| decimal(5,2) | YES | | NULL| | | prod_discont| tinyint(1)| YES | | NULL| | | prod_ready | tinyint(1)| YES | | NULL| | | pub_id | varchar(15) | YES | MUL | NULL| | | prod_published | date | YES | | NULL| | | prod_pub_prod_id| varchar(255) | YES | MUL | NULL| | | prod_pub_acct_id| varchar(2)| YES | | NULL| | | prod_pub_doi| date | YES | | NULL| | | prod_pub_resp | date | YES | | NULL| | | prod_pub_prod_url | varchar(255) | YES | | NULL| | | prod_rel_freq | smallint(3) | YES | | NULL| | | prod_content_info | varchar(255) | YES | | NULL| | | prod_info_type | varchar(5)| YES | | NULL| | | prod_language | varchar(50) | YES | | NULL| | | prod_broch_doc | varchar(255) | YES | | NULL| | | prod_samp_doc | varchar(255) | YES | | NULL| | | prod_samp_pgs | varchar(255) | YES | | NULL| | | prod_exec_summ | varchar(255) | YES | | NULL| | | prod_toc_doc| varchar(255) | YES | | NULL| | | prod_e_title_tag| varchar(255) | YES | | NULL| | | prod_meta_tags | varchar(255) | YES | | NULL| | | prod_keywords | varchar(255) | YES | | NULL| | | prod_comments | text | YES | | NULL| | | prod_if_sample_pdf | varchar(255) | YES | | NULL| | | prod_stop_date | date | YES | | NULL| | | prod_hide_web | tinyint(1)| YES | | NULL| | | prod_changed| tinyint(1)| YES | | NULL| | | prod_export | tinyint(1)| YES | | NULL| | | prod_export_pending | tinyint(1)| YES | | NULL| | | prod_scoop_changed | tinyint(1)| YES | | NULL| | | prod_on_scoop | tinyint(1)| YES | | NULL| | | prod_added | datetime | YES | | NULL| | | prod_updated| datetime | YES | | NULL| | +-+---+--+-+-+-- -+ I am trying to break eo_name_table into two sets, based upon matching eo_name_table.eo_pub_date against prod.prod_published. The first query is SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS pub_date, IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num,
Re: Problem attempting to use load data into
On Apr 14, 2008, at 2:01 PM, Rob Wultsch wrote: On Mon, Apr 14, 2008 at 10:47 AM, Rob Wultsch [EMAIL PROTECTED] wrote: It is probably trying to insert a string of no length into the not null field. Try it with: SET SQL_MODE = ''; Above should read into an int field, while the server is in strict mode. Hi Rob, Where would I set that? I tried to add it to the load data infile line and it didn't like that... Should I try it before I do the indata? -- 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] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
Hi again everyone, After taking the advice of someone offlist I tried the IGNORE 1 LINES and that didn't help... Same result. I've tried a tab delimited file, and a comma separated file. Same result with both. Any other ideas? :) On Apr 14, 2008, at 1:29 PM, Jason Pruim wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/ raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; My table is created as such: | elksCurrent | CREATE TABLE `elksCurrent` ( `FName` varchar(40) default NULL, `LName` varchar(40) default NULL, `Add1` varchar(50) default NULL, `Add2` varchar(50) default NULL, `City` varchar(50) default NULL, `State` varchar(20) default NULL, `Zip` varchar(14) default NULL, `XCode` varchar(50) default NULL, `Reason` varchar(20) default NULL, `Record` mediumint(11) NOT NULL auto_increment, PRIMARY KEY (`Record`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 | The error that I'm getting is: | Level | Code | Message| +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 The row it's choking on is this: FIRST NAME LAST NAME ALT ADD ADD CITYST ZIP XCODE Reason Record First Name Last Name 123 Main St Holland MI 49424 1 \t \t \n (Yes I did change the name to protect the innocent! But all data is the correct type in each row) Any Ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 1:29 PM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; [snip!] The error that I'm getting is: | Level | Code | Message | +-+--++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 That's because it's attempting to insert the name of the columns from your CSV into MySQL --- and 'Record' is not a valid INT. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 3:33 PM, Jason Pruim [EMAIL PROTECTED] wrote: On Apr 14, 2008, at 3:29 PM, Daniel Brown wrote: That's because it's attempting to insert the name of the columns from your CSV into MySQL --- and 'Record' is not a valid INT. Replaced field name with 0 and had the same end result... Just no error. But I get the first row included! Which is just field names and a 0 for good measure :) Any other ideas Master Brown? :) ***Before I get yelled at for not showing respect please note that I know Dan from another list and I am allowed to give him crap like this no matter what he says :P I don't know you from Adam, you insignificant little cur! ;-P Does your file actually have the characters \t \t \n at the end of each row like that? Send it to me as an attachment off-list and I'll help you figure it out and then post back here for the MySQL archives. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LEFT JOIN problem
As usual, the computer is right and I am wrong. The only reason that one query was coming out right is that it just happened the WHERE clause was never failing. It was just luck that my data was just so. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Monday, April 14, 2008 11:26 AM To: 'Mysql' Subject: LEFT JOIN problem I have a table, eo_name_table, that has exactly 860 unique titles in it. Each record also has a date field, eo_pub_date: +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | eo_name | varchar(255) | | PRI | | | | eo_pub_date | date | YES | | NULL| | +-+--+--+-+-+---+ I have another table, prod, that has many fields in it but the fields of interest are prod_num, prod_title, prod_discont, and prod_published. The other fields are irrelevant Here is the structure of the prod table: +-+---+--+-+-+-- -+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+-- -+ | prod_id | varchar(15) | | PRI | | | | prod_num| mediumint(6) unsigned | YES | MUL | NULL| | | prod_title | varchar(255) | YES | MUL | NULL| | | prod_type | varchar(2)| YES | | NULL| | | prod_vat_pct| decimal(5,2) | YES | | NULL| | | prod_discont| tinyint(1)| YES | | NULL| | | prod_ready | tinyint(1)| YES | | NULL| | | pub_id | varchar(15) | YES | MUL | NULL| | | prod_published | date | YES | | NULL| | | prod_pub_prod_id| varchar(255) | YES | MUL | NULL| | | prod_pub_acct_id| varchar(2)| YES | | NULL| | | prod_pub_doi| date | YES | | NULL| | | prod_pub_resp | date | YES | | NULL| | | prod_pub_prod_url | varchar(255) | YES | | NULL| | | prod_rel_freq | smallint(3) | YES | | NULL| | | prod_content_info | varchar(255) | YES | | NULL| | | prod_info_type | varchar(5)| YES | | NULL| | | prod_language | varchar(50) | YES | | NULL| | | prod_broch_doc | varchar(255) | YES | | NULL| | | prod_samp_doc | varchar(255) | YES | | NULL| | | prod_samp_pgs | varchar(255) | YES | | NULL| | | prod_exec_summ | varchar(255) | YES | | NULL| | | prod_toc_doc| varchar(255) | YES | | NULL| | | prod_e_title_tag| varchar(255) | YES | | NULL| | | prod_meta_tags | varchar(255) | YES | | NULL| | | prod_keywords | varchar(255) | YES | | NULL| | | prod_comments | text | YES | | NULL| | | prod_if_sample_pdf | varchar(255) | YES | | NULL| | | prod_stop_date | date | YES | | NULL| | | prod_hide_web | tinyint(1)| YES | | NULL| | | prod_changed| tinyint(1)| YES | | NULL| | | prod_export | tinyint(1)| YES | | NULL| | | prod_export_pending | tinyint(1)| YES | | NULL| | | prod_scoop_changed | tinyint(1)| YES | | NULL| | | prod_on_scoop | tinyint(1)| YES | | NULL| | | prod_added | datetime | YES | | NULL| | | prod_updated| datetime | YES | | NULL| | +-+---+--+-+-+-- -+ I am trying to break eo_name_table into two sets, based upon matching eo_name_table.eo_pub_date against prod.prod_published. The first query is SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS pub_date, IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num, IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, , Discontinued) AS discont, IF(prod.prod_title IS NOT NULL, prod.prod_title, ) AS match_title FROM eo_name_table LEFT JOIN prod ON eo_name_table.eo_name = prod.prod_title AND eo_name_table.eo_pub_date = prod.prod_published WHERE (prod.prod_discont = 0 OR prod.prod_discont IS NULL) ORDER BY eo_name_table.eo_name; As
Re: Problem attempting to use load data into
On Apr 14, 2008, at 3:29 PM, Daniel Brown wrote: On Mon, Apr 14, 2008 at 1:29 PM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; [snip!] The error that I'm getting is: | Level | Code | Message | +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 That's because it's attempting to insert the name of the columns from your CSV into MySQL --- and 'Record' is not a valid INT. Replaced field name with 0 and had the same end result... Just no error. But I get the first row included! Which is just field names and a 0 for good measure :) Any other ideas Master Brown? :) ***Before I get yelled at for not showing respect please note that I know Dan from another list and I am allowed to give him crap like this no matter what he says :P -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Importing - Adding Fields Into MySql From A List
It sounds like you want to easily create a new MySQL table that is a copy of a table in a different DBMS. The way I would do it is generate a DDL script from the other DBMS (create table etc.) For example, SQL Server has a generate script wizard that does it for you automatically. Then take the DDL and modify it to meet your MySQL needs. Then you can run the script in MySQL Query Browser (make sure you're pointed at the right database, or put a USE command at the beginning of your script). Presto, you have the table you want. -Original Message- From: revDAVE [mailto:[EMAIL PROTECTED] Sent: Monday, April 14, 2008 8:25 AM To: mysql@lists.mysql.com Subject: Importing - Adding Fields Into MySql From A List Newbie question! I have a list of field names from another database (not mysql) - like: name phone1 phone2 street city state zip info etc (a bunch more fields) Q: Is there a way I can add these to an existing empty/blank table? Maybe I can use: - phpMyAdmin ? - sql commands with php - loop thru a list of these names? - import field names from CSV? - some other method? I tried a test with php and got NO errors - but no result either ( looked with phpMyAdmin after - the table didn't add or drop the fields... Nothing changed) ?php $sql = 'ALTER TABLE `ztest` ADD `myfield2` VARCHAR(10) NOT NULL;'; $sql = 'ALTER TABLE `ztest` DROP `myfield1`;'; ? I have phpMyAdmin and If there's a way add tables w / php - maybe that would work also If I can just get all the field names in the table as text fields - that would be ok for now - then I can individually change the field type by hand w phpMyAdmin... -- Thanks - RevDave Cool @ hosting4days . com [db-lists] -- 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: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 3:45 PM, Daniel Brown [EMAIL PROTECTED] wrote: Does your file actually have the characters \t \t \n at the end of each row like that? Send it to me as an attachment off-list and I'll help you figure it out and then post back here for the MySQL archives. Sorry, got sidetracked with the day job and the pre-wife nagging me. ;-P Anyway, as I suspected, you did have literal \t and \n characters. I wrote a script to fix it, and I'll link you to the updated CSV file. Run that with the IGNORE 1 ROWS command and you should be set. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Apr 14, 2008, at 4:37 PM, Daniel Brown wrote: On Mon, Apr 14, 2008 at 3:45 PM, Daniel Brown [EMAIL PROTECTED] wrote: Does your file actually have the characters \t \t \n at the end of each row like that? Send it to me as an attachment off-list and I'll help you figure it out and then post back here for the MySQL archives. Sorry, got sidetracked with the day job and the pre-wife nagging me. ;-P Anyway, as I suspected, you did have literal \t and \n characters. I wrote a script to fix it, and I'll link you to the updated CSV file. Run that with the IGNORE 1 ROWS command and you should be set. Just to complete the archives, This did fix it. Make sure you don't try and put literal tab values \t and new line values \n into your data and it should work just fine! So thank you Dan for your help! And everyone else as well! -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using Date Functions in Where Clause
Hello, Basically I want to know if this is a good query for indexing. I have the following query: select count(1) as count from session where last = DATE_SUB(NOW(), INTERVAL :from SECOND) Is it safe to assume that the expression calling the function DATE_SUB is evaluated just once to a fixed date? Thanks Jamie Madill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange performance problem
OK folks, I'm kind of stumped; looking into things a bit more, but thought I'd hit the list and see if anyone had any suggestions for a rock to look under, in case I'm missing it... DB Server: Windows 2003, 8-way CPU, lots of RAM, MySQL 4.1.22-nt binary from MySQL Current Production web server: Linux, RedHat 7.2, MySQL 5.0.51a client from MySQL Binary RPMs New Web Server: Linux, RedHat EL 4, MySQL 5.0.51a client from MySQL Binary RPMs The current production web server has a fairly high traffic load, but DB usage is extremely efficient, so there's not a significant load on the DB server. Connection times from the production web server are almost instantaneous (under 1 second), using the press enter and observe method, using the command-line interface. Connection times from the new web server box take between 4-6 seconds between the time you press enter and the time that you get the MySQL prompt. YSlow in Firefox confirms a consistent average of 4-6 seconds difference between the servers reflected in page load times. At this point, we're trying to see what it is going on between the client and the server; datacenter folks have assured us that it's not a networking issue (although I'm not sure that I'm convinced). We've ruled out any apache or PHP issues causing a problem, as the configurations and build options are identical. Any thoughts of a performance number or configuration option that would make any differences? Thanks much in advance - I'm scratching my head on this one... -Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Incorrect results from sum
Sorry to return to this topic, I haven't found a lot to explain what's happening. I'm trying to total certain nutrients consumed on a given date (though I've removed date temporarily). You'll see I have three items (in two meals) in itemized, and two meal totals in simple. mysql select id, item, carb from my_menu where id in (10, 11, 22); ++-+---+ | id | item| carb | ++-+---+ | 10 | apples, w/skin, raw | 0.138 | | 11 | bananas, raw| 0.228 | | 22 | bread, Arnold Natural Wheat | 0.500 | ++-+---+ 3 rows in set (0.00 sec) mysql select * from itemized; +++-+-+-+--+ | id | date | time_of_day | uid | personal_id | units| +++-+-+-+--+ | 3 | 2008-04-01 | 06:15:00| jmangin | 10 | 167. | | 7 | 2008-04-01 | 12:30:00| jmangin | 11 | 52. | | 6 | 2008-04-01 | 12:30:00| jmangin | 22 | 36. | +++-+-+-+--+ 3 rows in set (0.01 sec) mysql select * from simple; +++-+-+--+-+--+ | id | date | time_of_day | uid | carb | protein | fat | +++-+-+--+-+--+ | 1 | 2008-04-01 | 12:05:00| jmangin | 85.0 |10.0 | 2.3 | | 2 | 2008-04-01 | 18:30:00| jmangin | 80.4 |10.0 | 10.0 | +++-+-+--+-+--+ 2 rows in set (0.01 sec) mysql select sum(my_menu.carb*units) from itemized left join my_menu on personal_id=my_menu.id; +-+ | sum(my_menu.carb*units) | +-+ | 52.9020 | +-+ 1 row in set (0.00 sec) mysql select sum(carb) from simple; ++ | sum(carb) | ++ | 165.4 | ++ 1 row in set (0.01 sec) select round(sum(my_menu.carb * units) + sum(simple.carb),2) from itemized inner join simple using (uid) left join my_menu on itemized.personal_id = my_menu.id; Instead of 218.3 this returns 602, which is (52.9 * 2 items in simple) + (165.4 * 3 items in itemized). Is it possible to get correct totals some other way with this table structure? Or explain why this is wrong? Thanks, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why doesn't mysql select the correnct index?
why does mysql use group_id index ? because in this case group_id would be faster than user_id but in fact group_id is very slow (51.21 sec), user_id is (0.00 sec) ok, at least MySQL does think so I know, but I what I really want to know is how does mysql think so ? how to let mysql choose user_id as an index ? what's the mean of 'rows' ? valid rows after applying the WHERE to this index the result is empty set, if what you said is true, then the rows must be 0 ? no, not the final result, only for this index read about EXPLAIN in the MySQL manual http://dev.mysql.com/doc/refman/5.0/en/using-explain.html I has read it , and it described like what you said, I don't really know what is the mean of 'only for this index', I tried 'select count(*) from photo where group_id=0 and album_id!=0,' ant it get a huge number, but not the value of rows. -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks Regards Changying Li -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incorrect results from sum
On Mon, Apr 14, 2008 at 5:40 PM, Jonathan Mangin [EMAIL PROTECTED] wrote: select round(sum(my_menu.carb * units) + sum(simple.carb),2) from itemized inner join simple using (uid) left join my_menu on itemized.personal_id = my_menu.id; Instead of 218.3 this returns 602, which is (52.9 * 2 items in simple) + (165.4 * 3 items in itemized). Is it possible to get correct totals some other way with this table structure? Or explain why this is wrong? Change the column clause to SELECT * and you'll see what's wrong: you're operating on a set of six rows after doing the joins. You can certainly get the results you want from those tables, but not from a single query unless you use subqueries. (Well, you can use the DISTINCT keyword with SUM, but that has the potential to wreak havoc if you have legitimate duplicate values.) - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ibbackup apply log getting slower and slower
Hi all: I'm a first time poster here...and forgive my broken English... These days i am doing some database restore test using ibbackup. The db's original size is about 350GB ( we know that is already too big ), compressed size is about 130GB. Then i ran ibbackup --apply-log. At first the speed is ok, about 100MB in several seconds. After several hours, about 295GB uncompressed, the proccess getting slower and slower ( abount 100MB in 6 or 7 minutes! ) and become cpu bound. Is it a bug of ibbackup? Or is due to the size of data? Is there any way to help? Now i just can wait... Any suggestion would be welcomed. Thanks. Best regard, Jayven
Create table
Hi, I have created a table name group. CREATE TABLE `group` ( `group_id` int(11) NOT NULL, `group_name` varchar(128) NOT NULL, `date_created` datetime NOT NULL, `created_by` int(11) NOT NULL, `modified_by` int(11) default NULL, `status` char(1) NOT NULL default 'Y', PRIMARY KEY (`group_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; The table was successfully created without any errors. After that when i try to describe it. Its giving error. mysql desc group; ERROR 1064 (42000): 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 'group' at line 1 mysql drop table group; ERROR 1064 (42000): 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 'group' at line 1 Does the problem is with table name. Thanks, -- Krishna Chandra Prajapati Email-id: [EMAIL PROTECTED]
Re: Create table
Do a show tables and see what is the actual table name. I think group is a key work and hence its giving you the error. See the table names in that database. regards anandkl On 4/15/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi, I have created a table name group. CREATE TABLE `group` ( `group_id` int(11) NOT NULL, `group_name` varchar(128) NOT NULL, `date_created` datetime NOT NULL, `created_by` int(11) NOT NULL, `modified_by` int(11) default NULL, `status` char(1) NOT NULL default 'Y', PRIMARY KEY (`group_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; The table was successfully created without any errors. After that when i try to describe it. Its giving error. mysql desc group; ERROR 1064 (42000): 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 'group' at line 1 mysql drop table group; ERROR 1064 (42000): 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 'group' at line 1 Does the problem is with table name. Thanks, -- Krishna Chandra Prajapati Email-id: [EMAIL PROTECTED]
Re: Create table
Hi. group is a reserved word i think you must enclose the table name with single quotes (as when you created the table) in order to avoid the confusion. Carlos Krishna Chandra Prajapati wrote: Hi, I have created a table name group. CREATE TABLE `group` ( `group_id` int(11) NOT NULL, `group_name` varchar(128) NOT NULL, `date_created` datetime NOT NULL, `created_by` int(11) NOT NULL, `modified_by` int(11) default NULL, `status` char(1) NOT NULL default 'Y', PRIMARY KEY (`group_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; The table was successfully created without any errors. After that when i try to describe it. Its giving error. mysql desc group; ERROR 1064 (42000): 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 'group' at line 1 mysql drop table group; ERROR 1064 (42000): 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 'group' at line 1 Does the problem is with table name. Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
