Home / Summaries / MySQL 5.0 Certification Study Guide / table-index-engine

Tables and Indexes

Q: What are the 2 types of tables that exist in MySQL?

Base table : a table that contains data View : a virtual table

44 important questions on Tables and Indexes

What are the 2 types of tables that exist in MySQL?

Base table: a table that contains data
View: a virtual table

What is stored in a format file in a database directory?

The definition, or structure, of a table

What is the suffix of a format file?

.frm

What is the suffix of a MyISAM data file?

.MYD

What is the suffix of a MyISAM index file?

.MYI

Which database engine stores table data of multiple tables in the same file?

InnoDB (has a shared tablespace)

What database engine does not store database information on disk?

MEMORY engine

What is the maximum amount of tables for the InnoDB engine?

2 billion

Name 3 external factors that may influence the table limit of MySQL?

1. Filesystem max subdirectories
2. Filesystem load time with lots of subdirectories (practical limit)
3. Disk space available

Name 3 methods to overcome the table size limits

1. Use MERGE tables
2. Convert to storage engine with greater limits
3. Change the operating system

What are the 3 forms of creating a table supported by MySQL?

1. Create an empty table from a new definition or definition of an existing
table
2. Create a table populated from a SELECT statement
3. Create a temporary table

What is the format of the CREATE TABLE command?

CREATE TABLE table_name (column_definitions[, index definitions]) [storage engine]

What 3 storage engines are always available?

MyISAM, MERGE and MEMORY

What command displays the available storage engines?

SHOW ENGINES;

How do you change a tables engine to MyISAM?

ALTER TABLE `world` ENGINE = MyISAM;

What system variable defines the default storage engine?

storage_engine

What is the default built-in storage engine of MySQL?

MyISAM

Name 3 ways to change the default storage engine

1. --default-storage-engine=MyISAM at startup
2. SET GLOBAL storage_engine = MyISAM
3. SET [SESSION] storage_engine = MyISAM

What command displays the warnings issued?

SHOW WARNINGS;

Is the AUTO_INCREMENT option copied with a CREATE TABLE ... SELECT statement?

Is the storage engine copied with a CREATE TABLE ... SELECT statement?

Is the default CHARSET copied with a CREATE TABLE...SELECT statement?

Yes

What 2 item types are not copied with a CREATE TABLE...LIKE statement?

1. DATA DIRECTORY and INDEX DIRECTORY
2. Foreign key definitions

Name 4 properties of a TEMPORARY table?

1. It's visible only to the client who created it
2. It exists only for the duration of the connection of the client who created it
3. It may have the same name as a temporary table
4. It can be renamed only with ALTER TABLE

Does the table contents of a MEMORY table persist between server restarts?

Name 4 functions of the ALTER TABLE statement

1. Adding and dropping columns
2. Changing the name or definition of a column
3. Adding or dropping indexes
4. Renaming the table

Name 2 keywords for specifying the position of a column

FIRST and AFTER `colname`

Are column names case sensitive in MySQL?

What 2 keywords can change a column definition?

1. ALTER TABLE `table` MODIFY `column_name` TYPE
2. ALTER TABLE `table` CHANGE `old_colname` `new_colname` TYPE

What is the statement to rename a table?

ALTER TABLE t1 RENAME TO t2; or RENAME TABLE t1 TO t2;

What does not work with TEMPORARY tables RENAME TABLE or ALTER TABLE?

RENAME TABLE

How do you swap the names of two tables in one command?

RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2;

Can the DROP TABLE command drop multiple tables in one command?

Yes, DROP TABLE t1, t2, t3;

What 2 statements delete all contents from a table?

DELETE FROM t;
TRUNCATE TABLE t;

Name 2 reasons for applying indexes on a table?

1. Performance
2. Unique constraint on a column

What are the 3 general index types in MySQL?

1. Primary key, does not allow null
2. Unique, can allows null
3. Non-unique index, allows anything

Does a UNIQUE index allow multiple null values if the column allows it?

Yes

What is the syntax of the CREATE INDEX command?

CREATE INDEX NameIndex ON `tablename` (col1, col2);

What is the practical difference between ALTER TABLE and CREATE INDEX?

1. ALTER TABLE supports multiple indexes
2. ALTER TABLE supports PRIMARY KEY

What storage engine supports index algorithm selection?

MEMORY

What is the default index algorithm for MEMORY tables?

Hash

Under what circumstances is a BTREE index better than HASH?

When the index contains a lot of non-unique values

What is the syntax of the DROP INDEX statement?

DROP INDEX `NameIndex` ON `table_name`;

Name a synonym for DESCRIBE `table_name`?

SHOW COLUMNS/FIELDS FROM `table_name`;

The question on the page originate from the summary of the following study material:

MySQL 5.0 Certification Study Guide | 9780672332708 | Paul DuBois, et al

View summary

A unique study and practice tool
Never study anything twice again
Get the grades you hope for
100% sure, 100% understanding

Remember faster, study better. Scientifically proven.