This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Which are the key advantages of InnoDB?
InnoDB is a high-reliability and high-performance storage engine for MySQL.
Its design follows the ACID model, with transaction features like to commit, rollback.
Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance.
InnoDB tables, arrange your data on disk to optimize common queries based on primary keys. Each InnoDB table has a primary key index called the clustered index.
You can freely mix InnoDB tables with tables from other MySQL storage engines, even within the same statement. For example, you can use a join operation to combine data from InnoDB and MEMORY tables in a single query.
What is MyISAM table and explain storage structure for it?
This is one of Table Stroage Engine.
All data values are stored with the low byte first. This makes the data machine and operating system independent.
Each MyISAM table 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. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.
How many indexes you can create on MyISAM table?
The maximum number of indexes per MyISAM table is 64.
Is MVCC supported by MyISAM?
No:
Is ENUM supported by MySQL?
Yes, you can use for predefine values during table creation.
How to get current MySQL version?
SELECT VERSION ();
What is the difference between BLOB , CLOB and TEXT?
TEXT is a data-type of text based input. On the other hand, you have BLOB and CLOB, which are more suitable for data storage (images, etc.) due to their larger capacity limits (4GB for example).
TEXT and CHAR will convert to/from the character set they have associated with time. BLOB and CLOB simply store bytes.
As for the difference between BLOB and CLOB, I believe CLOB has a character encoding associated with it, which implies it can be suited well for very large amounts of text.
What is table_cache and max_connections in MySQL?
It is about to Opens and Closes Tables by MySQL.
MySQL is multi-threaded, so there are different connection which are accessing the same table simultaneously. MySQL open this table into cache for all different connections.
table_cache is a system variable and this is number of open tables for all threads. This can be changed base on the requirement.
max_connection is a system variable and this is the maximum permitted number of simultaneous client connections.
