Re: LEFT JOIN and WHERE: identical or not and what is better?
'where' is a filter. You're limiting records based on a criterion. 'on' is used for joining. On Mon, Apr 25, 2011 at 10:42 AM, Andre Polykanine an...@oire.org wrote: Hello everyone, Sorry for my beginner question. Actually I have been using MySql for a long time but I just start using some advanced things (earlier I accomplished those tasks with PHP), so I will be asking stupid questions, please bear with me. Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mmal...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
better way to backup 50 Gig db?
I'm using MySQL to manage data on my computer . The total data is 50 Gig in MyISAM folders. As I type, I already have the folder with the myd, frm, etc being copied offsite. As I understand it, if this computer dies tomorrow, I can reinstall MySQL on a new computer, drag over the archive, stick the folder under data and I'm back in business. Or am I dreaming? I'd rather be corrected now than find out the hard way. Any advice?+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
reshaping challenge
I have a table with two columns, ID and order. Each ID can be repeated up to 16 timers. I need to reshape it so that I have one row per ID, and columns order1, order 2,...order 16, and one number that lists how many orders there actually were. No this is not a homework assignment. I'm trying to make sense out of a legacy project. Can someone help me out? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
fast update with 1:1 table?
I have two tables, A and B. B has newer data, A has more columns. I want to update some of the columns in A with all but one of the columns in B. They have the same number of records -- about eight million -- and one key column in common for matching. (That's the one from B I don't want to update) I get the idea that using UPDATE...WHERE is not my fastest option. Can anyone suggest an alternative? Not sure about LOAD INFILE and REPLACE because they seem to cover entire rows. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
getting 64 bit machine -- need to compile workbench?
I've already downloaded the 64 bit build of MySQL to have ready for a 64 bit machine I have coming. But the only available download for Workbench binaries is 32 bit. So I have a few questions: (1) will the 32 bit Workbench work with 64 bit MySQL under Windows XP (64 bit)? (2) if I need to compile Workbench, can I use the MinGW compiler? (3) any advantage in compiling MySQL? Would the MinGW compiler be appropriate for it as well? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MyISAM better than innodb for large files?
I'm going to be setting up a MySQL database for a project. My reading indicates that MyISAM (default) is going to be better than InnoDB for the project but I want to be sure I have the trade-offs right. This is going to be a very large data file -- many gigabytes -- only used internally, and once installed perhaps updated once a year, queried much more often. MyISAM apparently has the advantage in memory and time overheads. InnoDB's advantage seems to be better recovery from disk crashes. Should I stick with MyISAM (MySQL default), or does the recovery issue mean I'm better off using InnoDB for an insurance policy? Inexperienced minds want to know -- ideally, from experienced minds. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MyISAM better than innodb for large files?
You want the crash safety and data integrity that comes with InnoDB. Even more so as your dataset grows. It's performance is far better than myisam tables for most OLTP users, and as your number of concurrent readers and writers grows, the improvement in performance from using innodb over myisam becomes more pronounced. His scenario is perhaps updated once a year, though, so crash recovery and multiple writer performance is not important. And the concurrent reader and writer number is set at one, unless I undergo mitosis or something. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MyISAM better than innodb for large files?
Didn't even know that one existed. It has an attraction, esp. in terms of backing up the data. But the link refers to the performance benefit in accessing one line at a time. Supposing I was doing a search for all records where a particular string is present -- what would the overhead be in the searching of the compressed file? On Fri, Apr 2, 2010 at 9:24 PM, Walter Heck - OlinData.com li...@olindata.com wrote: Ah, if you are single-user and updating really is a special occasion that is completely in your control, you could even use compressed MyISAM. That makes the table read-only though, but it does give performance benefits: http://dev.mysql.com/doc/refman/4.1/en/myisampack.html good luck! Walter Heck Engineer @ Open Query (http://openquery.com) On Sat, Apr 3, 2010 at 08:50, Mitchell Maltenfort mmal...@gmail.com wrote: You want the crash safety and data integrity that comes with InnoDB. Even more so as your dataset grows. It's performance is far better than myisam tables for most OLTP users, and as your number of concurrent readers and writers grows, the improvement in performance from using innodb over myisam becomes more pronounced. His scenario is perhaps updated once a year, though, so crash recovery and multiple writer performance is not important. And the concurrent reader and writer number is set at one, unless I undergo mitosis or something. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
music.sql with FK from Learning MySQL -- 1005 error?
I've been mucking with the O'Reilly book Learning MySQL for the obvious purpose of learning MySQL. The book offers two versions of a program: http://learningmysql.com/Downloads/Files/Data/SQL_files_with_foreign_key_references/music.sql sticks at creating the track table. However, its sibling programs http://learningmysql.com/Downloads/Files/Data/SQL_files_with_foreign_key_references/University.sql and http://learningmysql.com/Downloads/Files/Data/SQL_files_with_foreign_key_references/Flight.sql work fine, as does the version without foreign key references If this helps explain it, I'm using the latest stable community MySQL on a Dell Optiplex GX620 with 0.5 Gig RAM and Windows XP. The error from show innodb status follows. Can anyone clarify what might be causing the problem? 'InnoDB', '', ' = 100322 17:30:47 INNODB MONITOR OUTPUT = Per second averages calculated from the last 40 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 7, signal count 7 Mutex spin waits 0, rounds 70, OS waits 1 RW-shared spins 10, OS waits 5; RW-excl spins 1, OS waits 1 LATEST FOREIGN KEY ERROR 100322 17:29:47 Error in foreign key constraint of table music/track: FOREIGN KEY (album_id) REFERENCES album(album_id) ): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with = InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html for correct foreign key definition. TRANSACTIONS Trx id counter 0 3373 Purge done for trx''s n:o 0 3370 undo n:o 0 0 History list length 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 792 MySQL thread id 2, query id 45 localhost root show innodb status FILE I/O I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o''s: 0, sync i/o''s: 0 Pending flushes (fsync) log: 0; buffer pool: 0 34 OS file reads, 109 OS file writes, 36 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 769826 Log flushed up to 0 769826 Last checkpoint at 0 769826 0 pending log writes, 0 pending chkp writes 29 log i/o''s done, 0.00 log i/o''s/second -- BUFFER POOL AND MEMORY -- Total memory allocated 15522286; in additional pool allocated 1389568 Dictionary memory allocated 24888 Buffer pool size 512 Free buffers 484 Database pages 27 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 24, created 3, written 79 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 3936, state: waiting for server activity Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s END OF INNODB MONITOR OUTPUT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: music.sql with FK from Learning MySQL -- 1005 error?
that makes sense except why is that not a problem for flight.sql and university.sql? On 3/22/10, Martin Gainty mgai...@hotmail.com wrote: FOREIGN KEY (album_id) REFERENCES album(album_id)whichever value is being used for to populate album_id is NOT presently as a row in the album table (and therefore not in the index) populate the album record BEFORE inserting / updating the table which references album_id thru the FK Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: mmal...@gmail.com Date: Mon, 22 Mar 2010 18:00:27 -0400 Subject: music.sql with FK from Learning MySQL -- 1005 error? To: wi...@lists.mysql.com; mysql@lists.mysql.com I've been mucking with the O'Reilly book Learning MySQL for the obvious purpose of learning MySQL. The book offers two versions of a program: http://learningmysql.com/Downloads/Files/Data/SQL_files_with_foreign_key_references/music.sql sticks at creating the track table. However, its sibling programs http://learningmysql.com/Downloads/Files/Data/SQL_files_with_foreign_key_references/University.sql and http://learningmysql.com/Downloads/Files/Data/SQL_files_with_foreign_key_references/Flight.sql work fine, as does the version without foreign key references If this helps explain it, I'm using the latest stable community MySQL on a Dell Optiplex GX620 with 0.5 Gig RAM and Windows XP. The error from show innodb status follows. Can anyone clarify what might be causing the problem? 'InnoDB', '', ' = 100322 17:30:47 INNODB MONITOR OUTPUT = Per second averages calculated from the last 40 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 7, signal count 7 Mutex spin waits 0, rounds 70, OS waits 1 RW-shared spins 10, OS waits 5; RW-excl spins 1, OS waits 1 LATEST FOREIGN KEY ERROR 100322 17:29:47 Error in foreign key constraint of table music/track: FOREIGN KEY (album_id) REFERENCES album(album_id) ): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with = InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html for correct foreign key definition. TRANSACTIONS Trx id counter 0 3373 Purge done for trx''s n:o 0 3370 undo n:o 0 0 History list length 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 792 MySQL thread id 2, query id 45 localhost root show innodb status FILE I/O I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o''s: 0, sync i/o''s: 0 Pending flushes (fsync) log: 0; buffer pool: 0 34 OS file reads, 109 OS file writes, 36 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 769826 Log flushed up to 0 769826 Last checkpoint at 0 769826 0 pending log writes, 0 pending chkp writes 29 log i/o''s done, 0.00 log i/o''s/second -- BUFFER POOL AND MEMORY -- Total memory allocated 15522286; in additional pool allocated 1389568
Q on Mysql install on OS X (SNow Leopard)
For some reason, the thing doesn't show the problem I posted already about foreign keys, but what it won't let me do is drop a schema once I've created it. How do I change permissions so MySQL on OS X can delete directories? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
