{"id":4639,"date":"2012-01-30T17:04:34","date_gmt":"2012-01-30T15:04:34","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=4639"},"modified":"2012-01-30T17:04:34","modified_gmt":"2012-01-30T15:04:34","slug":"mysql-command-line-vs-visual-editors","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/mysql-command-line-vs-visual-editors","title":{"rendered":"MySQL command line vs. visual editors"},"content":{"rendered":"<p>Students in my training classes usually prefer to use some kind of visual editor for MySQL. Typically this would be the software they&#8217;re using at work. Sometimes they just bring over their laptops with the software installed. Or they would use MySQL Workbench, which is what I usually have pre-installed on their desktops.<\/p>\n<p>I see MySQL Workbench, SQLyog, Toad for MySQL, or several more.<\/p>\n<p>I always humbly suggest they <em>close down their software and open up a command line<\/em>.<\/p>\n<p>It isn&#8217;t fancy. It may not even be convenient (especially on Windows, in my opinion). And repeating your last command with a minor modification requires a lot of key stroking. Or you would copy+paste from some text editor. Most students will give it a shot, then go back to their favorite editor.<\/p>\n<p>Well, again and again I reach the same conclusion:<\/p>\n<h4>Visual editors are not as trustworthy as the command line.<\/h4>\n<p>Time and again students show me something on their editor. Behavior seems strange to me. Opening up a console shows a completely different picture.<\/p>\n<p>Things like:<!--more--><\/p>\n<ul>\n<li>The visual editor would open a new connection for every new query (oh, so the <strong>@user_defined_variable<\/strong> I&#8217;ve just assigned turns <strong>NULL<\/strong>, or the <strong>TEMPORARY TABLE<\/strong> disappears).<\/li>\n<li>The visual editor will only show <strong>1,000<\/strong> results, via <strong>LIMIT 0,1000<\/strong>. &#8220;But the same query runs <em>so much faster<\/em> on my machine!&#8221;. Well, sure, a <strong>filesort<\/strong> of <strong>1,000,000<\/strong> rows that can satisfy the first <strong>1,000<\/strong> will quit early!<\/li>\n<li>The visual editor shows table definition graphically. &#8220;I didn&#8217;t realize the index did(n&#8217;t) cover this and that columns. I didn&#8217;t realize it only covered first <strong>n<\/strong> characters of my <strong>VARCHAR<\/strong>.&#8221;. That&#8217;s because you can&#8217;t beat <strong>SHOW CREATE TABLE<\/strong>, the definite table structure description.<\/li>\n<li>The visual editor allows for export\/import\/copy\/transfer of tables and rows with just one click! &#8220;Why is it so complicated in the command line to purge <strong>1,000,000<\/strong> rows from a table?&#8221;. Ummm, did you realize the visual editor would typically use a naive approach of doing everything in one huge transaction?<\/li>\n<li>The visual editor is smart. But sometimes you don&#8217;t want smart. You just <a href=\"http:\/\/www.devart.com\/blogs\/dbforge\/index.php\/mind-data-modifications-via-data-editor-in-dbforge-studio-for-mysql.html\">assume simple<\/a>. I personally take great precaution with <em>smart<\/em> solutions. Luckily, with scripts you have so much greater control (i.e. command line options, &#8220;dry-run&#8221; mode, etc.) that I have greater confidence in them.<\/li>\n<\/ul>\n<p>I do like it when a visual editor plays it both <em>smart<\/em> and <em>safe<\/em>, in such way that before doing its smart work it actually presents you with <em>the query it&#8217;s going to issue<\/em>. Which is why I always considered MySQL Query Browser (now replaced by Workbench) to be the visual editor of choice in my classes.<\/p>\n<p>But, at the end of the day, I strongly believe: if you don&#8217;t know how to do it with command line, you can&#8217;t really know how it&#8217;s done.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Students in my training classes usually prefer to use some kind of visual editor for MySQL. Typically this would be the software they&#8217;re using at work. Sometimes they just bring over their laptops with the software installed. Or they would use MySQL Workbench, which is what I usually have pre-installed on their desktops. I see [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[78,51,50],"class_list":["post-4639","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-command-line","tag-opinions","tag-scripts"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1cP","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4639","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=4639"}],"version-history":[{"count":7,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4639\/revisions"}],"predecessor-version":[{"id":4646,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4639\/revisions\/4646"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=4639"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=4639"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=4639"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}