Interview Questions
MySQL is an open-source relational database management system (RDBMS) based on SQL (Structured Query Language). It is developed and supported by Oracle Corporation.
MySQL supports multiple storage engines, including InnoDB (the default and most widely used), MyISAM, MEMORY (HEAP), CSV, ARCHIVE, and others. Each storage engine has its own strengths and weaknesses, making them suitable for different use cases.
MyISAM is more suitable for applications with mostly read operations, whereas InnoDB is more suitable for transactional applications due to its support for ACID (Atomicity, Consistency, Isolation, Durability) properties.
ACID stands for Atomicity, Consistency, Isolation, and Durability, which are the key properties that guarantee database transactions are processed reliably.
Optimization techniques include using indexes, avoiding SELECT *, optimizing WHERE clauses, using EXPLAIN to analyze query performance, and denormalizing the database where appropriate.
A primary key is a unique identifier for each record in a MySQL table. It ensures that each row in a table is uniquely identified and helps enforce entity integrity.
An index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional space and decreased write performance. Indexes are created on columns in tables to facilitate faster data retrieval.
A foreign key is a field or a group of fields in a table that uniquely identifies a row in another table. It establishes and enforces a link between data in two tables, ensuring referential integrity.
MySQL databases can be backed up using tools like mysqldump or by using MySQL Workbench. To restore, you can use mysql command-line utility or MySQL Workbench to import the backup file.
CHAR is a fixed-length data type, while VARCHAR is a variable-length data type. CHAR will pad spaces to the maximum length defined, whereas VARCHAR will store only the actual length of data plus one or two bytes for length information.
