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
Punch offers four divisions of services: design, engineering, staffing, and demand

Interview questions for your next interview

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.
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.

Find developers today

Hire a Punch engineer

Punch offers four divisions of services: design, engineering, staffing, and demand. Our four divisions form the core of our People Forward Approach.

Contact us
Find developers today
Question
How do we find Duplicate values in MySQL?
Answer
SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;
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).
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.
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.
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.
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.
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.
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.

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 number is stored privately and never shared.
By submitting a question, you agree to our terms.
Request sent. Thank you!
Send another one