MySQL Q&A

MySQL Storage Engines

There are many but following three are commonly used:

1. MyISAM
    MyISAM is the default storage engine for MySQL

2. InnoDB
    InnoDB is a transaction safe (ACID compliant) storage engine for MySQL that has commit, rollback
    and crash recover capabilities to protect user data

3. Memory
    Memory storage engine creates tables with contents that are stored in memory. Memory/Heap tables are used for high speed temporary storage. Only Comparison operators supported (=, <=>). TEXT or BLOB fields are not supported. Auto Increment fields also not supported
   
Comparison 
Features                                MyISAM                   InnoDB                  Memory
Storage Limits                            256TB                         64TB                      RAM
Transaction                                 No                               Yes                         No
Locking                                      Table                            Row                       Table
Full Text Search Indexes             Yes                               No                         No
Clustered Indexes                        No                              Yes                         No                   
Replication Support                     Yes                              Yes                        Yes
Foreign Key                                No                               Yes                        No

What is the difference between MyISAM static and MyISAM dynamic?
MyISAM static tables fields are fixed length. MyISAM dynamic tables can have variable length fields
such as Text, BLOB etc

Advantages of MyISAM over InnoDB
 MyISAM tables are stored on disk which can therefore be compressed so it can accommodate a very large set of data 


Advantages of InnoDB over MyISAM
Transaction support, row level locking, foreign key constraints, crash recovery

What is Serial Data Type in MySQL?
BIGINT NOT NULL PRIMARY KEY AUTO INCREMENT

What are Federated Tables?
They are like linked tables in SQL Server which allows access to tables on different server

What is Candidate Key?
Any table that uniquely identifies a row in a table is candidate key for that table. We select one of the candidate keys as primary key

Difference between Primary Key and Unique Key
Primary Key
1. Uniquely identifies a column
2. Cannot be NULL
3. There can only be one primary key in a table

Unique Key
1. Uniquely identifies a column
2. Can be NULL
3, There can be more than unique keys

What is the difference between Drop Table and Truncate Table?
Drop table deletes table including data, structure, attributes & indexes
Truncate table deletes only the data

Performance Tuning Best Practices for MySQL

MySql - InnoDB vs MyISAM

Most people who use MySQL know that MyISAM and InnoDB are the two most-common database engines available with the popular open-source database provider.

Storage Engine

The storage-engine is what will store, handle, and retrieve information for a particular table. As hinted by the opening of this article, there is no be-all end-all solution to every or even most cases. Each has very specific pros and cons that by design can not be all-inclusive of each other.

What is a full text search in mysql?

According to the MySQL manual, Full-text is a “natural language search”; it indexes words that appear to represent the row, using the columns you specified

MySQL has had FULLTEXT searching in one form or another since version 3.23.23. FULLTEXT indices in MySQL allow database administrators and programmers to designate any character-based field (CHAR, VARCHAR, or TEXT) as a FULLTEXT index, which allows for complex text searching against data stored in those fields.