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
|
1 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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,
|
1 2 3 4 |
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.
|
1 2 |
mysql>\P less mysql>show status; |
Reset pager:
you can reset your pager using \P with arguments;
|
1 |
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,
|
1 2 3 4 5 6 7 |
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,
|
1 2 3 4 5 6 7 8 |
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,
|
1 2 3 4 5 6 |
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.
|
1 2 3 4 5 6 |
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 <strong>where</strong> in delete query mysql>rollback; -- You can rollback your accidental mistake |