Difference between InnoDB and MyISAM storage engine in MySQL

InnoDB: A transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. InnoDB is the default storage engine as of MySQL 5.5.5.

MyISAM: The MySQL storage engine that is used the most in Web, data warehousing, and other application environments. MyISAM is supported in all MySQL configurations, and is the default storage engine prior to MySQL 5.5.5.

For example, customer bank records might be grouped by customer in InnoDB but by transaction date with MyISAM, so InnoDB would likely require fewer disk seeks and less RAM to retrieve and cache a customer account history

The major differences between these two storage engines are :

  • InnoDB supports transactions which is not supported by tables which use MyISAM storage engine.
  • InnoDB has row-level locking, relational integrity i.e. supports foreign keys, which is not possible in MyISAM.
  • InnoDB ‘s performance for high volume data cannot be beaten by any other storage engines available.
Tables created in MyISAM are known to have higher speed compared to tables in InnoDB. But since InnoDB supports volume, transactions, integrity it’s always a better option which you are dealing with a larger database. A single database can have tables of different storage engines.
To check engines you can use command:
mysql> Show engines;
You can change the engine while creating the table by command:
CREATE TABLE test name varchar(30) ENGINE = InnoDB;
It is also possible to convert from one engine to the other by command:
ALTER TABLE my_table ENGINE=new_engine;
When you will execute the above command, complete process will be as such – the table will get locked, dumped to a tmp space, then rebuilt with the new engine. Also you will be losing innodb-only info (foreign keys, etc.) and features if you’re going to MyISAM.
Advertisements

How To Create a New User and Grant Permissions in MySQL

About MySQL

MySQL is an open source database management software that helps users store, organize, and later retrieve data. It has a variety of options to grant specific users nuanced permissions within the tables and databases—this article will give a short overview of a few of the many options.

How to Create a New User

The default user in MySQL is ‘root‘ user, that has full access to all of the databases. However there are ways to create users with custom permissions.

Let’s start by making a new user within the MySQL shell:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Sadly, at this point newuser has no permissions to do anything with the databases. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell.

Therefore, the first thing to do is to provide the user with access to the information they will need.

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

The asterisks in this command refer to the database and table (respectively) that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables.

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

FLUSH PRIVILEGES;

Your changes will now be in effect.

How To Grant Different User Permissions

Here is a short list of other common possible permissions that users can enjoy.

    • ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
    • CREATE- allows them to create new tables or databases
    • DROP- allows them to them to delete tables or databases
    • DELETE- allows them to delete rows from tables
    • INSERT- allows them to insert rows into tables
    • SELECT- allows them to use the Select command to read through databases
    • UPDATE- allow them to update table rows
    • GRANT OPTION- allows them to grant or remove other users’ privileges

To provide a specific user with a permission, you can use this framework:

 GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name.

Each time you update or change a permission be sure to use the Flush Privileges command.

If you need to revoke a permission, the structure is almost identical to granting it:

 REVOKE [type of permission] ON [database name].[table name] TO ‘[username]’@‘localhost’;

Just as you can delete databases with DROP, you can use DROP to delete a user altogether:

 DROP USER ‘demo’@‘localhost’;

To test out your new user, log out by typing

 quit

and log back in with this command in terminal:

mysql -u [username]-p

Source: Digitalocean.com

Get The Sequence Number of Rows In The Result of SELECT Query [MySQL]

ORACLE database provides ROWNUM to keep track of the no of rows produced in SELECT query. But in MySQL there is no default ROWNUM functionality. Here is a way how to do it.

ORACLE Implementation:


select rownum, name, sal from emp1;

ORACLE O/P:

ROWNUM NAME SAL
1 a 10000
2 b 5000
3 c 2000
4 d 1000
5 e 7000
6 f 4000

MySQL Implementation:


select @rownum:=@rownum+1 rownum, name, sal from emp1, (SELECT @rownum:=0) r;

MySQL O/P:

rownum name sal
1 a 10000
2 b 5000
3 c 2000
4 d 1000
5 e 7000
6 f 4000

So here in MySQL you need a variable to implement the ROWNUM feature of ORACLE.

CHANGE Vs. MODIFY in ALTER TABLE Command

Whenever I have to change a column in MySQL (which isn’t that often), I always forget the difference between ALTER COLUMN, CHANGE COLUMN, and MODIFY COLUMN. Here’s a handy reference.

ALTER COLUMN

Used to set or remove the default value for a column. Example:

ALTER TABLE MyTable ALTER COLUMN foo SET DEFAULT 'bar';
ALTER TABLE MyTable ALTER COLUMN foo DROP DEFAULT;

CHANGE COLUMN

Used to rename a column, change its datatype, or move it within the schema. Example:

ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL FIRST;
ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL AFTER baz;

MODIFY COLUMN

Used to do everything CHANGE COLUMN can, but without renaming the column. Example:

ALTER TABLE MyTable MODIFY COLUMN foo VARCHAR(32) NOT NULL AFTER baz;

The official documentation for ALTER TABLE (for MySQL 5.1) is here.