Views, Indexes & File Loads
Peter Komisar © Conestoga College version 1.1

reference: MySQL Reference Manual. MySQL Structured Query
Language(SQL) ,D.Geller, R. Roselius, Danielle Waleri, DDC
Publishing, Core MySQL, Leon Atkinson, Prentice-Hall Publishing,


'OverView'

Views are virtual tables, or tables that are created
and reside only in short-term memory.  A view may
be stored in a database in the form of an SQL query.
The query is said to reference it's base tables.

The class companion manual by Geller et. al describe
the following advantages and disadvantages for
views.

View supply several advantages.


View Advantages

Security is provided by views when they are used
to limit the information that is available to users.
A view can be created that shows a user some
table information while restricting access to other
details.

While a query may be very complex, the view
of the result set created by that query may be
very simple.

While underlying data structures may undergo
modification, views can be used to continue to
provide a consistent presentation of data.

View Disadvantages

Queries that are performed against views may
result in complex joins as the query is essentially
a sub-set of the query that creates the view in
the first case.

Views may have limited privileges such as being
read-only

Following is the general form of the view supplied
by the class companion manual.

General Form of the View

CREATE VIEW viewName [ (columnList) ]
AS SELECT statement


Views In MySQL

Views at first were not supported in MySQL. Support
was added somewhere around version 4.1 Before
then it was recommended that an equivalent of views
be provided by providing read only privileges on tables.

In MySQL you can think of a view as a temporary
table you have created using a SELECT statement.
The view you create is accessible via the same
SELECT syntax you use to view tables.

Following is the MySQL Description of the View
form, differing only in supplying a CHECK option.


MySQL View Form 

CREATE VIEW viewName AS
  SELECT statement
  [WITH CHECK OPTION];


Execution

mysql> create view
-> p as select province
-> from store;
Query OK, 0 rows affected (0.09 sec)

mysql> select * from p;
+----------+
| province |
+----------+
| BC |
| BC |
| AL |
| AL |
| SK |
// middle part deleted to
// keep the note economical
| QC |
| ON |
| ON |
| ON |
| ON |
| ON |
+----------+
29 rows in set (0.05 sec)

Usually the idea behind a view is to supply some
simplification of a set of data and make it safely
available to users.

In the following example a combination of data
is provided as a view. First the view is created
with the following statement after which a select
is made on the resultant view.


Example

CREATE VIEW city_phone
AS SELECT
store_number,city,concat(area_code,"-",phone_number)
from store;
Query OK, 0 rows affected (0.00 sec)


Execution

mysql> CREATE VIEW city_phone
-> AS SELECT
-> store_number,city,concat(area_code,"-",phone_number)
-> from store;
Query OK, 0 rows affected (0.00 sec)



Selection on View // arbitrary limit, setting city's greater than "M"


mysql> select * from city_phone
-> where city >"M";
+--------------+-------------+------------------------------------+
| store_number | city | concat(area_code,"-",phone_number) |
+--------------+-------------+------------------------------------+
| 1001 | Victoria | 204-3231122 |
| 1002 | Vancouver | 204-2039485 |
| 1005 | Saskatoon | 306-1223344 |
| 1006 | Regina | 306-9987787 |
| 1007 | Winnipeg | 204-5456566 |
| 1009 | Ottawa | 613-9348888 |
| 1010 | Toronto | 416-7634567 |
| 1011 | Toronto | 416-5673300 |
| 1013 | Orangeville | 519-4343431 |
| 1014 | Moncton | 506-4400493 |
| 1017 | Sydney | 902-5484448 |
| 1019 | St. John | 709-4456281 |
| 1021 | Montreal | 450-2998858 |
| 1022 | Quebec City | 819-2292299 |
| 1024 | Owen Sound | 705-6647782 |
+--------------+-------------+------------------------------------+
19 rows in set (0.00 sec)



Dropping Views

Dropping Views is the similar to dropping tables.
In the case of views the following form is used.

Dropping View Form

DROP VIEW viewName;

The following example shows the view created
earlier being dropping. The SELECT statement
that follows indicates the view has been removed.


Example

mysql> DROP VIEW city_phone;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from city_phone;
ERROR 1146 (42S02): Table 'bizzy.city_phone' doesn't exist



Restrictions on Views

The manual describes a number of limitations on
views. For instance, queries can only return columns
listed in the view.

Updates are more restricted. In the case of Updates
where a 'from clause' contains more than one table
or the SELECT statement uses group functions
or the DISTINCT keyword, the view becomes 'read-only'.

Updates are only allowed to simple columns. Complex
columns made up of compound sets of columns are
read only.


MySQL View Limitations

MySQL also requires UPDATES on views to be simple.
A couple of these limitation from the MySQL 5.x manual
are described below.

You Cannot Use:

You will not be held accountable in our course, for remembering
the details of limitations of view. You should just be aware that
for any specific database that you may use, some limitations on
the use of views may exist. If you are using views extensively,
you will need to know those limitations.


Indexes


MySQL Quote

The following quote from the MySQL

"Indexes are used to find rows with specific column
values quickly. Without an index, MySQL must begin
with the first row and then read through the entire table
to find the relevant rows. The larger the table, the more
this costs.

If the table has an index for the columns in question,
MySQL can quickly determine the position to seek to
in the middle of the data file without having to look at
all the data. If a table has 1,000 rows, this is at least
100 times faster than reading sequentially. If you need
to access most of the rows, it is faster to read sequentially,
because this minimizes disk seeks."

- MySQL 5.0 Reference Manual


About Indexes

The SQL standard does not require the use of indexes.
Indexing is supported by most database vendors as
they generally improve the performance of a database.
Some vendors automatically create indexes on primary
keys.

Functionally, A simple ordered key value is added
to each row which the database can reference very
quickly and easily.

Indexes are optional. Further they are not visible
to the database user. They are utilized 'behind the
scenes'. Indexes may be created on single or multiple
rows. Sometimes indexes are used to enforce
uniqueness of data.

Applying unique indexes implicitly apply the UNIQUE
constraint without it having to be explicitly stated for
the row.


Downside of Using Indexes

The MySQL quote above showed that for selects,
using indexes can improve performance greatly.
This is significant, as a majority of time data is
being drawn from a table.

The downside is that INSERTS UPDATES and
DELETES have overhead added, as the system
must scan and perhaps adjust a number if indexes.

Good Practice // from the Class Manual

It might be good to drop any indexes that are
on a table before bulk loading data, adding them
back after the load is complete.

A Note on Database Optimization

Database engines optimize each query before
execution. Optimizations are based on a set of
algorithms that take advantage of statistics, both
real and estimated. Oracle for instance has an
ANALYZE command that generates statistics on
a set of data, storing that information in a data
dictionary.

The MySQL Manual describes a number of
optimizations that are used in it's database
engine.

Suffice it to say, each database system, will use
a lot of mathematical techniques to diminish the
impact that large-sized table sets will have on
finding data.


Indexes are related to storage engines in MySQL.
Following is a brief survey the internal workings of
MySQL.

A Look Inside MySQL
// info from the MySQL 5.0 Manual

MySQL storage engines program objects that are
associated with a set of files that determine the
upper limit for storage size.

Each of the default, MyISAM tables is stored on disk
in three files. The files have names that begin with the
table name and have an extension to indicate the file
type.

MyISAM Associated Files

// MySQL's Default Table Type

MySQL supports several storage engines that serve
as 'handlers' for different table types. MySQL storage
engines include both those that handle transaction-safe
tables and those that handle non-transaction-safe tables:

Other Storage Engine Types Supported in MySQL:


Why the tangential tour of MySQL storage engines?
To make sense of the following statement quoted from
the MySQL manual.

"The maximum number of indexes per table and the maximum
index length is defined per storage engine. . . All storage engines
support at least 16 indexes per table and a total index length of
at least 256 bytes. Most storage engines have higher limits."


Using Column Indexes on MySQL

The MySQL manual states that the use of indexes on the
relevant columns is the best way to improve the performance
of SELECT operations. Since all MySQL data types can be
indexed, any column may be indexed.

Single Column Indexes

In the following example table constraint syntax is used
to create an index on the last_name column.


Example

CREATE TABLE associates (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name)
);


Execution

mysql> CREATE TABLE associates (
-> id INT NOT NULL,
-> last_name CHAR(30) NOT NULL,
-> first_name CHAR(30) NOT NULL,
-> PRIMARY KEY (id),
-> INDEX name (last_name)
-> );
Query OK, 0 rows affected (0.09 sec)


Multiple Column Indexes

MySQL can create composite indexes. Composite
columns are indexes on multiple columns. MySQL
allows up to 15 columns to be part of a composite
index. Following is an example of an index based
on two columns. In the execution the table, is dropped
so the new index can be applied.


Example
// adapted from the MySQL 5.0 Manual

CREATE TABLE associates (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name, first_name)
 );


Execution

mysql> drop table associates;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE associates (
-> id INT NOT NULL,
-> last_name CHAR(30) NOT NULL,
-> first_name CHAR(30) NOT NULL,
-> PRIMARY KEY (id),
-> INDEX name (last_name, first_name)
-> );
Query OK, 0 rows affected (0.05 sec)


How Indexes on Multiple Columns are Used

The following examples are from the MySQL
Manual where compound indexing is used by
the search engine. In the first example, the
compound index is referenced because it
is the last name or second column that is
referenced. All the rest use the AND statement
and involve the multiple index.




Examples Where Multiple Indexing is Used

SELECT * FROM test WHERE last_name='Widenius';
SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';
SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');
SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

The composite name index is not used in the
following. In the first case, because the first of
the two columns is referenced. In the second
case it is because the two columns are referenced
in an OR statement where the first name column is
included.

Examples Where Multiple Indexing is Not Used 

SELECT * FROM test WHERE first_name='Michael';
SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';


Creating Indexes And Removing Indexes

Indexes may be created using a CREATE statement.


Index Creation Form

CREATE [UNIQUE] INDEX indexName
ON tableName (columnName [ ASC | DESC ] [, . . . ] );

Similarly, an index may be dropped using the following
form.

Dropping Index Form

DROP INDEX indexName ON tableName;

To example this, we will use the DROP INDEX form
to remove the composite index that was applied to
the 'associates' table.

Example

DROP INDEX name ON associates;

// DROP form shown in the manual is incomplete


Execution

mysql> DROP INDEX name ON associates;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0


Now the INDEX command is used to add an
index on a single column.


Example

CREATE INDEX surname ON associates
(last_name );


Execution

mysql> CREATE INDEX surname ON associates
-> (last_name );
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0


Col_Name( N) Syntax

Some data types allowing making an index out
of the first part, or prefix of the column values.
The syntax used in this case is as follows

Form for Using a Part of the Column Value as an Index

columnName( N )

where N represents the number of characters of
a string column that are used.

Using a partial value keeps the index file much
smaller. With the BLOB and TEXT types in fact
a prefix length must be used as is shown in the
following example.


Example // from the MySQL 5.0 Manual

CREATE TABLE test 
(blob_col BLOB, INDEX(blob_col(10)));

Execution 

mysql> CREATE TABLE test
    -> (blob_col BLOB, INDEX(blob_col(10)));
Query OK, 0 rows affected (0.06 sec)

In the above example the first 10 characters of
the data type are used as part of the index. 

Prefix Lengths

Prefixes in MySQL can be up to 1000 bytes long
in the default MYISAM table and 767 bytes for
InnoDB tables. This prefix limit is measured in
bytes. In the CREATE TABLE statements the N
is in characters. This needs to be considered as
different character encodings may use two or
more bytes per character.

FullText Indexes

FULLTEXT indexes may also be created. These are
used for full-text searches. In MySQL only the MyISAM
storage engine supports FULLTEXT indexes.

Further FULLTEXT indexes can only be applied to
CHAR, VARCHAR and TEXT column types. It follows
that in FULLTEXT indexing prefix indexing is not
supported.

Bulk Data Loads


It is often necessary to load a bulk of data that has
been stored in file form to a table. Most databases
use a similar syntax for loading data from a file.
Loading data by this method allows rows to be
read into a table at very high speeds.

Typical there is a function or a switch which takes
care of reversing the process, backing up the data
from a table into a file.

Following is the MySQL syntax for the load function
with all it's options. This function loads data from a
file into a database table.


LOAD DATA INFILE Form

LOAD DATA
[LOW_PRIORITY | CONCURRENT]
[LOCAL]
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[FIELDS [TERMINATED BY 'string']
[ [OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char'] ]
[ LINES [STARTING BY 'string']
[TERMINATED BY 'string'] ]
[ IGNORE number LINES ]
[ (col_name_or_user_var,...) ]
[SET col_name = expr,...]

// The filename must be given as a literal string.

Just to bring the command down to earth, following
is an example from the MySQL 5.0 Manual.


Essential LOAD DATA INFILE Example 

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
We need a file. Following is a file that adds two stores
to or store table. The key aspects to the file is that string
types are quoted and all data items are tab separated.


File Contents Saved as 'DataAdd' under the C:\. 

1031    8665    '99 Maple Ave.' 'Markham'       'ON'    'T25G3E'        '905'   '2294545'       5.0     7.0     0.0
1032    8668    '3987 Rayburn St.'      'Saskatoon'     'SK'    'S4K2T1'        '413'   '9987654'       5.0     7.0     0.0

To load this file the LOAD Command takes the 
following form. 

Example 

LOAD DATA INFILE 'C:\\AddStore.txt' INTO TABLE store;

"Backslash is the MySQL escape character within strings, so to
write FIELDS ESCAPED BY '\\', you must specify two backslashes
for the value to be interpreted as a single backslash." - MySQL Manual

This seems to apply even in specifying the file as a string
literal.

Execution

mysql> LOAD DATA INFILE 'C:\\AddStore.txt' INTO TABLE store;
Query OK, 1 row affected (0.01 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0


The LOCAL Keyword

The LOCAL keyword refers to where the file will
be loaded from relative to the client and server
machines. Because for academic and development
purposes, client and server and often on the same
machine the LOCAL keyword does not seem to
have an effect. If the client and the server were
separated which is the normal case, the effects
would be much more pronounced.


LOCAL Keyword Specified

If the LOCAL keyword is specified the file is read  by the
"client program on the client host and sent to the server."
The file can be given as a full pathname to the exact location.
If a relative path is given, it is interpreted as relative to the
directory in which the client program is started. On this
machine the path to MySQL's bin is as follows.


LOCAL Keyword Not Specified

If the LOCAL keyword is not specified then "the file must
be located on the server host and read directly by the
server." On the server side, a supplied absolute path is
read as given. A relative path is interpreted relative to the
server's data directory. If a plain file name is supplied,
with no path part, the server looks in the database directory
of the default database. - info from MySQL Manual


Example Path to Program Location on a Client

C:\Program Files\MySQL\MySQL Server 5.0\bin

If we wanted to reference a file by a relative path, we would
need to go up three directories to get to C:\. We also have
to remember to escape the backslash.


Example and Execution

mysql> LOAD DATA LOCAL INFILE
-> '..\\..\\..\\..\\AddStore'
-> INTO TABLE bizzy.store;
Query OK, 2 rows affected, 22 warnings (0.06 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 21

// the warnings would need to be checked!

The local keyword must be the implicit default as the same
command works without specifying 

Loading Default Values

It would seem that MySQL doesn't load default
values correctly from a load file. Apparently the
expected behavior is, where there is a null 
value is represented to column that is marked
default, the column is suppose to adopt the 
default value. Apparently MySQL does not yet
do this, but plans to fix this in future editions.


Other System LOAD Utilities

Many systems such as Oracle supply stand-alone
utilities that implement the LOAD function.

Following is a LOAD Function example for Oracle.


Oracle Simplified LOAD Form
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-load.html

LOAD DATA
INFILE <dataFile>
APPEND INTO TABLE <tableName>
FIELDS TERMINATED BY '<separator>'
(<list of all attribute names to load>)


Oracle Load Example
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-load.html

LOAD DATA
INFILE test.dat
INTO TABLE test
FIELDS TERMINATED BY '|'
(i, s)


Loading Data To File From a Table

The opposite function where data is unloaded from
a table and is stored in a file has obvious utility. 

SELECT . . .INTO OUTFILE 

MySQL SELECT ... INTO OUTFILE is the complement
function of LOAD DATA INFILE.  It uses the features
of a SELECT to enable part or all of a table's data
to be stored to file. 

Following everything in the store table is saved to
a StoreSave.txt file under C:\. 

Example Execution

mysql> SELECT * INTO OUTFILE 'c:\\StoreSave.txt' FROM STORE;
Query OK, 34 rows affected (0.14 sec)

Following is a little bit of the output file.

Truncated Output File

1001    1893    1111 Rosemary Blvd.     Victoria        BC      B1C9W2  204     3231122 5.0     7.0     \N
1002    4930    579a Delaware St.       Vancouver       BC      B2C8T1  204     2039485 5.0     7.0     \N
1003    2309    2 Shepard Rd.   Edmonton        AL      A2BW2T  780     3345555 5.0     0.0     \N

// truncated output file

Notice that the null value is escaped as '\N'. 


Different Variations on the Storage File Form

The following example shows that details of the 
storage file can be dictated by clauses in the 
SELECT INTO statement. 

MySQL Example

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

We can borrow some of these options and apply 
them to our earlier statement, this time writing to 
a second store file.

Example and Execution

mysql> SELECT * INTO OUTFILE 'c:\\StoreSave2.txt'
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    ->   LINES TERMINATED BY '\n'
    -> FROM STORE;
Query OK, 34 rows affected (0.00 sec)

Following is part of the first part of the output 
file. Note quotes have been added to string fields
and comma is used as the delimiter. 

Truncated OUTPUT

1001,1893,"1111 Rosemary Blvd.","Victoria","BC","B1C9W2","204","3231122",5.0,7.0,\N
1002,4930,"579a Delaware St.","Vancouver","BC","B2C8T1","204","2039485",5.0,7.0,\N
1003,2309,"2 Shepard Rd.","Edmonton","AL","A2BW2T","780","3345555",5.0,0.0,\N
1004,1234,"555 Queen St.","Calgary","AL","A6NN3W","403","9192834",5.0,0.0,\N
1005,9991,"33 WheatField Ave.","Saskatoon","SK","S3S9Q2","306","1223344",5.0,6.0,\N
// etc.  


Oracle's UNLOAD Function

Oracle also has an Load to Out File function 
in the UNLOAD command that has the following 
form.

Oracle UNLOAD Command Form

UNLOAD TO 'fileName' DELIMITER 'char'
SELECT (columnList) FROM tableName



Reading Session


Read or even glance through the contents of our
collection of notes we have posted to date. This
should provide a sense of for the volume of material
we have covered so far.

If you haven't done so get up on your assignments!

Optional // very modest bonus point!

One would expect that an output file generated
by MySQLs SELECT TO FILE command would
generate a file that would be of a form that could
be reloaded via the LOAD INFILE command.

For the database in question, by unloading to
a file then reloading this data to a new table
we can confirm that the process is functional
in both directions.

We may also discover required syntax for
the IN FILE. ( For example do string need
to be in quotes. Can we specify a delimiter
or must be it be a tab.)

A brief summary of the features needs to be
taken in the IN FILE can be deduced.

Alternatively, you can find this info on the net
and submit a short paragraph summary, including
the article's title, author and web site for the source.


Optional

We have a good number of students who are following
the course using Microsoft's SQL server. Meanwhile
we have adopted MySQL as our practical exampling
tool. These students cannot help but being familiar with
both systems. Following is a link which describes migration
from Microsoft's database system to MySQL. It serves
as an interesting summary of some of the differences
between the two systems. It's information would also be
useful for a reverse migration, from MySQL to MSSQL!