DC Web Makers Blogs & News

15 Tricks That Any Serious MySQL Database Developer Should Know (Part 3)

Continuing from Part 2:

11. Handy Date Functions
There are a lot of MySQL date functions. Of particular interest ...

Continuing from Part 2:

11. Handy Date Functions
There are a lot of MySQL date functions. Of particular interest (from practical perspective) are DATE_ADD(), DATE_SUB(), CURRENT_DATE(), NOW(), and DATEDIFF(), so lookup the MySQL manual for details.

12. Index, Unique, Primary Keys
The Primary Key (PK) or table identifier can be an auto-increment integer or a custom unique value like the email address. The Unique key is identical to the PK, except that it accepts null values. The Index key is mainly used for speeding up the MySQL processing time, so use it only for very important fields on your table. Otherwise, it may slow down your database response time.

13. Incremental versus Periodic Backups
There are primary two ways by which you can backup a MySQL database: incremental and periodic. For the former, you need to define a binary directory path on your MySQL.ini file. Then, the MySQL will automatically backup from any INSERT, UPDATE or DELETE statements instantly (of course, at the cost of higher processing load), while saving them in the binary log on your server. However, for the latter, you should run MySQL_dump function at different intervals (preferably daily) and save the SQL backup file afterward.

14. Normal Operators
Here are the main MySQL opertators: , != , AND, OR, . When you say A is not equal to B, you can use AB or A!=B

15. Group By
Make sure you understand Group By very well before using it. Otherwise, it may return ambiguous results, especially in multi-table join queries.

There are many other minor things that I did not cover here. However, the discussed 15 tricks would be a good starting point for mastering the giant MySQL database. Last but not least, to handle the MySQL structure designs, you should know the business processes behind the table entities, like user and transaction info, or transaction info and product info, on top of your technical knowledge. Thus, there is a long journey to go.

DC Web Makers Blogs
15 Tricks MySQL Developer (Part 3)

Blog & News Details

15 Tricks That Any Serious MySQL Database Developer Should Know (Part 3)

  • Posted: Tuesday February 26th
  • Continuing from Part 2:

    11. Handy Date Functions
    There are a lot of MySQL date functions. Of particular interest (from practical perspective) are DATE_ADD(), DATE_SUB(), CURRENT_DATE(), NOW(), and DATEDIFF(), so lookup the MySQL manual for details.

    12. Index, Unique, Primary Keys
    The Primary Key (PK) or table identifier can be an auto-increment integer or a custom unique value like the email address. The Unique key is identical to the PK, except that it accepts null values. The Index key is mainly used for speeding up the MySQL processing time, so use it only for very important fields on your table. Otherwise, it may slow down your database response time.

    13. Incremental versus Periodic Backups
    There are primary two ways by which you can backup a MySQL database: incremental and periodic. For the former, you need to define a binary directory path on your MySQL.ini file. Then, the MySQL will automatically backup from any INSERT, UPDATE or DELETE statements instantly (of course, at the cost of higher processing load), while saving them in the binary log on your server. However, for the latter, you should run MySQL_dump function at different intervals (preferably daily) and save the SQL backup file afterward.

    14. Normal Operators
    Here are the main MySQL opertators: , != , AND, OR, . When you say A is not equal to B, you can use AB or A!=B

    15. Group By
    Make sure you understand Group By very well before using it. Otherwise, it may return ambiguous results, especially in multi-table join queries.

    There are many other minor things that I did not cover here. However, the discussed 15 tricks would be a good starting point for mastering the giant MySQL database. Last but not least, to handle the MySQL structure designs, you should know the business processes behind the table entities, like user and transaction info, or transaction info and product info, on top of your technical knowledge. Thus, there is a long journey to go.