This article will take you through few MySQL tips those are pretty helpful.

We will start with few useful statements,

Do you know, if you are working offline with mysql  and you need some help on syntax. You can get it right away on terminal  with simple statement  help followed by command or statement.

help

mysql> help  <COMMAND or STATEMENT>

The above statement in the mysql console will give you the whole syntax and it’s usage.

Here is  an example:

mysql> help create database
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: http://dev.mysql.com/doc/refman/5.6/en/create-database.html

For suppose you want know available list of date and time functions. Here is how you will get,

mysql> help  date and time functions;
You asked for help about help category: "Date and Time Functions"
For more information, type 'help <item>', where <item> is one of the following
topics:
   ADDDATE
   ADDTIME
   CONVERT_TZ
   CURDATE
   CURRENT_DATE
   CURRENT_TIME
   CURRENT_TIMESTAMP
   CURTIME
   DATE FUNCTION
   DATEDIFF
   DATE_ADD
   DATE_FORMAT
   DATE_SUB
   DAY
   DAYNAME
   DAYOFMONTH
   DAYOFWEEK
   DAYOFYEAR
   EXTRACT
   FROM_DAYS
   FROM_UNIXTIME
   GET_FORMAT
   HOUR
   LAST_DAY
   LOCALTIME
   LOCALTIMESTAMP
   MAKEDATE
   MAKETIME
   MICROSECOND
   MINUTE
   MONTH
   MONTHNAME
   NOW
   PERIOD_ADD
   PERIOD_DIFF
   QUARTER
   SECOND
   SEC_TO_TIME
   STR_TO_DATE
   SUBDATE
   SUBTIME
   SYSDATE
   TIME FUNCTION
   TIMEDIFF
   TIMESTAMP FUNCTION
   TIMESTAMPADD
   TIMESTAMPDIFF
   TIME_FORMAT
   TIME_TO_SEC
   TO_DAYS
   TO_SECONDS
   UNIX_TIMESTAMP
   UTC_DATE
   UTC_TIME
   UTC_TIMESTAMP
   WEEK
   WEEKDAY
   WEEKOFYEAR
   YEAR
   YEARWEEK

 

status

The  status statement in the mysql console will give you the quick overview about connection, mysql server and client details

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.6.27, for debian-linux-gnu (x86_64) using  EditLine wrapper

Connection id:		38
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.6.27-0ubuntu0.14.04.1 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			6 hours 4 min 6 sec

Threads: 1  Questions: 112  Slow queries: 0  Opens: 87  Flush tables: 1  Open tables: 80  Queries per second avg: 0.005

Pager

Are you frustrated with querying lot of records to analyze in mysql conventional command line client console? No worries,  can set the pager in mysql client console. You can set it to either less or more. Here is the way to do it,

mysql>\P less
PAGER set to 'less'
mysql>\P more
PAGER set to 'more'

Let’s say if you set pager to less, you will see your query output like wise when you read file using less command. You try the following statements.

mysql>\P less
mysql>show status;

Reset pager:

you can reset your pager using \P with arguments;

mysql>\P

Processlist

The following statement show processlist will give you overview about all connected clients and their work, even queries they are being executed,

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 38 | root | localhost | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

explain

An other statement which very helpful in optimizing your queries and schema is explain . You can’t imagine how helpful this statement is, it will show how mysql is using indexes how many rows or records it’s searching for a result. Based on the report you can optimize the queries and even you can add indexes to table if required to speed up results.

Here is simple example,

mysql> EXPLAIN SELECT t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') ORDER BY t.name ASC;
+----+-------------+-------+--------+---------------------------+------------------+---------+---------------------------+------+-----------------------+
| id | select_type | table | type   | possible_keys             | key              | key_len | ref                       | rows | Extra                 |
+----+-------------+-------+--------+---------------------------+------------------+---------+---------------------------+------+-----------------------+
|  1 | SIMPLE      | t     | ALL    | PRIMARY                   | NULL             | NULL    | NULL                      |    1 | Using filesort        |
|  1 | SIMPLE      | tt    | eq_ref | term_id_taxonomy,taxonomy | term_id_taxonomy | 138     | wordpress.t.term_id,const |    1 | Using index condition |
+----+-------------+-------+--------+---------------------------+------------------+---------+---------------------------+------+-----------------------+
2 rows in set (0.00 sec)

You will see a record for each table from which you are selecting or used in query. Find more about explain here

 

Other than few handy statements explained above, now we will go through few tips to avoid common pit falls in MySQL.

• Always try to avoid creating the user with wildcard host % . Doing so will mitigate the possible risks of getting hacked.

• Only give required permissions to the user, so other databases or things will not get screwed up either intentionally or accidentally because of user or script.

• When you are deleting something from production or from important place, use transactions. Here is how you will do it,

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> -- do you work like deleting records and other stuff
mysql> delete from users where id in (1, 3, 4);
mysql>commit;

This is useful to avoid common problems caused by either typos or accidental mistakes. Before you type commit, you can always review your changes and rollback if changes are unintended.

ysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> -- do you work like deleting records and other stuff
mysql> delete from users;  -- lets say you forgot where  in delete query
mysql>rollback;   -- You can rollback your accidental mistake