Explain advantages of InnoDB over MyISAM?

Interview Questions

MySQL and SQL

(Continued from previous question...)

Explain advantages of InnoDB over MyISAM?

Row-level locking, transactions, foreign key constraints and crash recovery.

(Continued on next question...)

Other Interview Questions

What's MySQL
What is DDL, DML and DCL?
How do you get the number of rows affected by query?
If the value in the column is repeatable, how do you find out the unique values?
How do you return the a hundred books starting from 25th?
You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user?
How would you write a query to select all teams that won either 2, 4, 6 or 8 games?
How would you select all the users, whose phone number is null?
What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id)
How do you find out which auto increment was assigned on the last insert?
What does –i-am-a-dummy flag to do when starting MySQL?
On executing the DELETE statement I keep getting the error about foreign key constraint failing. What do I do?
When would you use ORDER BY in DELETE statement?
How can you see all indexes defined for a table?
How would you change a column from VARCHAR(10) to VARCHAR(50)?
How would you delete a column?
How would you change a table to InnoDB?
When you create a table, and then run SHOW CREATE TABLE on it, you occasionally get different results than what you typed in. What does MySQL modify in your newly created tables?
How do I find out all databases starting with ‘tech’ to which I have access to?
How do you concatenate strings in MySQL?
How do you get a portion of a string?
What’s the difference between CHAR_LENGTH and LENGTH?
How do you convert a string to UTF-8?
What do % and _ mean inside LIKE statement?
What does + mean in REGEXP?
How do you get the month from a timestamp?
How do you offload the time/date handling to MySQL?
How do you add three minutes to a date?
What’s the difference between Unix timestamps and MySQL timestamps?
How do you convert between Unix timestamps and MySQL timestamps?
What are ENUMs used for in MySQL?
How are ENUMs and SETs represented internally?
How do you start and stop MySQL on Windows?
How do you start MySQL on Linux?
Explain the difference between mysql and mysqli interfaces in PHP?
What’s the default port for MySQL Server?
What does tee command do in MySQL?
Can you save your connection settings to a conf file?
How do you change a password for an existing user via mysqladmin?
Use mysqldump to create a copy of the database?
Have you ever used MySQL Administrator and MySQL Query Browser?
What are some good ideas regarding user security in MySQL?
Explain the difference between MyISAM Static and MyISAM Dynamic.
What does myisamchk do?
Explain advantages of InnoDB over MyISAM?
Explain advantages of MyISAM over InnoDB?
What are HEAP tables in MySQL?
How do you control the max size of a HEAP table?
What are CSV tables?
Explain federated tables.
What is SERIAL data type in MySQL?
What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?
Explain the difference between BOOL, TINYINT and BIT.
Explain the difference between FLOAT, DOUBLE and REAL.
If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table?
What happens if a table has one column defined as TIMESTAMP?
But what if you really want to store the timestamp data, such as the publication date of the article?
Explain data type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do?
Explain TIMESTAMP DEFAULT ‘2006:09:02 17:38:44? ON UPDATE CURRENT_TIMESTAMP.
If I created a column with data type VARCHAR(3), what would I expect to see in MySQL table?
General Information About MySQL
The Main Features of MySQL
Database Basics
MySQL Command Interpreter
Installing a MySQL Binary Distribution
MySQL - Quick Installation Overview
MySQL - MySQL Extensions to ANSI SQL92
MySQL - Running MySQL in ANSI Mode
Functionality Missing from MySQL - Sub-selects
Functionality Missing from MySQL - SELECT INTO TABLE
Functionality Missing from MySQL - Transactions
Functionality Missing from MySQL - Stored Procedures and Triggers
Functionality Missing from MySQL - Foreign Keys
MySQL - Reasons NOT to Use Foreign Keys constraints
Functionality Missing from MySQL - `--' as the Start of a Comment
Functionality Missing from MySQL - How to Cope Without COMMIT/ROLLBACK
MySQL - General Security
How to Make MySQL Secure Against Crackers
MySQL - Startup options to mysqld which concerns security
MySQL - What the Privilege System Does
MySQL - User Names and Passwords
How to connecting to the MySQL Server
MySQL - Keeping Your Password Secure
Privileges Provided by MySQL
MySQL - How the Privilege System Works
MySQL - Access Control, Stage 1: Connection Verification
MySQL - Access Control
MySQL - When Privilege Changes Take Effect
Setting Up the Initial MySQL Privileges
Adding New User Privileges to MySQL
MySQL - Setting Up Passwords
MySQL - Causes of Access denied Errors
Replication in MySQL
MySQL - Replication Implementation Overview
MySQL - how to set up complete replication on your current MySQL server
MySQL - Replication Features and known problems
MySQL - SQL Commands Related to Replication
MySQL - Why do I sometimes see more than one Binlog_Dump thread on the master after I have restarted the slave?
MySQL - How do I rotate replication logs?
MySQL - How do I upgrade on a hot replication setup?
MySQL - What issues should I be aware of when setting up two-way replication?
MySQL - How can I use replication to improve performance of my system?
MySQL - What should I do to prepare my client code to use performance-enhancing replication?
MySQL - When and how much can MySQL replication improve the performance of my system?
MySQL - How can I use replication to provide redundancy/high availability?
MySQL - Troubleshooting Replication
How to get Maximum Performance from MySQL
MySQL - Optimization Overview
MySQL - System/Compile Time and Startup Parameter Tuning
MySQL - Disk Issues
MySQL - Using Symbolic Links for Databases and Tables
MySQL - Tuning Server Parameters
How MySQL Opens and Closes Tables
MySQL - Drawbacks to Creating Large Numbers of Tables in the Same Database
MySQL - Why So Many Open tables?
How MySQL Uses Memory
How MySQL Locks Tables
MySQL - Table Locking Issues
How MySQL uses DNS
MySQL - Get Your Data as Small as Possible
How MySQL Uses Indexes
MySQL - Speed of Queries that Access or Update Data
MySQL - Estimating Query Performance
MySQL - Speed of SELECT Queries
How MySQL Optimizes WHERE Clauses
How MySQL Optimizes DISTINCT
How MySQL Optimizes LEFT JOIN and RIGHT JOIN
How MySQL Optimizes LIMIT
MySQL - Speed of INSERT Queries
MySQL - Speed of UPDATE Queries
MySQL - Speed of DELETE Queries
MySQL - Other Optimization Tips
MySQL - Using Your Own Benchmarks
How MySQL Stores Its Row Data and Index Data?
MySQL is Portability
What Have We Used MySQL For?
What is the difference between mysql_fetch_array and mysql_fetch_object?
What are the different table present in MYsql?