mysql advance query

Show table status

show table status like 'table_name';

A better way to change storage engine

CREATE TABLE innodb_table LIKE myisam_table;
ALTER TABLE innodb_table ENGINE=InnoDB;
INSERT INTO innodb_table SELECT * FROM myisam_table;

Create user

grant select,insert,update,delete on *.* to username@"hostname" identified by "password";

Add column

alter table table_name add column <column_name> <column_option>

Flush  cache and show session status

FLUSH STATUS;

<some sql statement>

SHOW SESSION STATUS LIKE 'Select%';

SHOW SESSION STATUS LIKE 'Handler%';

SHOW SESSION STATUS LIKE 'Sort%';

SHOW SESSION STATUS LIKE 'Created%';  //temporary tables MySQL created for the query
Show profiles

//set profiling variable to 1

SET profiling = 1;

<some sql statement>

SHOW PROFILES/G

Show processlist

SHOW PROCESSLIST/G

Explain

 explain <sql statement>/G

show index

show index table_name/G

 

 

not end......