Database Management System, Slides of Database Management Systems (DBMS)

Download Database Management System and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Managing Data

in MySQL

Altering Tables

Creating tables.

• Using Partitioned Tables
  • Partitioning is similar in concept to the MERGE storage engine in the sense that it enables use of table contents that are stored in different locations. However, a partitioned table is a single table, not a logical construct that maps onto multiple underlying tables. Also,a partitioned table can use storage engines other than MyISAM, whereas MERGE tables require the use of MyISAM tables.
  • By sectioning table storage,partitioned tables offer benefits such as these:
    • Table storage can be distributed over multiple devices,which may improve access time by virtue of I/O parallelism.
    • The optimizer may be able to localize searches to specific partitions, or to search partitions in parallel.

Altering Tables

Creating tables.

• Using Partitioned Tables
  • Partitioning functions assign rows based on ranges or lists of values or hash values:
    • Use range partitioning when rows contain a domain of values such as dates, income level, or weight that can be divided into discrete ranges.
    • Use list partitioning when it makes sense to specify an explicit list of values for each partition, such as sets of postal codes, phone number prefixes, or IDs for entities that you group by geographical region.
    • Use hash partitioning to distribute the rows among partitions according to hash values computed from row keys. You can either supply the hash function yourself or tell MySQL which columns to use and it will compute values based on those columns using a built-in hash function.
  • The partitioning function must be deterministic so that the same input consistently results in row assignment to the same partition. This rules out the use of functions such as RAND() or NOW().

Altering Tables

Creating tables.

• Using Partitioned Tables
  • Sample usage CREATE TABLE log_partition ( dt DATETIME NOT NULL, info VARCHAR(100) NOT NULL, INDEX (dt) ) PARTITION BY RANGE(YEAR(dt)) ( PARTITION p0 VALUES LESS THAN (2005), PARTITION p1 VALUES LESS THAN (2006), PARTITION p2 VALUES LESS THAN (2007), PARTITION p3 VALUES LESS THAN (2008), PARTITION p4 VALUES LESS THAN MAXVALUE ); ALTER TABLE log_partition REORGANIZE PARTITION p INTO ( PARTITION p4 VALUES LESS THAN (2009), PARTITION p5 VALUES LESS THAN MAXVALUE );

Altering Tables

Dropping Tables.

• To drop a table only if it is a temporary table,include the TEMPORARY

keyword:

DROP TEMPORARY TABLE tbl_name

Altering Tables

Indexing Tables

• Indexes are the primary means of speeding up access to the contents of your tables, particularly for queries that involve joins on multiple tables
• Storage Engine Index Characteristics
  • MySQL provides quite a bit of flexibility in the way you can construct indexes:
    • You can index single columns or construct composite indexes that include multiple columns.
    • An index can be constrained to contain only unique values or allowed to contain duplicate values.
    • You can have more than one index on a table to help optimize different queries on the table that are based on different columns.
    • For string data types other than ENUM or SET ,you can elect to index a prefix of a column; that is,only the leftmost n characters,or n bytes for binary string types.(For BLOB and TEXT columns,you can set up an index only if you specify a prefix length.) If the column is mostly unique within the prefix length,you usually won’t sacrifice performance,and may well improve it:Indexing a column prefix rather than the entire column can make an index much smaller and faster to access.

Altering Tables

Indexing Tables

• Creating Indexes
  • A unique index. This disallows duplicate values. For a single-column index, this ensures that the column contains no duplicate values. For a multiple-column (composite) index, it ensures that no combination of values in the columns is duplicated among the rows of the table.
  • A regular (non-unique) index. This gives you indexing benefits but allows duplicates.
  • A FULLTEXT index, used for performing full-text searches. This index type is supported only for MyISAM tables
  • A SPATIAL index. These can be used only with MyISAM tables for the spatial data types(For other storage engines that support spatial data types, you can create non- SPATIAL indexes.)
  • A HASH index. This is the default index type for MEMORY tables, although you can override the default to create BTREE indexes instead.

Altering Tables

Indexing Tables

• Creating Indexes
  • You can create indexes for a new table when you use CREATE TABLE
  • To add indexes to existing tables, use ALTER TABLE or CREATE INDEX .(MySQL maps CREATE INDEX statements onto ALTER TABLE operations internally.) - ALTER TABLE is more versatile than CREATE INDEX because it can create any kind of index supported by MySQL. ALTER TABLE tbl_name ADD INDEX index_name ( index_columns ); ALTER TABLE tbl_name ADD UNIQUE index_name ( index_columns ); ALTER TABLE tbl_name ADD PRIMARY KEY ( index_columns ); ALTER TABLE tbl_name ADD FULLTEXT index_name ( index_columns ); ALTER TABLE tbl_name ADD SPATIAL index_name ( index_columns );

Altering Tables

Indexing Tables

• Creating Indexes
  • CREATE INDEX can add most types of indexes,with the exception of a PRIMARY KEY :
    • CREATE INDEX index_name ON tbl_name ( index_columns );
    • CREATE UNIQUE INDEX index_name ON tbl_name ( index_columns );
    • CREATE FULLTEXT INDEX index_name ON tbl_name ( index_columns );
    • CREATE SPATIAL INDEX index_name ON tbl_name ( index_columns );
  • tbl_name , index_name ,and index_columns have the same meaning as for ALTER TABLE .Unlike ALTER TABLE ,the index name is not optional with CREATE INDEX ,and you cannot create multiple indexes with a single statement.

Altering Tables

Indexing Tables

• Creating Indexes
  • To create indexes for a new table when you issue a CREATE TABLE statement, the syntax is similar to that used for ALTER TABLE ,but you specify the index-creation clauses in addition to the column definitions: CREATE TABLE tbl_name ( ... column definitions ... INDEX index_name ( index_columns ), UNIQUE index_name ( index_columns ), PRIMARY KEY ( index_columns ), FULLTEXT index_name ( index_columns ), SPATIAL index_name ( index_columns ), ... );

Altering Tables

Indexing Tables

• Creating Indexes
  • In some circumstances,you may find it not only desirable but

necessary to index a column prefix rather than the entire column:

• Prefixes are required for indexing BLOB or TEXT columns.
• The length of index rows is equal to the sum of the length of the index parts of the columns that make up the index

Altering Tables

Indexing Tables

• Creating Indexes
  • Columns in FULLTEXT indexes are indexed in full and do not have prefixes. If you specify a prefix length for a column in a FULLTEXT index, MySQL ignores it.
  • Columns with spatial data types such as POINT or GEOMETRY can be indexed as follows: - SPATIAL indexes can be used only for MyISAM tables,and only for columns that are NOT NULL .The columns are indexed in full. - Other index types ( INDEX , UNIQUE , PRIMARY KEY ) can be used with any storage engine other than ARCHIVE that supports spatial data types.Columns can be NULL unless part of a PRIMARY KEY .A prefix length in bytes must be specified for each spatial column in the index except POINT columns.

Altering Tables

Altering Table Structure

• ALTER TABLE is useful when you find that the structure of a table no

longer reflects what you want to do with it.

• Reason for altering table:
  • You might want to use the table to record additional information.
  • Perhaps the table contains information that has become superfluous.
  • Maybe existing columns are too small,
  • defined columns larger than you need and you’d like to make them smaller to save space and improve query performance.

Altering Tables

Altering Table Structure

• The syntax for ALTER TABLE looks like this:

ALTER TABLE tbl_name action [, action ] ... ;