MySQL command line vs. visual editors

January 30, 2012

Students in my training classes usually prefer to use some kind of visual editor for MySQL. Typically this would be the software they'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 MySQL Workbench, SQLyog, Toad for MySQL, or several more.

I always humbly suggest they close down their software and open up a command line.

It isn'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.

Well, again and again I reach the same conclusion:

Visual editors are not as trustworthy as the command line.

Time and again students show me something on their editor. Behavior seems strange to me. Opening up a console shows a completely different picture.

Things like:

  • The visual editor would open a new connection for every new query (oh, so the @user_defined_variable I've just assigned turns NULL, or the TEMPORARY TABLE disappears).
  • The visual editor will only show 1,000 results, via LIMIT 0,1000. "But the same query runs so much faster on my machine!". Well, sure, a filesort of 1,000,000 rows that can satisfy the first 1,000 will quit early!
  • The visual editor shows table definition graphically. "I didn't realize the index did(n't) cover this and that columns. I didn't realize it only covered first n characters of my VARCHAR.". That's because you can't beat SHOW CREATE TABLE, the definite table structure description.
  • The visual editor allows for export/import/copy/transfer of tables and rows with just one click! "Why is it so complicated in the command line to purge 1,000,000 rows from a table?". Ummm, did you realize the visual editor would typically use a naive approach of doing everything in one huge transaction?
  • The visual editor is smart. But sometimes you don't want smart. You just assume simple. I personally take great precaution with smart solutions. Luckily, with scripts you have so much greater control (i.e. command line options, "dry-run" mode, etc.) that I have greater confidence in them.

I do like it when a visual editor plays it both smart and safe, in such way that before doing its smart work it actually presents you with the query it's going to issue. Which is why I always considered MySQL Query Browser (now replaced by Workbench) to be the visual editor of choice in my classes.

But, at the end of the day, I strongly believe: if you don't know how to do it with command line, you can't really know how it's done.

  • That's indeed true. Caching of database and table names in visual editors is also one of the things which may get out of sync.

    There are some good uses for visual editors:
    - BLOB's with images
    - Bug 63184:

    And there are some things which are mostly done with visual editors but which also can be done on the commandline:
    - Simple graphs SELECT REPEAT('=',somevalue/100) FROM tbl WHERE …

  • Very important and interesting points.

    I do LOVE to know more about the differences in behavior between using CLI SQL and phpMySQL (Or WB), Especially, since i use them both, depending on the nature of the task.

    It is definitely important to know how to use SQL from the CLI when you first learn SQL (and MySQL) but using it for as a roll, always, is absolutely against the idea of computers, to my opinion. Since we by using them, are always trying to find a shorter way to do a "task". Same way as SQL is the "GUI" to getting the data in the tables instead of opening the raw tables in some hex editor. I guess.

    Again, I would defently love to know more about the short comes of the "GUI" SQL tools 🙂

  • I fully agree that for training people should learn command line. It does exactly what you tell it to do and nothing more. But I don't think you weighted productivity (in a non-training situation) right.

    Besides I have to object to the generalism here in this post ("GUI tools will do this_and_that"). The question is rather: exactly what GUI tools do exactly what? Can it be configured? Is it transparent for user if the tool does more that the written statement (like adding a LIMIT) if it does and what it does on a GUI operation etc. (ie: is there a complete client-side log or similar, can you preview the SQL for nontrivial operations if you want etc.)?

    This will have to be a *per tool discussion*. If not the discussion is close to ridiculous and definitely misleading IMHO.

  • @Peter,

    I agree to some extent with your points:

    I do not argue on possible productivity gain offered by some tools, and for those who can benefit from such tools. Like I said, many prefer to use GUI tools. Of course there is a good reason for that.
    I cannot possibly cover each and every aspect of working with visual editors (syntax highlighting, drag-and drop to generate JOIN queries, in-place editing of row cells, ...).

    I wanted to concentrate on the (mis)transparency of the workings of visual editors, and I think my post is very clear about exactly what it is it attempts to say. I cannot take the blame for not showing the positive sides of this and that feature set etc. This post covers one aspect which is of interest to me.

    Let's take the particular case of SQLyog. By default it limits result sets to 1,000 rows. There's nothing inherently wrong with that, not evil. And it is configurable. However it is confusing and misleading, in my opinion, to have a query come back so quickly when issued from the visual editor, yet so slowly from the command line.
    Not everyone has the time nor understanding of playing with all these settings.

    I can testify that on a recent class, 5 students were using SQLyog, all of them have been using it for years. To all 5 of them, the unchecking of the "Limit to 1,000 rows" checkbox was big news.

    I disagree that the discussion is close to ridiculous, since I have evidenced such misunderstandings many times, and thus think the point to which you have agreed is very important: people must learn to use the command line.
    I also add that they should keep in mind to compare results with command line.


  • wlad

    I do not think learning process should be centered around what teacher knows. It must be more student oriented. And if students use GUI tools, and they will use them after your course, then it is good if teacher knows them as well. And does not only show the dogmatic "one true way", i.e something teacher personally is most comfortable with.
    Also generally, I do not have much trust in posts that roughly translate to "I don't know it. It sucks"

  • @wlad,
    point well taken.

    Like I said, most students revert back to whatever feels more comfortable to them, and I don't object.

    If my post read to you like "I do not allow use of GUI tools in my classes", then I have failed to deliver the message properly. It is not as such, but rather "I see importance that students know how to use the command line and that what they get from visual editors may not be consistent with what they get from command line". And I confess I do encourage them to use the command line, after all.

    To the other extreme, I don't think the learning process should fully comply with whatever students are doing and using. For example, if they all use Windows, that doesn't mean I shouldn't be telling them on the advantages of using MySQL on linux. I may just even go as far as suggesting that they should try linux.

    I think you have also extra-extrapolated your conclusion about "the dogmatic one true way". I did *not* say people should not be using visual editors. I *do* say they should be using the command line. To be very verbose: they can do so even while working with their visual editors. I stand by my point: if you don't know how to do it with command line, you can't really know how it's done.

    "I don't know it. It sucks" is truly a bad approach. I do not think I take this approach. Since you do believe I do, allow me to take a few days to reflect upon myself. It is possible others see in me things I do not see in myself (though a bit extreme to diagnose a person from a single blog post), and I will think this over as good measure.

  • @Shlomi,

    There are reasons why GUI tools behave in certain ways. A lot of thought has been put into each of these aspects.

    For example, I want to explain why there is a limit of 1000 rows in SQLyog "by default", as this behavior has been specifically pointed out in your post.

    In the command line, it takes a little more effort to look at the contents of a table. At the very minimum, you have to type "select * from ".

    In SQLyog, viewing the data is just a single click on the table name. When it is that simple, users have the tendency to click on one table after another in quick succession when they want to "study" a database. They just want to quickly look at a few rows and see what type of data each of these tables contain.

    For such use-cases, it would be annoying to bring up all rows of a table if that particular table has thousands of rows. It is just wasteful -- both on the client and the server.

    Earlier versions of SQLyog didn't have this "feature" 🙂 We built this based on feedback from thousands of SQLyog users.

  • Mahesh Patil


    I can understand your concern about using Command Line and other Visual Editors.
    Using any Visual Editors definitely you will not be known to syntax for DDL and DML statements.
    For student or MySQL trainee one must use command line for basic SQL operations and understanding MYSQL internals i.e. Related to server metadata, user management etc..
    Once they gain intermediate knowledge about SQL and MySQL internals.. they can opt for Visual tools using this they will improve productivity and something which is found in Visual tool not in command line (Like LIMIT 1000 and pagination kind of result set) as they already gained enough knowledge to check what queries being executed in background.
    Also using Visual tools they can save time for doing tasks such as CREATE TABLE, FK RELATIONSHIP , Build queries, Optimize queries, User Management, Sync Schemas/Data etc.. which can be difficult and time consuming tasks using Command line isn't it? 🙂

  • @Rohit,
    Thank you. Yes, I agree this is a feature, I didn't suggest it is a bug. And I realize many users sent you feedback saying "we just want to see a sample of the data". And you would not be able to load 1,000,000 rows into the GUI widget you are using (who wants to browse through 1,000,000 rows anyway?)

    And your tool wants to be smart. Who wants to use a stupid GUI tool? Being smart is important.

    Everything that is smart falls into the corner of not satisfying the minority who actually want something else.

    I'm suggesting that the command line client, being not-smart, shows the de-facto real picture, and is the place to go to for reference.

  • @Mahesh,
    I agree.

  • Pingback: MySQL command line vs. visual editors – reflections |

  • To start, CLI cannot match the productivity gain of GUI tools and GUIs cannot match the flexibility of CLIs to a certain extent. Keeping the discussion limited to the academic usage, I think learning has to be fun and GUIs add this fun element. Students can easily start playing with GUIs and can visualize things under the hood. I think tools also make students curious to try out something new. They might end up discovering a concept all by themselves which they wouldn't have otherwise unless reading a thick volume.

  • Ben Uphoff

    Regarding "The visual editor would open a new connection for every new query (oh, so the @user_defined_variable I've just assigned turns NULL, or the TEMPORARY TABLE disappears).":

    I use MySQL Workbench for my development work, and most of that work is in fairly sophisticated stored procedures. I do find that my temporary tables persist, as I've been able to query them independently of the output from my procedures.

    I concur with your sentiment that people should know and be able to develop SQL via the command line; however, editing thousand-line plus procedures using the command line is cumbersome; a visual editor like MySQL Workbench is the way to go

  • @Ben,
    Yes, no doubt it is unthinkable to create stored routines code directly with the command line. Visual editors are great for that.

    BTW, an alternative is to use the SOURCE command from the command line client, such that I edit my stored routine code in my favorite text editor, then just SOURCE '/path/to/my/code.sql'

Powered by Wordpress and MySQL. Theme by