experience real-world insight

User Interface Issues in mysql

As a user of mysql I have found it to be an incredibly powerful tool. However, I have also found some issues with the interface that are minor, yet constant stumbling blocks.

This document outlines some of these issues, and proposes suggestions to resolve these issues. The suggestions consider implementation concerns, but a more technical design document will be required to implement these changes. The purpose of this document is to build consensus as to how the interface need to be changed for improved usability.

The issues raised mainly relate to consistency, so that similar functions or commands should have similar interfaces. Inconsistency in the interface requires that the user must memorize the exceptions to the rule, which results in increased learning time, and a higher frequency of errors. It has been demonstrated that that commands sets are easier to learn when they are internally consistent[1], and that many user errors can be attributed to inconsistent command syntax[2].

Issue 1: Confusion between 'from' and 'in'

When a user wants to show the tables in a database, they cannot use the following command:

  mysql> show tables in mysql;
  ERROR 1064: You have an error in your SQL syntax near 'in mysql' at line 1

They have to remember to use 'from' not 'in'.

  mysql> show tables from mysql;
  +-----------------+
  | Tables in mysql |
  +-----------------+
  | columns_priv    |
  | db              |
  | func            |
  | host            |
  | tables_priv     |
  | user            |
  +-----------------+
  6 rows in set (0.00 sec)

However, when they use the correct syntax, notice how the table heading in the results uses the incorrect term 'in'.

The interface encourages the user to remember the incorrect form of this command.

Suggestion

Make FROM and IN synonymous in the SHOW TABLES command.

This solution could also be extended to so that FROM and IN were synonymous when used anytime in the SHOW command.

For example, the following would be equivalent:

  SHOW COLUMNS FROM user FROM mysql;
  SHOW COLUMNS IN user IN mysql;
  SHOW COLUMNS IN user FROM mysql;

---

Issue 2: Singular vs Plural

A database contains tables, tables contain columns, and tables contain indexes.

The SHOW command allows a user to view these objects in the database.

  mysql> show databases;
  mysql> show tables from mysql;
  mysql> show columns from user;
  mysql> show index from user;

Notice how the last command uses the singular form, whereas all the other commands use plural forms. If you attempt to use the plural form, you get an error:

  mysql> show indexes from user;
  ERROR 1064: You have an error in your SQL syntax near 'indexes from user' at line 1

Suggestion

1. Make "show index" and "show indexes" synonymous.

2. Make the singular form of the objects synonymous with the plural version.

  Existing              Proposed
  ------------------------------------
  show databases        show database
  show tables           show table
  show columns          show column
  show index            show indexes

---

Issue 3: Starting and stopping mysql

To start mysqld, the 'safe_mysqld' script is recommended. To stop mysqld the command is, 'mysqladmin shutdown'. While there are sound technical reasons for the very different ways of starting and stopping mysql, there would be less cognitive load on the user if the techniques to start and stop mysqld were similar, or more explicitly tied together in the interface.

Suggestions:

1. Rename the "safe_mysqld" script so that the name starts with "mysql". This would make it consistent with all of the other mysql tools. It would also make it easier for the user to "find" the command using shell tab completion.

  Action     Existing              Proposed 
  ----------------------------------------------------
  startup    safe_mysqld           mysqld_safe
  shutdown   mysqladmin shutdown   mysqladmin shutdown

Note: For backwards compatability a symbolic link would need to be added so that "safe_mysqld" would point to "mysqld_safe".

2. The names "startup" and "shutdown" are orthogonal and often used together. mysqladmin uses "shutdown", so it would be beneficial for the user to see a command that contained "startup" in it instead of "safe".

Add a symbolic link from "mysqld_startup" to "mysqld_safe".

  Action     Existing              Proposed
  -----------------------------------------------------
  startup    mysqld_safe           mysqld_startup
  shutdown   mysqladmin shutdown   mysqladmin shutdown

3. In order to inform the user about the proper way to shutdown the database, when "mysqld_safe" starts, it should include a line such as:

  Note: to shutdown mysql cleanly use the "mysqladmin shutdown" command.

---

Issue 4: Specifying the password on the command line

All of the short command line options that take an argument have a space separating the option from the argument. The exception to this rule is the password option. There is no reason from the user's perspective to treat the password differently from the other command line arguments, and there is more cognitive load on the user to have to remember this exception.

If the user forgets about the special handling of the password argument command line, then the password is requested from the user, and the original password is interpreted as a database name.

e.g.

> mysqlshow -u root -p test mysql
Enter password:
mysqlshow: Cannot list fields in db: test, table: mysql: Table 'test.mysql' doesn't exist


Suggestion

Treat the password argument the same as other arguments. For backwards compatibility, the old behavior should be retained.

---

Future Discussion

The document and other mysql interface issues can be discussed at:

Quick Topic http://www.quicktopic.com/7/H/ekc3yvN3wMSnL

References

[1] Barnard, Phil J., and Jonathan Grudin, "Command Names," Handbook of Human-Computer Interaction, Martin Helander, ed., (Amsterdam: North-Holland, 1998) pp. 237-55.

[2] Kraut, Robert E., Stephen J. Hanson, and James M. Farber, "Command Use and Interface Design," Proceedings, CHI '83, 1983, pp.120-24, ACM.

Colophon

All tests were done with: mysql Ver 9.32 Distrib 3.22.21, for pc-linux-gnu (i686)
Prepared by: Jason Moore - jmoore[@]sober.dk
Date: Wed Jun 6 2001
Version: 0.1

Home | About Xinsight | Blog | Contact Info | Client Area