Top 150+ MySQL DBA, Developer, and Designer Interview Questions

 

Table of Contents

1. Beginner-Level Questions

   • MySQL DBA

   • MySQL Developer

   • MySQL Designer

2. Intermediate-Level Questions

   • MySQL DBA

   • MySQL Developer

   • MySQL Designer

3. Expert-Level Questions

   • MySQL DBA

   • MySQL Developer

   • MySQL Designer

4. Conclusion

---

Beginner-Level Questions

These questions cover foundational concepts, basic SQL, and core MySQL principles for entry-level candidates.

MySQL DBA (Beginner)

1. What is MySQL, and what are its key components?

   • Type: Basic Understanding

   • Expected Answer: MySQL is an open-source RDBMS managing data with components like the server, storage engines, and data files.

   • Example: InnoDB stores data and indexes in .ibd files.

2. What is the difference between a MySQL instance and a database?

   • Type: Conceptual

   • Expected Answer: An instance is the running MySQL server process, while a database is a collection of tables within it.

3. What is the role of the my.cnf or my.ini file?

   • Type: Conceptual

   • Expected Answer: The my.cnf/my.ini file configures MySQL server settings like memory and storage engines.

4. What is a storage engine in MySQL?

   • Type: Basic Understanding

   • Expected Answer: A storage engine manages how data is stored and retrieved, e.g., InnoDB or MyISAM.

   • Example: InnoDB supports transactions, MyISAM does not.

5. What is the purpose of the MySQL error log?

   • Type: Conceptual

   • Expected Answer: The error log records server issues, startups, and shutdowns for troubleshooting.

6. What are the main MySQL storage engines, and their use cases?

   • Type: Conceptual

   • Expected Answer: InnoDB for transactions, MyISAM for read-heavy tasks, and MEMORY for temporary data.

7. What is a backup, and why is it important in MySQL?

   • Type: Basic Understanding

   • Expected Answer: A backup is a copy of a database for recovery from data loss or corruption.

8. What is the difference between a user and a role in MySQL?

   • Type: Conceptual

   • Expected Answer: A user authenticates to the server, while a role groups privileges for easier management.

9. What is the purpose of the mysql database?

   • Type: Conceptual

   • Expected Answer: The mysql database stores system metadata, such as user privileges and schema information.

10. What is a MySQL tablespace?

    • Type: Conceptual

    • Expected Answer: A tablespace is a logical storage unit for data, used by InnoDB to manage files.

MySQL Developer (Beginner)

11. What is SQL, and how does MySQL use it?

    • Type: Basic Understanding

    • Expected Answer: SQL is a query language for managing data; MySQL extends it with custom functions.

    • Example:

      SELECT NOW();

12. What is a stored procedure in MySQL?

    • Type: Conceptual

    • Expected Answer: A stored procedure is a precompiled SQL block for reusable logic.

13. What is the difference between a stored procedure and a function?

    • Type: Conceptual

    • Expected Answer: Functions return a value and can be used in queries, while procedures do not.

    • Example:

      CREATE FUNCTION get_salary(emp_id INT) RETURNS INT
      DETERMINISTIC
      BEGIN
        DECLARE sal INT;
        SELECT salary INTO sal FROM employees WHERE employee_id = emp_id;
        RETURN sal;
      END;

14. What is a trigger in MySQL?

    • Type: Conceptual

    • Expected Answer: A trigger is a SQL block that executes automatically on DML events.

    • Example:

      CREATE TRIGGER log_update
      AFTER UPDATE ON employees
      FOR EACH ROW
      INSERT INTO audit_log (employee_id, update_time)
      VALUES (OLD.employee_id, NOW());

15. What is a cursor in MySQL?

    • Type: Conceptual

    • Expected Answer: A cursor processes query results row by row in stored procedures.

    • Example:

      DECLARE emp_cursor CURSOR FOR SELECT employee_id FROM employees;
      OPEN emp_cursor;
      FETCH emp_cursor INTO v_emp_id;

16. What is the purpose of the LOCK TABLES statement?

    • Type: Practical

    • Expected Answer: It locks tables to prevent concurrent access during operations.

    • Example:

      LOCK TABLES employees WRITE;

17. What is a view in MySQL?

    • Type: Basic Understanding

    • Expected Answer: A view is a virtual table based on a query, storing no data itself.

    • Example:

      CREATE VIEW emp_view AS
      SELECT employee_id, first_name FROM employees;

18. What is the difference between DELETE and TRUNCATE?

    • Type: Conceptual

    • Expected Answer: DELETE removes specific rows with logging, while TRUNCATE removes all rows without logging.

19. What is an index, and why is it used?

    • Type: Basic Understanding

    • Expected Answer: An index improves query performance by organizing data.

    • Example:

      CREATE INDEX idx_emp_id ON employees(employee_id);

20. What is the purpose of the EXPLAIN statement?

    • Type: Practical

    • Expected Answer: EXPLAIN shows the query execution plan for optimization.

    • Example:

      EXPLAIN SELECT * FROM employees WHERE employee_id = 100;

MySQL Designer (Beginner)

21. What is database normalization, and why is it important?

    • Type: Basic Understanding

    • Expected Answer: Normalization reduces redundancy and ensures integrity using normal forms (1NF, 2NF, 3NF).

    • Example: Splitting orders and customers to avoid duplicate data.

22. What is a primary key in MySQL?

    • Type: Conceptual

    • Expected Answer: A primary key uniquely identifies rows and enforces integrity.

23. What is a foreign key, and how does it work?

    • Type: Conceptual

    • Expected Answer: A foreign key links tables, ensuring referential integrity.

    • Example:

      CREATE TABLE orders (
        order_id INT PRIMARY KEY,
        customer_id INT,
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
      );

24. What is the difference between a logical and physical data model?

    • Type: Conceptual

    • Expected Answer: Logical models define data structure conceptually, while physical models specify storage.

25. What is an ERD (Entity-Relationship Diagram)?

    • Type: Basic Understanding

    • Expected Answer: An ERD visualizes entities, attributes, and relationships.

26. What is a unique key in MySQL?

    • Type: Conceptual

    • Expected Answer: A unique key ensures column values are unique, allowing NULLs unlike primary keys.

    • Example:

      CREATE TABLE users (
        user_id INT PRIMARY KEY,
        email VARCHAR(100) UNIQUE
      );

27. What is the purpose of a schema in MySQL?

    • Type: Conceptual

    • Expected Answer: A schema organizes database objects for modularity and access control.

28. What is the role of constraints in MySQL?

    • Type: Basic Understanding

    • Expected Answer: Constraints enforce rules like uniqueness or nullability.

    • Example:

      ALTER TABLE employees ADD CONSTRAINT emp_pk PRIMARY KEY (employee_id);

29. What is data modeling, and why is it important?

    • Type: Basic Understanding

    • Expected Answer: Data modeling designs the database structure to meet business needs and ensure efficiency.

30. What is the difference between 1NF and 2NF?

    • Type: Conceptual

    • Expected Answer: 1NF eliminates repeating groups, while 2NF ensures non-key attributes depend on the entire primary key.

---

Intermediate-Level Questions

These questions target candidates with 2–5 years of experience, focusing on practical scenarios, optimization, and advanced MySQL features.

MySQL DBA (Intermediate)

31. How do you monitor MySQL performance?

    • Type: Practical

    • Expected Answer: Use tools like SHOW STATUS, Performance Schema, or MySQL Workbench.

    • Example:

      SELECT * FROM performance_schema.events_statements_summary_by_digest;

32. What is the purpose of the MySQL slow query log?

    • Type: Conceptual

    • Expected Answer: The slow query log records queries exceeding a time threshold for optimization.

33. How do you handle a MySQL server crash?

    • Type: Scenario-Based

    • Expected Answer: Check logs, restore from backups, and repair tables using mysqlcheck.

    • Example:

      mysqlcheck --repair mydatabase;

34. What is MySQL replication, and how does it work?

    • Type: Conceptual

    • Expected Answer: Replication copies data from a primary to replica servers using binary logs.

    • Example:

      CHANGE REPLICATION SOURCE TO SOURCE_HOST='primary_host';

35. How do you configure a MySQL backup?

    • Type: Practical

    • Expected Answer: Use mysqldump or Percona XtraBackup for logical/physical backups.

    • Example:

      mysqldump -u root -p mydatabase > backup.sql;

36. What is the difference between InnoDB and MyISAM storage engines?

    • Type: Conceptual

    • Expected Answer: InnoDB supports transactions and foreign keys, MyISAM is faster for reads but lacks these features.

37. How do you troubleshoot a slow MySQL query?

    • Type: Scenario-Based

    • Expected Answer: Use EXPLAIN, check indexes, and optimize query structure.

    • Example:

      EXPLAIN SELECT * FROM employees WHERE salary > 50000;

38. What is the role of the binary log in MySQL?

    • Type: Conceptual

    • Expected Answer: Binary logs record changes for replication and recovery.

39. How do you manage user privileges in MySQL?

    • Type: Practical

    • Expected Answer: Use GRANT and REVOKE statements to assign or remove privileges.

    • Example:

      GRANT SELECT ON mydatabase.* TO 'user'@'localhost';

40. What is the purpose of the INFORMATION_SCHEMA database?

    • Type: Conceptual

    • Expected Answer: INFORMATION_SCHEMA provides metadata about database objects.

    • Example:

      SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mydatabase';

MySQL Developer (Intermediate)

41. How do you optimize a MySQL query for performance?

    • Type: Practical

    • Expected Answer: Use indexes, avoid subqueries, and optimize joins.

    • Example:

      SELECT e.employee_id, e.first_name
      FROM employees e
      WHERE EXISTS (SELECT 1 FROM orders o WHERE o.employee_id = e.employee_id);

42. What is a Common Table Expression (CTE) in MySQL?

    • Type: Conceptual

    • Expected Answer: A CTE is a temporary result set for query readability (MySQL 8.0+).

    • Example:

      WITH emp_cte AS (
        SELECT employee_id, first_name FROM employees WHERE salary > 50000
      )
      SELECT * FROM emp_cte;

43. How do you handle errors in MySQL stored procedures?

    • Type: Practical

    • Expected Answer: Use DECLARE ... HANDLER for error handling.

    • Example:

      DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_error = 1;

44. What is the difference between UNION and UNION ALL?

    • Type: Conceptual

    • Expected Answer: UNION removes duplicates, while UNION ALL is faster and keeps all rows.

45. How do you implement pagination in a MySQL query?

    • Type: Coding Challenge

    • Expected Answer:

      SELECT * FROM employees
      ORDER BY employee_id
      LIMIT 10 OFFSET 20;

46. What is a stored function, and when is it used?

    • Type: Conceptual

    • Expected Answer: Stored functions return values and are used in queries for reusable logic.

    • Example:

      CREATE FUNCTION total_salary() RETURNS INT
      BEGIN
        DECLARE total INT;
        SELECT SUM(salary) INTO total FROM employees;
        RETURN total;
      END;

47. How do you use dynamic SQL in MySQL?

    • Type: Practical

    • Expected Answer: Use PREPARE and EXECUTE for dynamic queries.

    • Example:

      SET @sql = 'SELECT * FROM employees WHERE salary > ?';
      PREPARE stmt FROM @sql;
      SET @sal = 50000;
      EXECUTE stmt USING @sal;

48. What is the purpose of the INSERT ... ON DUPLICATE KEY UPDATE statement?

    • Type: Conceptual

    • Expected Answer: It inserts or updates rows based on unique key conflicts.

    • Example:

      INSERT INTO employees (employee_id, salary)
      VALUES (100, 60000)
      ON DUPLICATE KEY UPDATE salary = 60000;

49. How do you create a sequence-like behavior in MySQL?

    • Type: Practical

    • Expected Answer: Use AUTO_INCREMENT for sequences.

    • Example:

      CREATE TABLE employees (
        employee_id INT AUTO_INCREMENT PRIMARY KEY,
        first_name VARCHAR(50)
      );

50. What is the difference between INNER JOIN and LEFT JOIN?

    • Type: Conceptual

    • Expected Answer: INNER JOIN returns matching rows, while LEFT JOIN includes all rows from the left table.

MySQL Designer (Intermediate)

51. How do you design a database schema for a multi-tenant application?

    • Type: Scenario-Based

    • Expected Answer: Use separate databases, schemas, or tenant IDs in tables.

    • Example:

      CREATE TABLE tenants (
        tenant_id INT PRIMARY KEY,
        tenant_name VARCHAR(100)
      );

52. What is the role of indexing in database design?

    • Type: Conceptual

    • Expected Answer: Indexes improve query performance but increase storage and maintenance.

53. How do you handle denormalization in database design?

    • Type: Scenario-Based

    • Expected Answer: Denormalize for read-heavy systems, balancing redundancy and performance.

54. What is a composite key, and when is it used?

    • Type: Conceptual

    • Expected Answer: A composite key uses multiple columns for uniqueness when no single column suffices.

55. How do you model a many-to-many relationship?

    • Type: Practical

    • Expected Answer: Use a junction table with foreign keys.

    • Example:

      CREATE TABLE student_courses (
        student_id INT,
        course_id INT,
        PRIMARY KEY (student_id, course_id)
      );

56. What is the role of partitioning in MySQL?

    • Type: Conceptual

    • Expected Answer: Partitioning splits large tables for performance and manageability.

    • Example:

      CREATE TABLE sales (
        sale_id INT,
        sale_date DATE
      ) PARTITION BY RANGE (YEAR(sale_date)) (
        PARTITION p0 VALUES LESS THAN (2024),
        PARTITION p1 VALUES LESS THAN (2025)
      );

57. How do you ensure data integrity in a database design?

    • Type: Practical

    • Expected Answer: Use constraints, triggers, and foreign keys.

58. What is the difference between BCNF and 3NF?

    • Type: Conceptual

    • Expected Answer: BCNF is stricter, ensuring no non-trivial functional dependencies.

59. How do you design a database for scalability?

    • Type: Scenario-Based

    • Expected Answer: Use partitioning, sharding, and optimized indexing.

60. What is the purpose of the performance_schema database?

    • Type: Conceptual

    • Expected Answer: performance_schema provides performance metrics for monitoring.

---

Expert-Level Questions

These questions challenge senior professionals with complex scenarios, advanced MySQL features, and critical system design tasks.

MySQL DBA (Expert)

61. How do you implement MySQL Group Replication for high availability?

    • Type: Scenario-Based

    • Expected Answer: Configure multi-primary or single-primary replication with automatic failover.

    • Example:

      SET GLOBAL group_replication_bootstrap_group=ON;
      START GROUP_REPLICATION;

62. What is the role of the InnoDB buffer pool?

    • Type: Conceptual

    • Expected Answer: The buffer pool caches data and indexes in memory for faster access.

63. How do you recover a MySQL database from a corrupted table?

    • Type: Scenario-Based

    • Expected Answer: Restore from a backup or repair using REPAIR TABLE.

    • Example:

      REPAIR TABLE employees;

64. What is the role of MySQL’s Performance Schema?

    • Type: Conceptual

    • Expected Answer: Performance Schema monitors server performance and resource usage.

    • Example:

      SELECT * FROM performance_schema.events_waits_summary_global_by_event_name;

65. How do you optimize MySQL for high-concurrency workloads?

    • Type: Scenario-Based

    • Expected Answer: Tune buffer pool, thread cache, and connection limits.

    • Example:

      SET GLOBAL innodb_buffer_pool_size = 2G;

66. What is the difference between logical and physical backups?

    • Type: Conceptual

    • Expected Answer: Logical backups (e.g., mysqldump) export SQL, while physical backups copy data files.

67. How do you implement MySQL Galera Cluster?

    • Type: Scenario-Based

    • Expected Answer: Configure synchronous multi-master replication with Galera.

    • Example:

      SET GLOBAL wsrep_on=ON;

68. What is the role of the MySQL query cache?

    • Type: Conceptual

    • Expected Answer: Query cache stores query results for faster retrieval (deprecated in MySQL 8.0).

69. How do you handle a MySQL replication lag?

    • Type: Scenario-Based

    • Expected Answer: Optimize queries, increase replica resources, or use parallel replication.

    • Example:

      SET GLOBAL slave_parallel_workers = 4;

70. What is the role of MySQL’s Event Scheduler?

    • Type: Practical

    • Expected Answer: Event Scheduler automates recurring tasks.

    • Example:

      CREATE EVENT cleanup
      ON SCHEDULE EVERY 1 DAY
      DO DELETE FROM logs WHERE log_date < NOW() - INTERVAL 30 DAY;

MySQL Developer (Expert)

71. How do you implement bulk insert in MySQL?

    • Type: Coding Challenge

    • Expected Answer:

      LOAD DATA INFILE 'employees.csv'
      INTO TABLE employees
      FIELDS TERMINATED BY ',';

72. What is the difference between a stored procedure and a stored function?

    • Type: Conceptual

    • Expected Answer: Stored procedures execute tasks, while functions return values and are query-friendly.

73. How do you implement parallel query execution in MySQL?

    • Type: Practical

    • Expected Answer: Use InnoDB parallel query processing (MySQL 8.0+).

    • Example:

      SET SESSION innodb_parallel_read_threads = 4;

74. What is the role of the WITH ROLLUP clause?

    • Type: Conceptual

    • Expected Answer: WITH ROLLUP adds summary rows to GROUP BY results.

    • Example:

      SELECT department_id, SUM(salary) FROM employees
      GROUP BY department_id WITH ROLLUP;

75. How do you handle JSON data in MySQL?

    • Type: Practical

    • Expected Answer: Use JSON data type and functions like JSON_EXTRACT.

    • Example:

      SELECT JSON_EXTRACT(data, '$.name') FROM json_table;

76. What is the difference between ROW_NUMBER and RANK?

    • Type: Conceptual

    • Expected Answer: ROW_NUMBER assigns unique numbers, while RANK assigns same rank for ties (MySQL 8.0+).

77. How do you implement a recursive CTE in MySQL?

    • Type: Coding Challenge

    • Expected Answer:

      WITH RECURSIVE org_chart AS (
        SELECT employee_id, manager_id, first_name
        FROM employees WHERE manager_id IS NULL
        UNION ALL
        SELECT e.employee_id, e.manager_id, e.first_name
        FROM employees e
        INNER JOIN org_chart o ON e.manager_id = o.employee_id
      )
      SELECT * FROM org_chart;

78. What is the role of HANDLER statements in MySQL?

    • Type: Practical

    • Expected Answer: HANDLER provides low-level access to table data.

    • Example:

      HANDLER employees OPEN;
      HANDLER employees READ FIRST;

79. How do you secure MySQL stored procedures?

    • Type: Practical

    • Expected Answer: Use DEFINER or INVOKER security and limit privileges.

    • Example:

      CREATE PROCEDURE my_proc SQL SECURITY INVOKER AS
      BEGIN
        SELECT * FROM employees;
      END;

80. What is the difference between a correlated and non-correlated subquery?

    • Type: Conceptual

    • Expected Answer: Correlated subqueries reference outer queries, executed per row, while non-correlated are independent.

MySQL Designer (Expert)

81. How do you design a database for high availability?

    • Type: Scenario-Based

    • Expected Answer: Use Group Replication or Galera Cluster with proper indexing.

82. What is the role of MySQL’s table partitioning?

    • Type: Conceptual

    • Expected Answer: Partitioning splits large tables for performance and manageability.

83. How do you implement data archiving in MySQL?

    • Type: Practical

    • Expected Answer: Use partitioning and automated jobs.

    • Example:

      ALTER TABLE logs DROP PARTITION p0;

84. What is the difference between a B-tree and hash index?

    • Type: Conceptual

    • Expected Answer: B-tree indexes support range queries, while hash indexes are faster for equality checks.

85. How do you design a database for real-time analytics?

    • Type: Scenario-Based

    • Expected Answer: Use InnoDB with optimized indexes and caching.

    • Example:

      CREATE TABLE analytics (
        event_id INT,
        event_time DATETIME,
        INDEX idx_time (event_time)
      );

86. What is the role of MySQL’s InnoDB Cluster?

    • Type: Conceptual

    • Expected Answer: InnoDB Cluster provides high availability and scalability via Group Replication.

87. How do you model a temporal database?

    • Type: Practical

    • Expected Answer: Use timestamp columns for versioning.

    • Example:

      CREATE TABLE employee_history (
        emp_id INT,
        valid_from DATETIME,
        valid_to DATETIME
      );

88. What is the difference between OLTP and OLAP in database design?

    • Type: Conceptual

    • Expected Answer: OLTP handles transactional data, while OLAP supports analytical queries.

89. How do you handle schema evolution in a production database?

    • Type: Scenario-Based

    • Expected Answer: Use online DDL and backward-compatible changes.

    • Example:

      ALTER TABLE employees ADD COLUMN new_col VARCHAR(50) NULL;

90. What is the role of MySQL’s X Protocol?

    • Type: Conceptual

    • Expected Answer: X Protocol supports NoSQL-style access via MySQL Document Store.

91. How do you design a database for GDPR compliance?

    • Type: Scenario-Based

    • Expected Answer: Implement data masking and encryption.

    • Example:

      SET SESSION data_masking = ON;

92. What is the role of MySQL’s Query Rewrite Plugin?

    • Type: Conceptual

    • Expected Answer: Query Rewrite Plugin modifies queries before execution for optimization.

93. How do you design a database for audit logging?

    • Type: Practical

    • Expected Answer: Use triggers or general log.

    • Example:

      SET GLOBAL general_log = 'ON';

94. What is the difference between a clustered and non-clustered index?

    • Type: Conceptual

    • Expected Answer: Clustered indexes (InnoDB primary keys) define data order, while non-clustered are separate.

95. How do you optimize a star schema for data warehousing?

    • Type: Scenario-Based

    • Expected Answer: Use denormalized tables and covering indexes.

96. What is the role of MySQL’s Data Dictionary?

    • Type: Conceptual

    • Expected Answer: Data Dictionary stores metadata in InnoDB tables (MySQL 8.0+).

97. How do you design a database for global applications?

    • Type: Scenario-Based

    • Expected Answer: Use replication, sharding, and UTF-8 encoding.

98. What is the difference between a unique and non-unique index?

    • Type: Conceptual

    • Expected Answer: Unique indexes enforce uniqueness, while non-unique do not.

99. How do you implement row-level security in MySQL?

    • Type: Practical

    • Expected Answer: Use views or stored procedures with session variables.

    • Example:

      CREATE VIEW emp_view AS
      SELECT * FROM employees WHERE department_id = CURRENT_USER();

100. What is the role of MySQL’s Document Store?

     • Type: Conceptual

     • Expected Answer: Document Store enables NoSQL JSON document management.

101. How do you design a database for high-concurrency applications?

     • Type: Scenario-Based

     • Expected Answer: Use InnoDB, optimize locking, and scale with replication.

102. What is the role of MySQL’s Router?

     • Type: Conceptual

     • Expected Answer: MySQL Router directs traffic in InnoDB Cluster for load balancing.

103. How do you handle data migration across MySQL versions?

     • Type: Scenario-Based

     • Expected Answer: Use mysqldump or MySQL Workbench for migrations.

     • Example:

       mysqldump -u root -p mydatabase > backup.sql;

104. What is the role of MySQL’s Enterprise Backup?

     • Type: Conceptual

     • Expected Answer: Enterprise Backup provides hot backups for InnoDB tables.

105. How do you design a database for IoT data?

     • Type: Scenario-Based

     • Expected Answer: Use time-based partitioning and JSON for unstructured data.

     • Example:

       CREATE TABLE sensor_data (
         sensor_id INT,
         timestamp DATETIME,
         data JSON
       ) PARTITION BY RANGE (UNIX_TIMESTAMP(timestamp));

MySQL Developer (Expert, Continued)

106. How do you implement a MySQL event scheduler?

     • Type: Coding Challenge

     • Expected Answer:

       CREATE EVENT daily_cleanup
       ON SCHEDULE EVERY 1 DAY
       DO DELETE FROM logs WHERE log_date < NOW() - INTERVAL 30 DAY;

107. What is the role of JSON_TABLE in MySQL?

     • Type: Conceptual

     • Expected Answer: JSON_TABLE converts JSON data into relational format.

     • Example:

       SELECT * FROM JSON_TABLE(data, '$[*]' COLUMNS (id INT PATH '$.id')) AS jt;

108. How do you handle large datasets in MySQL?

     • Type: Scenario-Based

     • Expected Answer: Use batch processing and indexing.

109. What is the difference between ENGINE=InnoDB and ENGINE=MEMORY?

     • Type: Conceptual

     • Expected Answer: InnoDB is persistent and transactional, while MEMORY is in-memory and non-persistent.

110. How do you implement a MySQL REST API consumer?

     • Type: Coding Challenge

     • Expected Answer: Use http plugin or external scripting.

     • Example:

       SELECT http_get('http://api.example.com');

111. What is the role of ANALYZE TABLE in MySQL?

     • Type: Practical

     • Expected Answer: Updates table statistics for query optimization.

     • Example:

       ANALYZE TABLE employees;

112. How do you implement error logging in MySQL?

     • Type: Practical

     • Expected Answer: Use stored procedures and log tables.

     • Example:

       CREATE PROCEDURE log_error(err_msg VARCHAR(255))
       BEGIN
         INSERT INTO error_log (message, log_time) VALUES (err_msg, NOW());
       END;

113. What is the difference between AUTO_INCREMENT and UUID?

     • Type: Conceptual

     • Expected Answer: AUTO_INCREMENT generates sequential integers, while UUID creates unique strings.

114. How do you implement a MySQL job chain?

     • Type: Practical

     • Expected Answer: Use Event Scheduler with dependent events.

     • Example:

       CREATE EVENT step2
       ON SCHEDULE EVERY 1 DAY
       DO CALL proc2();

115. What is the role of LOAD DATA INFILE in MySQL?

     • Type: Conceptual

     • Expected Answer: Imports data from files into tables.

     • Example:

       LOAD DATA INFILE 'data.csv' INTO TABLE employees;

MySQL Designer (Expert, Continued)

116. How do you design a database for microservices?

     • Type: Scenario-Based

     • Expected Answer: Use separate databases per microservice with API integration.

117. What is the role of MySQL’s Partitioned Views?

     • Type: Conceptual

     • Expected Answer: Partitioned views combine data from multiple tables for querying.

118. How do you implement a data warehouse in MySQL?

     • Type: Scenario-Based

     • Expected Answer: Use star schemas and partitioning.

119. What is the difference between a snowflake and star schema?

     • Type: Conceptual

     • Expected Answer: Star schemas are denormalized, while snowflake schemas normalize dimension tables.

120. How do you design a database for time-series data?

     • Type: Practical

     • Expected Answer:

       CREATE TABLE time_series (
         sensor_id INT,
         timestamp DATETIME,
         value FLOAT
       ) PARTITION BY RANGE (UNIX_TIMESTAMP(timestamp));

121. What is the role of MySQL’s Group Replication Plugin?

     • Type: Conceptual

     • Expected Answer: Enables synchronous multi-master replication.

122. How do you handle cross-database queries in MySQL?

     • Type: Practical

     • Expected Answer: Use fully qualified table names.

     • Example:

       SELECT * FROM other_db.employees;

123. What is the role of MySQL’s Shell?

     • Type: Conceptual

     • Expected Answer: MySQL Shell provides scripting and administration capabilities.

124. How do you design a database for audit compliance?

     • Type: Scenario-Based

     • Expected Answer: Use triggers and general log.

     • Example:

       SET GLOBAL general_log = 'ON';

125. What is the difference between a global and local index?

     • Type: Conceptual

     • Expected Answer: MySQL uses local indexes for partitions; global indexes are not supported.

126. How do you implement data encryption in MySQL?

     • Type: Practical

     • Expected Answer: Use InnoDB encryption or AES functions.

     • Example:

       CREATE TABLE secure_data (
         data VARCHAR(100) ENCRYPTION 'Y'
       );

127. What is the role of MySQL’s Enterprise Edition?

     • Type: Conceptual

     • Expected Answer: Enterprise Edition adds features like encryption and monitoring.

128. How do you design a database for high-frequency trading?

     • Type: Scenario-Based

     • Expected Answer: Use MEMORY engine and optimized indexing.

129. What is the difference between ACID and BASE properties?

     • Type: Conceptual

     • Expected Answer: ACID ensures reliability, while BASE prioritizes availability.

130. How do you implement a data retention policy?

     • Type: Practical

     • Expected Answer: Use partitioning and scheduled events.

     • Example:

       CREATE EVENT drop_old
       ON SCHEDULE EVERY 1 MONTH
       DO ALTER TABLE logs DROP PARTITION p0;

131. What is the role of MySQL’s ProxySQL?

     • Type: Conceptual

     • Expected Answer: ProxySQL load balances and caches queries.

132. How do you design a database for global applications?

     • Type: Scenario-Based

     • Expected Answer: Use replication and UTF-8 encoding.

133. What is the role of MySQL’s GIS features?

     • Type: Conceptual

     • Expected Answer: GIS features support geospatial data analysis.

     • Example:

       SELECT ST_Distance_Sphere(point1, point2) FROM locations;

134. How do you handle schema versioning in MySQL?

     • Type: Practical

     • Expected Answer: Use migrations and versioning tables.

     • Example:

       CREATE TABLE schema_version (
         version INT,
         applied_at DATETIME
       );

135. What is the difference between a heap and index-organized table?

     • Type: Conceptual

     • Expected Answer: MySQL uses B-tree for InnoDB; heap tables are MyISAM-specific.

136. How do you implement a change data capture (CDC) system?

     • Type: Practical

     • Expected Answer: Use triggers or binlog parsing tools.

     • Example:

       CREATE TRIGGER cdc_trigger
       AFTER INSERT ON employees
       FOR EACH ROW
       INSERT INTO cdc_log (event) VALUES ('INSERT');

137. What is the role of MySQL’s Authentication Plugins?

     • Type: Conceptual

     • Expected Answer: Authentication plugins secure user access.

138. How do you design a database for machine learning workloads?

     • Type: Scenario-Based

     • Expected Answer: Use JSON and optimized indexing.

139. What is the difference between a unique and non-unique index?

     • Type: Conceptual

     • Expected Answer: Unique indexes enforce uniqueness, non-unique do not.

140. How do you implement a failover mechanism in MySQL?

     • Type: Practical

     • Expected Answer: Use Group Replication or Orchestrator.

     • Example:

       SET GLOBAL group_replication_recovery = 'ON';

141. What is the role of MySQL’s Optimizer Hints?

     • Type: Conceptual

     • Expected Answer: Optimizer Hints guide query execution plans.

     • Example:

       SELECT /*+ INDEX(employees idx_emp_id) */ * FROM employees;

142. How do you design a database for event-driven architectures?

     • Type: Scenario-Based

     • Expected Answer: Use triggers and message queues.

143. What is the difference between a full and incremental backup?

     • Type: Conceptual

     • Expected Answer: Full backups copy all data, while incremental copy changes.

144. How do you implement a data masking solution?

     • Type: Practical

     • Expected Answer: Use dynamic masking (MySQL Enterprise).

     • Example:

       SET SESSION data_masking = ON;

145. What is the role of MySQL’s Window Functions?

     • Type: Conceptual

     • Expected Answer: Window Functions perform calculations across rows.

     • Example:

       SELECT employee_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary) AS rank
       FROM employees;

146. How do you design a database for reporting?

     • Type: Scenario-Based

     • Expected Answer: Use star schemas and materialized views.

147. What is the role of MySQL’s Thread Pool?

     • Type: Conceptual

     • Expected Answer: Thread Pool manages connections for high concurrency.

148. How do you handle large-scale data imports?

     • Type: Practical

     • Expected Answer: Use LOAD DATA INFILE or MySQL Shell.

     • Example:

       LOAD DATA INFILE 'data.csv' INTO TABLE employees;

149. What is the difference between a synonym and a view?

     • Type: Conceptual

     • Expected Answer: MySQL does not support synonyms; views are virtual tables.

150. How do you implement a distributed transaction in MySQL?

     • Type: Practical

     • Expected Answer: Use XA transactions.

     • Example:

       XA START 'xid';
       INSERT INTO employees VALUES (100, 'John');
       XA END 'xid';
       XA COMMIT 'xid';

151. What is the role of MySQL’s Slow Query Log Analyzer?

     • Type: Conceptual

     • Expected Answer: Analyzes slow queries for optimization.

152. How do you design a database for high-performance analytics?

     • Type: Scenario-Based

     • Expected Answer: Use partitioning and MySQL HeatWave.

153. What is the difference between a physical and logical data model?

     • Type: Conceptual

     • Expected Answer: Physical models define storage, logical models focus on business rules.

154. How do you implement a backup strategy for a 24/7 database?

     • Type: Scenario-Based

     • Expected Answer: Use XtraBackup for hot backups and replication.

     • Example:

       xtrabackup --backup --target-dir=/backup;

155. What is the role of MySQL’s HeatWave?

     • Type: Conceptual

     • Expected Answer: HeatWave accelerates analytics in MySQL databases.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone