During an interview or while having general discussions, I have found some funny responses that can be easily classified as “Wrong Answers,” but at times, they’re thought-provoking or involve a deep meaning within. This blog is regarding some of the usual MySQL database conversations and responses, which can appear “wrong” or “funny,” but there’s actually more to them. I will share a selection of such seemingly “wrong” or whimsical responses and take a closer look at the valuable lessons and perspectives they offer.
Let the “MySQL Interview” begin.
Q: How will you improve a slow query?
A: Let’s not execute it at all. A query avoided is a query improved.
While this is a fact, we should carefully consider whether a query is necessary before executing it. Avoiding unnecessary queries and fetching only the required data can significantly optimize the query’s performance.
An approach to improve a query which cannot be avoided will be:
- Monitor slow query log and use pt-query-digest to generate a summary report for slow queries.
- Use an explain statement in MySQL to understand the query execution plan, offering insights into table access order, index usage, and potential performance bottlenecks.
Additional read
- Mike’s blog on How to Find and Tune a Slow SQL Query
Q: What is your disaster recovery (DR) strategy?
A: We have a replica under our primary database.
Hmm, a replica seems like a straightforward response, but it is not a comprehensive disaster recovery strategy. In reality, relying solely on a replica under the primary server is not sufficient for a robust disaster recovery plan.
In a disaster recovery (DR) strategy, it is essential to consider multiple aspects, naming a few
- Data backup
- High availability
- Failover mechanisms
- Offsite storage
While having a replica is beneficial for load balancing and read scaling, it does not cover all disaster scenarios.
Additional read
Q: What about delayed replica?
A: Well, it is our delayed disaster recovery.
“What about delayed replica?” you may ask. Well, it is a delayed disaster-in-waiting. 🙂
A lot depends on how strong your monitoring strategy is and how fast you can react to the DR call.
The delayed replica surely complements regular real-time replicas by providing an additional layer of DR protection as compared to the active primary. But when disaster strikes and, importantly, is detected within the configured replica-delay, it provides a bit of an easy recovery option. That said, if the delayed replica is hosted on the same infrastructure/data center, it is vulnerable to the same disaster affecting the primary.
It should surely help provide a good backup plan to guard against human error, logical error, data corruption, etc.
Additional read
- Walter’s ultimate guide of MySQL Backup and Recovery Best Practices.
Q: What is one of your favourite (and common) security worst practices?
A: Usage of .my.cnf file
The .my.cnf file is typically used to store login credentials for MySQL, allowing users to connect to the database without providing credentials explicitly. We all know that saving plaintext passwords in this file is a significant security risk, as it could lead to unauthorized access if the file system is compromised. The same risk is present while using the password on the command prompt.
Additional read
Q: What will you do to alter a table sized 10T?
A: Nothing. I will not.
Well, the natural response would be to suggest looking for ONLINE ALTER options using tools like pt-online-schema-change or gh-ost. While those answers seem correct, would you really be able to alter a 10T table? Think about the time and resources required for such an activity. Clearly, 10T is just a number to represent a gigantic table size to give a perspective.
The counter question would be, “Why do you have such a large table in the database?”. Since the size is “terrantic” (terabyte-sized), further growth is highly likely; there should either be an archiving strategy or some change in application logic to have a manageable table size.
Large tables in your production will cost your query performance, cause inefficient reading and writing, slow backup/restores, and introduce challenges in application changes and database upgrades. It is important to understand and monitor the table growth in your system and work on possible table archiving strategies.
The Percona Monitoring and Management dashboard does list the large tables by size, by rows, and even tables that are getting to table-full situations.
Finally, one trivia question, I request that you respond in the comments.
MySQL has a single database object, which is actually double. You can’t see either of them, yet you can query! What is that?
Additional read
- Learn about Percona’s online schema change tool.
Conclusion
Before concluding, I invite you to share your own playful takes on MySQL-related questions. As we wrap up, let’s emphasize the importance of going beyond the obvious when tackling questions. Sometimes, the right answer requires a deeper dive, and that’s where the true understanding lies. Until next time, happy MySQL-ing!
I can relate to answers 1 and 5 as I probably would have answered the exact same thing. As having been in the interview seats many times, whenever I get an open question like these two I tend to answer with a provocative answer. Normally this would lead to a discussion. Being a MySQL expert, I can leverage this discussion to determine if I actually want to join the organization I’m applying to. If the discussion is unsatisfactory I’ll simply decline the offer.
> MySQL has a single database object, which is actually double.
> You can’t see either of them, yet you can query! What is that?
I would say you are referring to the generated invisible primary key. If the sql_generate_invisible_primary_key is enabled, a table without a primary key will automagically have one created. This creates both an invisible column with an auto increment attached to it and an invisible primary key. This means you can’t see either of them, but you would be able to select a specific row (or rows) by querying this column explicitly.
Hi “Art”,
As an interviewer I really like to understand the thought process more than the real answer. You know “love the journey more than the destination” thing 😉 it is sort of that. This gives us a picture of how much of a brainstorming the candidate can do.
Also, about “…if I actually want to join the organization I’m applying to. “, usually during an Interview, the interviewer might really pulling your nerves to test you, doing deliberate wrong counter arguments to get the better justification and so on. IMHO this is not really a good subject to base a decision.
About your response to Trivia question is not right. Here I’m playing with the word “Dual”.
MySQL has a single database object, which is actually double.
You can’t see either of them, yet you can query! What is that?
> “dual” is a table (object) but the meaning hints “double”
> you cannot see “dual” object by “show tables” or any other queries but you can query a “dual” table:
Finally, I’d share my blog about sql_generate_invisible_primary_key and its usefulness.
(BTW Art, I feel the comment is AI generated)
Thanks,
Kedar.
Yes, it’s a recently solved problem: