Top MySQL interview questions and answers in 2023

MySQL does not care about the case. Its case sensitivity is determined by the underlying operating system, as the case sensitivity of table names and databases is determined by the OS. Database and table names are not case sensitive in Windows, but they are case sensitive in UNIX. The database accepts both upper and lower case table names, especially on UNIX hosts.

This is one of the most searched MySQL query interview questions. Here are some guidelines for writing an optimized query in MySQL:

• Predicates should not contain functions
• Do not use wildcard symbols such as percent at the start of predicates
• In the SELECT clause, only include columns that are required
• Use the inner join option at all times
• When assuming sorted results, SQL requires the use of the "Order by" clause

These specific MySQL interview questions and answers for experienced candidates can be helpful. MyISAM and InnoDB are the most commonly used storage engines in MySQL.

The following are the distinctions between MyISAM and InnoDB:

• Transactions are no longer supported by MyISAM, but they are supported by InnoDB
• MyISAM facilitates table-level locking, whereas InnoDB facilitates row-level locking
• Full-text search is aided by MyISAM, but not by InnoDB
• MyISAM was designed for speed, whereas InnoDB was designed for maximum performance
• MyISAM no longer supports foreign keys, but InnoDB does
• With InnoDB, we can use commit and rollback, but not with MyISAM
• MyISAM no longer supports ACID (Atomicity, Consistency, Isolation, and Durability), whereas InnoDB does
• The AUTO INCREMENT field is a section of the index in the InnoDB table but not in MyISAM

The difference is as listed below:

TRUNCATE

• It deletes all of the rows from a table.
• It does not necessitate a WHERE clause.
• Truncate is incompatible with indexed views.
• It is faster in terms of results.

DELETE

• It deletes some or all of the rows from a table.
• A WHERE clause is used to exclude certain rows based on the matched condition. When we do not use the Where condition in the Query, all rows are deleted.
• It eliminates rows one by one.
• It is compatible with indexed views.

DROP

• It is used to remove a table from the database.
• When we use this command, we will also remove all of the table's rows, indexes, and privileges.
• The action cannot be reversed.

• When creating a stored procedure, do not use "sp_."
• When using the SELECT command, do not use "*." Avoid including unnecessary columns in the SELECT clause as well
• When writing queries, you can use table aliases
• Avoid using the wildcard (percent) at the beginning of a predicate
• DISTINCT and UNION should only be used when absolutely necessary

A database index is a fact structure that enhances the speed with which operations in a table are performed. It can be built with one or more columns, laying the groundwork for both quick random lookup and efficient sorting of records access. Indexes help you rapidly find rows with certain column values.

This is one of the most searched MySQL query interview questions. The term cardinality in MySQL relates to the type of data values that can be stored in columns. It's a property that affects the ability to search, cluster, and sort data.

There are two types of cardinality, which are as follows:

• Low Cardinality: A column's values must all be the same.
• High Cardinality: A column's values should all be unique.

These specific MySQL interview questions and answers for experienced candidates can be helpful. DDL stands for Data Definition Language in MySQL, and it is used in database schemas and descriptions to determine how data should be stored in the database.

DDL Queries:

• CREATE
• ALTER
• DROP
• TRUNCATE
• COMMENT
• RENAME

DML stands for Data Manipulation Language and is used to manipulate data in databases. It largely consists of standard SQL commands for storing, modifying, retrieving, deleting, and updating data.

DML Queries are:

• SELECT
• INSERT
• UPDATE
• DELETE
• MERGE
• CALL
• EXPLAIN PLAN
• LOCK TABLE

DCL stands for Data Control Language and encompasses instructions that deal with user rights, permissions, and other database system controls.

List of queries for DCL:

• GRANT
• REVOKE

When a certain change operation, such as a SQL INSERT, UPDATE, or DELETE query, is executed on a table, a trigger corresponds to a collection of activities that run automatically.

This is one of the most common MySQL query interview questions. Sharding is the technique of dividing huge tables into smaller portions (called shards) that are distributed across different servers. The benefit of sharding is that searches, maintenance, and all other operations are substantially faster because the sharded database is generally much smaller than the original.