CIS 331: Database Management Using SQL

A Guide to MySQL, Chapter 8: MySQL Special Topics

Objectives:
 

The last chapter of the course introduces a series of concepts that did not fit in other chapters. The objectives important to this chapter are:

  1. Importing data
  2. Exporting data
  3. Performance issues
  4. Analyzing tables
  5. Optimizing queries
  6. MySQL GUI interfaces
Concepts:

Most database systems will allow you to import data from simple text files and to export data as simple text files. Text files of this type must meet specific rules. Text files that contain table data typically separate one field from another with characters that are not expected to be found in the data, such as tab characters or commas. Such files are called tab-delimited and comma-delimited files. The characters that separate fields are called delimiters.

For MySQL, your book states that we must import data from tab-delimited files, and that data should not be enclosed by quotation marks. (In practice, I have not been able to make our instance of MySQL accept such a file. Take this chapter as another one we can study but not do at this time.) The basic command to load data into a table from a text file is
LOAD DATA INFILE 'path_to_file/filename'
INTO TABLE table_name;

The text also states that you may need to tell MySQL what character is used as the field delimiter, and what character or characters mark line returns. An example is given:
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n';

In the example above, \t stands for a tab character. \r stands for a carriage return, and \n stands for a new line. Windows systems generate both a carriage return and a new line character when the enter key is pressed.

Exporting data is similar to running a query. You must name the columns you want, the file to store the data in, and the table to read. The syntax is:
SELECT column_names
INTO OUTFILE 'path_to_file/filename'
FROM table_name;

The command should create a tab-delimited text file (this is the default) holding the data requested. Each record from the table will be on a separate line.

In large database systems, it will become desireable to analyze performance of the database and to optimize the tables for faster data delivery to users. A DBMS may have a built-in utility to perform the optimization. Before it can be used, the command ANALYZE TABLE is given to create data for the Optimizer to use in making improvements. As you will note, you will need to use this command on each table you wish to optimize. It is not necessary to use a new command for each table: list the tables, separated by commas as you would in a FROM clause.

Another command described in the text is CHECK TABLE, which will check for errors in the files that hold your table data.

If you get error messages from the CHECK TABLE command, try to repair the error laden table with the command REPAIR TABLE.

The manual command to optimize a table is OPTIMIZE TABLE.

Two more commands can be used to examine the performance of specific queries. Placing the command EXPLAIN in front of a query, and \G at the end of the query, causes the system to generate some statistics about the table to be used in the query. Note that this command does not actually execute the query, it just points out where it might be flawed.

The PROCEDURE ANALYSE() function is added as a new last line to a query. (Note that we now switch from American spelling to British spelling. No reason is given. We might guess that this is to differentiate between ANALYZE TABLE and PROCEDURE ANALYSE.) PROCEDURE ANALYSE allows the query to run, and the analysis is run on the results.

The MySQL Query Browser is an add-on program to let you create and use queries in a GUI interface. This is, of course, a program we cannot install on our workstations in the classroom, since we are in a managed environment (no user control). You should be aware that the program exists and should know what it is for. A feature that the Query Browser would provide that is not available in from the command line is saving query output in CSV (comma separated value) format. The text discusses this as saving a resultset, which is a formal word for the output of a query.

The other topics in the chapter are not related to the focus of this class, learning the SQL language. We will leave them for another course.