DBA Stuffs

MySQL

Maximum length of a table name in MySQL:

Identifier	Maximum Length (characters)
Database	64
Table	64
Column	64
Index	64
Constraint	64
Stored Procedure or Function	64
Trigger	64
View	64
Alias	256 (see exception following table)
Compound Statement Label	16

Storage Engine Features:

Feature	MyISAM	Memory	InnoDB	Archive	NDB
Storage limits	256TB	RAM	64TB	None	384EB
Transactions	No	No	Yes	No	Yes
Locking granularity	Table	Table	Row	Table	Row
MVCC	No	No	Yes	No	No
Geospatial data type support	Yes	No	Yes	Yes	Yes
Geospatial indexing support	Yes	No	No	No	No
B-tree indexes	Yes	Yes	Yes	No	No
T-tree indexes	No	No	No	No	Yes
Hash indexes	No	Yes	No[a]	No	Yes
Full-text search indexes	Yes	No	Yes[b]	No	No
Clustered indexes	No	No	Yes	No	No
Data caches	No	N/A	Yes	No	Yes
Index caches	Yes	N/A	Yes	No	Yes
Compressed data	Yes[c]	No	Yes[d]	Yes	No
Encrypted data[e]	Yes	Yes	Yes	Yes	Yes
Cluster database support	No	No	No	No	Yes
Replication support[f]	Yes	Yes	Yes	Yes	Yes
Foreign key support	No	No	Yes	No	No
Backup / point-in-time recovery[g]	Yes	Yes	Yes	Yes	Yes
Query cache support	Yes	Yes	Yes	Yes	Yes
Update statistics for data dictionary	Yes	Yes	Yes	Yes	Yes
[a] InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature. [b] InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher. [c] Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only. [d] Compressed InnoDB tables require the InnoDB Barracuda file format. [e] Implemented in the server (via encryption functions), rather than in the storage engine. [f] Implemented in the server, rather than in the storage engine. [g] Implemented in the server, rather than in the storage engine.

What are indexes in a Database. What are the types of indexes?

Answer : Indexes are the quick references for fast data retrieval of data from a database. There are two different kinds of indexes.

Clustered Index

1. Only one per table.
2. Faster to read than non clustered as data is physically stored in index order.

Non­clustered Index

1. Can be used many times per table.
2. Quicker for insert and update operations than a clustered index.

What is the difference between Primary Key and Unique Key?

Answer : Both Primary and Unique Key is implemented for Uniqueness of the column. Primary Key creates a clustered index of column where as an Unique creates unclustered index of column. Moreover, Primary Key doesn’t allow NULL value, however Unique Key does allows one NULL value.

How many TRIGGERS are possible in MySql?

Answer : There are only six triggers are allowed to use in MySQL database and they are.

1. Before Insert
2. After Insert
3. Before Update
4. After Update
5. Before Delete
6. After Delete