MySQL Tips

MySQL tips for beginners and intermediate

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

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

Here is  an example:

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

 

status

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

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,

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.

Reset pager:

you can reset your pager using \P with arguments;

Processlist

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

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,

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,

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.

Leave a Reply

Your email address will not be published. Required fields are marked *