MySQL interview questions

Become a Punch Developer Hire us

Engineering, Design, Marketing, and More

MySQL interview questions

The most popular questions

Use our complementary questions and answers to filter and hire the best. Questions crowdsourced by our clients, answers by Punch. We provide these complementary questions to help our clients more quickly create tests for potential hires at their organizations.

Get a question answered

Or, browse engineering services

Punch offers four divisions of services: design, engineering, staffing, and demand

Interview questions for your next interview

E-mail a friend | Download text file

Question

What are engines in MySQL?

Answer

Like MyISAM, the MEMORY and MERGE storage engines handle nontransactional tables, and both are also included in MySQL by default. The MEMORY storageengine formerly was known as the HEAP engine. The InnoDB and BDB storageengines provide transaction-safe tables.

Send a correction

Question

Which is the best engine in MySQL?

Answer

XtraDB is the best choice in the majority of cases. It is a performance-enhanced fork of InnoDB and is MariaDB's default engine.
InnoDB is a good general transaction storage engine. It is the default MySQL storage engine, but XtraDB is a performance enhanced fork of InnoDB, and is usually preferred.
Aria, MariaDB's more modern improvement on MyISAM, has a small footprint and allows for easy copying between systems.
MyISAM has a small footprint and allows for easy copying between systems. MyISAM is MySQL's oldest storage engine. There is usually little reason to use it except for legacy purposes. Aria is MariaDB's more modern improvement.

Send a correction

Question

How do we find Duplicate values in MySQL?

Answer

SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;

Send a correction

Question

Which MySQL Datatype should be used for storing boolean values?

Answer

For MySQL 5.0.3 and higher, you can use BIT.
For versions lower than 5.0.3 you can use bool and boolean which are at the moment aliases of tinyint(1).

Send a correction

Question

What is referential Integrity?

Answer

Referential integrity is a relational database concept, which states that table relationships must always be consistent. In other words, any foreign key field must agree with the primary key that is referenced by the foreign key.

Send a correction

Question

Which is better, a higher Normal Form or a lower Normal Form?

Answer

The lower the normal form the lower the referential integrity but fetching is really efficient but higher the normal form the higher the referential integrity but number of queries required to fetch the same data is higher and less efficient.

Send a correction

Question

How do we scale a MySQL database?

Answer

There are many ways to scale a MySQL database.

Enable indexing on the columns which are not updated frequently.
Enable database Sharding using MySQL clusters.
Decrease the normal form of tables.
Use slaves for reading and masters for writing. Redirect select queries on slave as much as possible. Keep separate slaves for running heavy queries - for reports/analytics.
Create a layer of Memcached on top of MySQL, so queries do not hit your MySQL server, unless it is necessary to fetch fresh data.
Use proper table types. Store session information in memory tables, if the data is not important. Use InnoDB and MyISAM tables judiciously. It is OK to use InnoDB on master and MyISAM on slave.

Send a correction

Question

How would you shard a database in MySQL?

Answer

MySQL Cluster automatically shards (partitions) tables across nodes, enabling databases to scale horizontally on low cost, commodity hardware to serve read and write-intensive workloads, accessed both from SQL and directly via NoSQL APIs.

Send a correction

Question

Do we have to make changes in the application code to access shards?

Answer

Sharding is entirely transparent to the application which is able to connect to any node in the cluster and have queries automatically access the correct shards. With its active/active, multi-master architecture, updates can be handled by any node, and are instantly available to all of the other clients accessing the cluster.

Send a correction

Question

Can we perform Join Operations on Shards?

Answer

Yes, unlike other sharded databases, users do not lose the ability to perform JOIN operations, sacrifice ACID-guarantees or referential integrity (Foreign Keys) when performing queries and transactions across shards.

Send a correction

Previous Next

Ask a question

Ask a question, and one of our engineers will answer it.

We keep our questions nice and simple to be useful for everyone, and we may not answer or publish every question.

Your question

Topic

Your name

Your email

Your number is stored privately and never shared.
By submitting a question, you agree to our terms.

Request sent. Thank you!
Send another one