IST366: Indexing, Optimization, Backup, and Restore.

Indexing, Optimization, Backup, and Restore

Indexing

Indexes are used to find rows with specific value of one column fast. Without an index MySQL has to start with the first record and then read through the whole table until it finds the relevant rows. All MySQL indexes are stored in B-trees. Indexes are used to

quickly find the rows that match at WHERE clause;

retrieve rows from other tables when performing joins;

find MAX() or MIN() value for a specified column;

sort or group a table if sorting or grouping is done by an index;

in some cases a query can be optimized to retrieve values without consulting the data file.

There are two ways to index a table:

create an index creating a new table

create an index for already existing table.

In the second case we need to use CREATE INDEX statement. The general form of this statement is:

CREATE [FULLTEXT|UNIQUE] INDEX index_name
    ON table_name (column_name[, column_name ...]);

Thus, the following query creates a new index name_ind for the table employee:

CREATE INDEX name_ind
    ON employee (name);

This query creates a unique index for the field phone (which prevents from having records with the same phone):

CREATE UNIQUE INDEX phone_ind
    ON employee (phone);

We can also create an index for several fields at once:

CREATE UNIQUE INDEX auth_title_ind
    ON book (author, title);

Now we can not have different records with the same combination (author, title). However, this multicolumn index can not be used in query like this:

SELECT * 
  FROM book
 WHERE title = 'Gone with the wind';

because we are using only the second part of the index. This index will be used in the following queries:

CREATE *
  FROM book
 WHERE author = 'Jack London';
CREATE *
  FROM book
 WHERE author = 'Margaret Mitchell' AND title = 'Gone with the wind';

Note that BLOB columns can be indexed only partially, that is you must index a prefix of the column:

CREATE INDEX syllabus_ind 
    ON course (syllabus(64));

This query indexes only the first 64 characters of syllabus. However, in MySQL version 3.23 or later, you can use keyword FULLTEXT which is applicable to TEXT or VARCHAR attributes only. They are used for full-text search.

If we want to create an index when we create a table we can use the following key words after the description of attributes:

CREATE TABLE table_name (
  column_name column_type,
  ...
  column_name column_type,
  PRIMARY KEY (column_name,...),
  KEY [index_name] (column_name,...),
  INDEX [index_name] (column_name,...),
  UNIQUE [INDEX] [index_name] (column_name,...),
  FULLTEXT [INDEX] [index_name] (column_name,...)
);

Please note that PRIMARY KEY and UNIQUE fields are automatically indexed. You can also add multicoulmn indexes and full text search indexes here.
Remember index only those fields that you are using in your SELECT statements.

To remove an index from a table you can use either ALTER TABLE statement or

DROP INDEX index_name
  ON table_name;

statement.

Optimizing tables

Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. After many DELETE/INSERT queries you can have a very fragmented data. You can also get a fragmented table if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). To defragment the table and increase the speed of SELECT queries on that table you can use OPTIMIZE statement on the table. General form for this statement is:

OPTIMIZE TABLE table_name [, table_name, ...];

General optimization tips:

Always check that all your queries really use the indexes you have created in the tables.
For tables that changes a lot you should try to avoid all VARCHAR or BLOB columns. You will get dynamics row length as soon as you are using a single VARCHAR or BLOB column.
It's not normally useful to split a table into different tables just because the rows gets 'big'. To access a row, the biggest performance hit is the disk seek to find the first byte of the row. After finding the data most new disks can read the whole row fast enough for most applications. The only cases where it really matters to split up a table if it's a dynamic row size table that you can change to a fixed row size, or if you very often need to scan the table and don't need most of the columns.
Take advantage of the fact that columns have default values. Insert values explicitly only when the value to be inserted differs from the default. This reduces the parsing that MySQL need to do and improves the insert speed.
Normally you should try to keep all data non-redundant (what is called 3rd normal form in database theory), but you should not be afraid of duplicating things or creating summary tables if you need these to gain more speed.
Use INSERT /*! DELAYED */ when you do not need to know when your data is written. This speeds things up because many records can be written with a single disk write.
Use INSERT /*! LOW_PRIORITY */ when you want your selects to be more important.
Use the multi-line INSERT statement to store many rows with one SQL command.
Use LOAD DATA INFILE to load bigger amounts of data. This is faster than normal inserts.
Use AUTO_INCREMENT columns to make unique values.
Use OPTIMIZE TABLE once in a while to avoid fragmentation when using dynamic table format.
When using a normal Web server setup, images should be stored as files. That is, store only a file reference in the database. The main reason for this is that a normal Web server is much better at caching files than database contents. So it it's much easier to get a fast system if you are using files.
Columns with identical information in different tables should be declared identical and have identical names. Before Version 3.23 you got slow joins otherwise. Try to keep the names simple (use name instead of customer_name in the customer table). To make your names portable to other SQL servers you should keep them shorter than 18 characters.
In many cases it's faster to access data from a database (using a live connection) than accessing a text file, just because the database is likely to be more compact than the text file (if you are using numerical data), and this will involve fewer disk accesses. You will also save code because you don't have to parse your text files to find line and column boundaries.

Backup and Restore

It's now a bad idea to backup you data. You can use mysqldump tool from MySQL package for that (see docs for details) or you can use SQL statements BACKUP and RESTORE:

BACKUP TABLE table_name [, table_name, ...] TO '/path/to/backup/directory';

and

RESTORE TABLE table_name [, table_name, ...] FROM '/path/to/backup/directory';

Of course, the user under which MySLQ server is running should have read/write permissions to that directory (OS user) and the user who is running this query should have FILE and RELOAD privileges (MySQL user).