Are equi-joins faster than left joins?
I have 3 tables that are 1:1 and will always have a row for a given product,_code & date. If I want to join them together, is it going to be faster to use an equi join or a left join, or does it matter? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto increment?
I read your other replies about the timestamp not working. I still think adding the updated and created fields is a good idea in general, to any table. I have some questions about the below since the original suggestion would not work for you. On Apr 2, 2009, at 12:35 AM, Andreas Pardeike wrote: +-+--+--+-+--- ++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+--- ++ | id | int(11) | NO | PRI | NULL | auto_increment | | foo | varchar(255) | NO | | NULL || | bar | varchar(255) | NO | | NULL || | payload | longblob | YES | | NULL || +-+--+--+-+--- ++ where 'payload' size usually is quite big! Now I want to manipulate a number of rows in a non-trivial way and at the same time set 'id' for those rows to a new number in the sequence. I.e. UPDATE test SET id = NEXT_INSERT_ID(), foo = some_function(foo) WHERE bar ... My motivation is that this table has several consumers in other applications that keep track of the biggest 'id' they have seen between polls and thus can keep track of new and changed rows. I am a little stumped on this, since id is auto_increment, do you start to see gaps in your id's? This is not undesirable to you? Right now, I solve this problem by copying the rows to a temporary table, then delete them and insert them from the temporary table but this moves my huge payload around which I really want to avoid. How can I solve this in a better way? How about changing the initial insert logic. The above table would keep the id but be used as your key. Add a second id of payload_id. Create a new table with id, payload_id, and payload. Store just the payload in a separate table, connected with the id = payload_id. Now you are never touching your payload data, which is too large. Also, I have been in cases with batch uploads where performance is an issue, and used the INSERT DELAYED features of mysql, where the the insert is sent in one command, but the server will do it when there is idle time to deal with it. You have to be a little careful to anticipate a server crash or connection failure, but rollback can solve that easily. There does not seem to be an UPDATE DELAYED syntax, but I did just learn of The UPDATE statement supports the following modifiers: • If you use the LOW_PRIORITY keyword, execution of the UPDATE is delayed until no other clients are reading from the table. This affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE). Maybe you can use the LOW_PRIORITY keyword in your update commands to your advantage? -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump failing to load data
Hi, On Thu, Apr 2, 2009 at 1:18 PM, Virgilio Quilario wrote: >> Hi, >> >> MySQL v4.1.22 on Linux 2.6.18-6-686 >> >> I have a dump file generate with mysqldump created by a version 4.1.10 >> server. >> >> I want to import the dump file into a different server. When I run >> >> mysqldump --database mydb --debug < mydumpfile.sql If you are running that command to import then you are sure to have a problem. Use: mysql < dumpfile http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html Or in your case mysql mydb < mydumpfile.sql Cheers, Ewen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump failing to load data
> Hi, > > MySQL v4.1.22 on Linux 2.6.18-6-686 > > I have a dump file generate with mysqldump created by a version 4.1.10 server. > > I want to import the dump file into a different server. When I run > > mysqldump --database mydb --debug < mydumpfile.sql > > I get the following: > > -- MySQL dump 10.9 > -- > -- Host: localhost Database: mydb > -- -- > -- Server version 4.1.22-debug-log > > /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; > /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; > /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; > /*!40101 SET NAMES utf8 */; > /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; > /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, > FOREIGN_KEY_CHECKS=0 */; > /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; > /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; > > /*!40101 SET sql_mo...@old_sql_mode */; > /*!40014 SET foreign_key_chec...@old_foreign_key_checks */; > /*!40014 SET unique_chec...@old_unique_checks */; > /*!40101 SET character_set_clie...@old_character_set_client */; > /*!40101 SET character_set_resul...@old_character_set_results */; > /*!40101 SET collation_connecti...@old_collation_connection */; > /*!40111 SET sql_not...@old_sql_notes */; > > > The database remains empty. Is there some incompatibility between the > data I am trying to import and the installed server? > > There is the following from the trace log: > | | >mysql_select_db > | | enter: db: 'spl2' > | | >net_clear > | | | >vio_blocking > | | | | enter: set_blocking_mode: 0 old_mode: 1 > | | | | exit: 0 > | | | | | | >vio_read > | | | | enter: sd=4, buf=0xb7c97008, size=1047551 > | | | | vio_error: Got error 11 during read > | | | | exit: -1 > | | | | | | >vio_blocking > | | | | enter: set_blocking_mode: 1 old_mode: 0 > | | | | exit: 0 > | | | | | > > User time 0.01, System time 0.00 > Maximum resident set size 0, Integral resident set size 0 > Non-physical pagefaults 679, Physical pagefaults 0, Swaps 0 > Blocks in 0 out 0, Messages in 0 out 0, Signals 0 > Voluntary context switches 4, Involuntary context switches 3 > > > Does anyone have any ideas what the probem might be? > Thanx, > Dp. hi, mysql error 11 indicates that the system cannot create new thread or resource is temporarily unavailable. two reasons: 1. server is out of memory 2. used up all file descriptors -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto increment?
Steve & Scott, Thanks for the suggestions. The problem with a timestamp is that it's not fine granular. The consumer application can record last_poll_time and if it is X then either of the following will not work: 1) select * from table where tstamp >= X -> this fails because it will receive rows that we already have processed 2) select * from table where tstamp > X -> this fails because if the producer application updates rows faster than 1 second, several rows can get the same timestamp and in the worst case, the consumer application will run the select query in the middle of that updating and thus get only a partial result. The next select will thus skip the remaining rows with the same timestamp 3) having a 'processed' boolean column -> this fails with several consumers that will clear that flag and thus prevent other consumers to see those rows I once read that if an auto_increment column is set to NULL then it will become a new number in the sequence but I was not able to get this to work. Any other solutions? /Andreas Pardeike On 2 apr 2009, at 10.11, Scott Haneda wrote: Add a column of type timestamp which, by default, will be updated every time a record is inserted or updated. Then the other applications can simply select records with timestamp > last_poll_time. My same suggestion as well. I go a far as to have at least, three standard fields to any table I make: CREATE TABLE IF NOT EXISTS `foobar` ( `id` int(11) NOT NULL auto_increment, `udpated` timestamp NOT NULL default '-00-00 00:00:00' on update CURRENT_TIMESTAMP, `created` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ; You never know when you want to know time and date, and this makes it brainless. So any new record you add you will set "created = NOW();", outside of that, never worry about the updated, field, it gets set automatically any time there is any change to the record. I am near certain, there is one small thing to note, which is for example, if you "update foobar set something = 'test' where id = 1" and something was already at "test", since no real update/change happened the time-stamp is not going to get updated. * There is a version of mysql 4, that going forward, had a change to how `timestamp` was defined. If your old data is in version four, and you plan to move to a new version, look out for that issue. Thanks for "welcome" :) -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto increment?
On Apr 2, 2009, at 12:51 AM, Steve Edberg wrote: At 9:35 AM +0200 4/2/09, Andreas Pardeike wrote: Hi, I have a table 'test' +-+--+--+-+--- ++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+--- ++ | id | int(11) | NO | PRI | NULL | auto_increment | | foo | varchar(255) | NO | | NULL || | bar | varchar(255) | NO | | NULL || | payload | longblob | YES | | NULL || +-+--+--+-+--- ++ where 'payload' size usually is quite big! Now I want to manipulate a number of rows in a non-trivial way and at the same time set 'id' for those rows to a new number in the sequence. I.e. UPDATE test SET id = NEXT_INSERT_ID(), foo = some_function(foo) WHERE bar ... My motivation is that this table has several consumers in other applications that keep track of the biggest 'id' they have seen between polls and thus can keep track of new and changed rows. Right now, I solve this problem by copying the rows to a temporary table, then delete them and insert them from the temporary table but this moves my huge payload around which I really want to avoid. How can I solve this in a better way? /Andreas Pardeike Add a column of type timestamp which, by default, will be updated every time a record is inserted or updated. Then the other applications can simply select records with timestamp > last_poll_time. My same suggestion as well. I go a far as to have at least, three standard fields to any table I make: CREATE TABLE IF NOT EXISTS `foobar` ( `id` int(11) NOT NULL auto_increment, `udpated` timestamp NOT NULL default '-00-00 00:00:00' on update CURRENT_TIMESTAMP, `created` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ; You never know when you want to know time and date, and this makes it brainless. So any new record you add you will set "created = NOW();", outside of that, never worry about the updated, field, it gets set automatically any time there is any change to the record. I am near certain, there is one small thing to note, which is for example, if you "update foobar set something = 'test' where id = 1" and something was already at "test", since no real update/change happened the time-stamp is not going to get updated. * There is a version of mysql 4, that going forward, had a change to how `timestamp` was defined. If your old data is in version four, and you plan to move to a new version, look out for that issue. Thanks for "welcome" :) -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto increment?
At 9:35 AM +0200 4/2/09, Andreas Pardeike wrote: Hi, I have a table 'test' +-+--+--+-+---++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+---++ | id | int(11) | NO | PRI | NULL | auto_increment | | foo | varchar(255) | NO | | NULL || | bar | varchar(255) | NO | | NULL || | payload | longblob | YES | | NULL || +-+--+--+-+---++ where 'payload' size usually is quite big! Now I want to manipulate a number of rows in a non-trivial way and at the same time set 'id' for those rows to a new number in the sequence. I.e. UPDATE test SET id = NEXT_INSERT_ID(), foo = some_function(foo) WHERE bar ... My motivation is that this table has several consumers in other applications that keep track of the biggest 'id' they have seen between polls and thus can keep track of new and changed rows. Right now, I solve this problem by copying the rows to a temporary table, then delete them and insert them from the temporary table but this moves my huge payload around which I really want to avoid. How can I solve this in a better way? /Andreas Pardeike Add a column of type timestamp which, by default, will be updated every time a record is inserted or updated. Then the other applications can simply select records with timestamp > last_poll_time. steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Centersbedb...@ucdavis.edu | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Auto increment?
Hi, I have a table 'test' +-+--+--+-+--- ++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+--- ++ | id | int(11) | NO | PRI | NULL | auto_increment | | foo | varchar(255) | NO | | NULL || | bar | varchar(255) | NO | | NULL || | payload | longblob | YES | | NULL || +-+--+--+-+--- ++ where 'payload' size usually is quite big! Now I want to manipulate a number of rows in a non-trivial way and at the same time set 'id' for those rows to a new number in the sequence. I.e. UPDATE test SET id = NEXT_INSERT_ID(), foo = some_function(foo) WHERE bar ... My motivation is that this table has several consumers in other applications that keep track of the biggest 'id' they have seen between polls and thus can keep track of new and changed rows. Right now, I solve this problem by copying the rows to a temporary table, then delete them and insert them from the temporary table but this moves my huge payload around which I really want to avoid. How can I solve this in a better way? /Andreas Pardeike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
re: Question!
Hi! > "Jarikre" == Jarikre Efemena writes: Jarikre> Dear sir, Jarikre> Jarikre> I am young web developer using PHP Script in designing interactive website. I desire to include Mysql database on my websites. Jarikre> Jarikre> Please, how do I import, upload/export Mysql database to a website server after creating a Mysql user account and a particular database on my local machine? Jarikre> Jarikre> I will be very grateful if comprehensive response is granted to my question. Just copy the files in your data directory or use 'mysqldump'. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
