Re: Import file into MySQL Database..
On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 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: Import file into MySQL Database..
-Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: 09 August 2007 14:16 To: Gary Josack Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! First off, to me it looks like your data is in the format ... state,zip,date,xcode,reason but your field list is ... state,zip,xcode,reason,date I have a cron job that updates one of my tables with a CSV file. This uses the following command which always works well: load data infile table.csv into table table_name fields terminated by ',' enclosed by '' lines terminated by '\r\n' starting by '' ignore 1 lines; The CSV file has Windows line endings and is imported by MySql on Linux. You wouldn't need the enclosed by bit for your data though. HTH, Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
On Aug 9, 2007, at 10:11 AM, Edward Kay wrote: -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: 09 August 2007 14:16 To: Gary Josack Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! First off, to me it looks like your data is in the format ... state,zip,date,xcode,reason but your field list is ... state,zip,xcode,reason,date I have a cron job that updates one of my tables with a CSV file. This uses the following command which always works well: load data infile table.csv into table table_name fields terminated by ',' enclosed by '' lines terminated by '\r\n' starting by '' ignore 1 lines; The CSV file has Windows line endings and is imported by MySql on Linux. You wouldn't need the enclosed by bit for your data though. HTH, Edward I have tried this many different reasons, and all the possibilities I can think of... the only thing I can figure is it's something to do with my actual file... But I have saved it as a tab separated, csv, both dos and windows line endings... and I just get get it to do it reliably. It throws the data all over the place. I'm about to give up and retype the whole thing... All 900+ records of it... Just so that it gets done... Anyone have any other ideas? (Sorry... Just getting frustrated) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 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: Inserting null values in mysql with PHP
Hi I added the following statement at the end of the my.cnf file: sql-mode=STRICT_ALL_TABLES,ALLOW_INVALID_DATES but I still got the values 0 and 0.00 where no values were entered. I did restart the the mysql daemon of course. What am I still doing wrong? Thanks. On 8/8/07, Christian High [EMAIL PROTECTED] wrote: On 8/8/07, Brent Baisley [EMAIL PROTECTED] wrote: One thing to check is to make sure you are not quoting your NULL value for your insert statement. MySQL will try to convert that to a numeric value, which may end up as 0. On Aug 8, 2007, at 12:55 PM, Mahmoud Badreddine wrote: Hello I have a table which contain a few numerical values. I set the default values to be NULL. When I insert values using phpMyAdmin, it sets the values to NULL correctly. But when I insert using a PHP script that I wrote it sets the values to 0.00or 0. In my script I do test if the values are empty and in case they are I set the variable to NULL. But that still doesn't help. Is that a mysql problem ? Thank you. -- -Mahmoud Badreddine http://www.spreadfirefox.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] As long as you are testing to see if they should be null, and the default is set to null, you could exclude the column all together from the insert statement and you should see they are then recorded in the table as null. cj -- -Mahmoud Badreddine http://www.spreadfirefox.com/
Re: Inserting null values in mysql with PHP
I did remove that column from the insert statement and no text appeared at all in that field under that column. Not even the word NULL. On 8/8/07, Christian High [EMAIL PROTECTED] wrote: On 8/8/07, Brent Baisley [EMAIL PROTECTED] wrote: One thing to check is to make sure you are not quoting your NULL value for your insert statement. MySQL will try to convert that to a numeric value, which may end up as 0. On Aug 8, 2007, at 12:55 PM, Mahmoud Badreddine wrote: Hello I have a table which contain a few numerical values. I set the default values to be NULL. When I insert values using phpMyAdmin, it sets the values to NULL correctly. But when I insert using a PHP script that I wrote it sets the values to 0.00or 0. In my script I do test if the values are empty and in case they are I set the variable to NULL. But that still doesn't help. Is that a mysql problem ? Thank you. -- -Mahmoud Badreddine http://www.spreadfirefox.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] As long as you are testing to see if they should be null, and the default is set to null, you could exclude the column all together from the insert statement and you should see they are then recorded in the table as null. cj -- -Mahmoud Badreddine http://www.spreadfirefox.com/
RE: Import file into MySQL Database..
Could the commas at the end of your data lines be causing a problem? (I've never loaded a CSV file.) 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: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 10:54 AM To: Edward Kay Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 9, 2007, at 10:11 AM, Edward Kay wrote: -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: 09 August 2007 14:16 To: Gary Josack Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! First off, to me it looks like your data is in the format ... state,zip,date,xcode,reason but your field list is ... state,zip,xcode,reason,date I have a cron job that updates one of my tables with a CSV file. This uses the following command which always works well: load data infile table.csv into table table_name fields terminated by ',' enclosed by '' lines terminated by '\r\n' starting by '' ignore 1 lines; The CSV file has Windows line endings and is imported by MySql on Linux. You wouldn't need the enclosed by bit for your data though. HTH, Edward I have tried this many different reasons, and all the possibilities I can think of... the only thing I can figure is it's something to do with my actual file... But I have saved it as a tab separated, csv, both dos and windows line endings... and I just get get it to do it reliably. It throws the data all over the place. I'm about to give up and retype the whole thing... All 900+ records of it... Just so that it gets done... Anyone have any other ideas? (Sorry... Just getting frustrated) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
The Extra commas at the end of some of the lines need to be there to keep everything in the right order, they represent empty fields... Now, I went through on a few of them and added \n to the end of the line, then tried to load the file again with the LINES TERMINATED BY \n and it looks like it went just fine for the ones I did that too... So now I need to add that to the rest... I could have sworn I had done this before and it didn't work though On Aug 9, 2007, at 11:22 AM, Jerry Schwartz wrote: Could the commas at the end of your data lines be causing a problem? (I've never loaded a CSV file.) 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: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 10:54 AM To: Edward Kay Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 9, 2007, at 10:11 AM, Edward Kay wrote: -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: 09 August 2007 14:16 To: Gary Josack Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! First off, to me it looks like your data is in the format ... state,zip,date,xcode,reason but your field list is ... state,zip,xcode,reason,date I have a cron job that updates one of my tables with a CSV file. This uses the following command which always works well: load data infile table.csv into table table_name fields terminated by ',' enclosed by '' lines terminated by '\r\n' starting by '' ignore 1 lines; The CSV file has Windows line endings and is imported by MySql on Linux. You wouldn't need the enclosed by bit for your data though. HTH, Edward I have tried this many different reasons, and all the possibilities I can think of... the only thing I can figure is it's something to do with my actual file... But I have saved it as a tab separated, csv, both dos and windows line endings... and I just get get it to do it reliably. It throws the data all over the place. I'm about to give up and retype the whole thing... All 900+ records of it... Just so that it gets done... Anyone have any other ideas? (Sorry... Just getting frustrated) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 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: Import file into MySQL Database..
Doesn't that (the trailing comma) depend upon whether or not you want the default value for the (missing) field, as opposed to or 0 used for empty fields? Either way, you are right - you should be able to import the data. 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: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 11:29 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. The Extra commas at the end of some of the lines need to be there to keep everything in the right order, they represent empty fields... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inserting null values in mysql with PHP
IF it is a null in that column, you should not see the word 'null'.. and the advise to put quotes around it I read earlier in this thread is completely misguided.. If you insert the string 'null' or 'NULL' into the database, you have just strored a string.. Perhaps it is the form of your queries: NULL needs special handling. YOu cant say SELECT * FROM foo WHERE bar = null; you need to specify: SELECT * FROM foo WHERE bar IS NULL; Nothing ever 'equals' NULL in SQL not even another NULL.. - michael On 8/9/07, Mahmoud Badreddine [EMAIL PROTECTED] wrote: I did remove that column from the insert statement and no text appeared at all in that field under that column. Not even the word NULL. On 8/8/07, Christian High [EMAIL PROTECTED] wrote: On 8/8/07, Brent Baisley [EMAIL PROTECTED] wrote: One thing to check is to make sure you are not quoting your NULL value for your insert statement. MySQL will try to convert that to a numeric value, which may end up as 0. On Aug 8, 2007, at 12:55 PM, Mahmoud Badreddine wrote: Hello I have a table which contain a few numerical values. I set the default values to be NULL. When I insert values using phpMyAdmin, it sets the values to NULL correctly. But when I insert using a PHP script that I wrote it sets the values to 0.00or 0. In my script I do test if the values are empty and in case they are I set the variable to NULL. But that still doesn't help. Is that a mysql problem ? Thank you. -- -Mahmoud Badreddine http://www.spreadfirefox.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] As long as you are testing to see if they should be null, and the default is set to null, you could exclude the column all together from the insert statement and you should see they are then recorded in the table as null. cj -- -Mahmoud Badreddine http://www.spreadfirefox.com/ -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
remove temporary table from SELECT query
I have been pulling my hair out over a temporary table being created in the following query SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. ++-+--+--+-+--+-+---+--+-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+-+--+-+---+--+-+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | ++-+--+--+-+--+-+---+--+-+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6) unsigned default NULL, `created` int(10) unsigned default NULL, `ts` int(10) unsigned default '0', `modified` int(10) unsigned default NULL, `date` date NOT NULL default '-00-00', `comments` smallint(3) unsigned NOT NULL default '1', `views` mediumint(8) NOT NULL default '0', `dir` varchar(10) NOT NULL default 'photos', `server` varchar(20) NOT NULL default 'i1.photoblog.com', `notes` longtext character set utf8 collate utf8_unicode_ci NOT NULL, `titles` text character set utf8 collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`entryid`), KEY `userid` (`userid`), KEY `date` (`date`), KEY `created` (`created`), KEY `ts` (`ts`), FULLTEXT KEY `title` (`title`,`text`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=204306 ; any help or pointers is a BIG help.
RE: remove temporary table from SELECT query
It goes to a temporary table when MySQL does not have enough memory (allocated) to store the temporary results in memory, so it needs to create a temporary table on disk. Try increasing the memory buffer size or eliminating more rows from the query. -Original Message- From: Mike Zupan [mailto:[EMAIL PROTECTED] Sent: Friday, 10 August 2007 4:52 AM To: mysql@lists.mysql.com Subject: remove temporary table from SELECT query I have been pulling my hair out over a temporary table being created in the following query SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. ++-+--+--+-+--+- +---+--+ -+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+-+--+- +---+--+ -+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | ++-+--+--+-+--+- +---+--+ -+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6) unsigned default NULL, `created` int(10) unsigned default NULL, `ts` int(10) unsigned default '0', `modified` int(10) unsigned default NULL, `date` date NOT NULL default '-00-00', `comments` smallint(3) unsigned NOT NULL default '1', `views` mediumint(8) NOT NULL default '0', `dir` varchar(10) NOT NULL default 'photos', `server` varchar(20) NOT NULL default 'i1.photoblog.com', `notes` longtext character set utf8 collate utf8_unicode_ci NOT NULL, `titles` text character set utf8 collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`entryid`), KEY `userid` (`userid`), KEY `date` (`date`), KEY `created` (`created`), KEY `ts` (`ts`), FULLTEXT KEY `title` (`title`,`text`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=204306 ; any help or pointers is a BIG help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
