Showing table columns ordered by name

Here is SQL syntax for showing table columns ordered by name:

MySQL Data Engines comparison - MyIsam Vs Innodb Vs Memory

Feature	MyISAM	InnoDB	Memory
ACID Transaction ACID - Atomicity, Consistency, Isolation, Durability (read more on it here: http://en.wikipedia.org/wiki/ACID)	No	Yes	No
Configurable ACID Properties	No	Yes	No
Crash Safe	No	Yes	No (RAM)
Foreign Key Support	No	Yes	No
Multi Version Concurrency Control (MVCC)	No	Yes	No
Geospatial datatype	Yes	Yes	No
Geospatial indexing	Yes	No	No
Full-text Search Index	Yes	No	No
Data Cache	No	Yes	N/A
Compressed Data	Yes	Yes	No
Storage Limits	256TB	64TB	RAM
Storage Cost	Low	High	N/A
Memory Cost	Low	High	Medium
Locking Granularity	Table	Row	Table

MyISAM:
The MyISAM storage engine in MySQL.

• Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.

Skipping constraint checks in MySQL

Error
Cannot delete or update a parent row: a foreign key constraint fails

Issue:
mysql> update User SET id=6 where id = 8;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`database_name`.`table_name`, CONSTRAINT `FK_35jtu0049tkg8m9twhc03ii9` FOREIGN KEY (`customer`) REFERENCES `user` (`id`))

Solution:
SET foreign_key_checks = 1;
; execute code with constraint restrictions here
SET foreign_key_checks = 0;

Optimizing queries with EXPLAIN

The explain command gives information about indexes which are used for the query, so you could check, whether it is optimized as you expected.

Modify primary key syntax

MySQL:

ALTER TABLE your_table DROP PRIMARY KEY, ADD PRIMARY KEY ( some_field, other_field );


There is no problem some_field to be AUTO_INCREMENT.

kickstart commands for mysql (create database & user)

mysql -u root -p --port=3306

CREATE DATABASE database_name;

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';

GRANT SELECT,INSERT,UPDATE,DELETE,DROP,CREATE       ON database_name.* TO 'user1'@'localhost';
GRANT SELECT,INSERT,UPDATE,DELETE,DROP,CREATE,ALTER,INDEX ON database_name.* TO 'user1'@'localhost';

FLUSH PRIVILEGES;


(mysql database) UPDATE user SET Password=PASSWORD('new_pass') WHERE User='username';

(mysql database) UPDATE user SET User='username_new' WHERE User='username_old';


For everyone host use '%' instead of 'localhost'