MySQL: Tips and Tricks

MySQL is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. MySQL is especially popular on the web. It is one of the parts of the very popular LAMP platform. Linux, Apache, MySQL and PHP. MySQL database is available on most important OS platforms. MySQL comes in two versions: MySQL server system and MySQL embedded system.

Tips and Tricks in MySQL

Create Relationships between MySQL Tables

There are three forms of table relationships: one-to-one, One-to-Many (O2M), and Many-to-Many (M2M). My analogy for O2M is it is a parent-children relationship where one parent can have many children but not the other way around. Thus, always remember putting the Foreign Key (FK) in the children (many) table. For M2M, you just need to create a lookup table.

Using MySQL Joins

MySQL joins are the most important thing in relational databases. They are used to join two or more different tables on a point in which both the tables match the same value and property. So, if you have an application with numerous JOIN queries, the columns you by have to be indexed on both of your tables. Make sure that the columns you join are the same type, including the character encoding for string columns. Otherwise, MySQL will try to do a full table scan. So anytime you need to pull information from your tables, just modify the queries to only retrieve user info from particular columns.
MySQL supports the following types of joins:

  1. Inner Join
  2. Left Join
  3. Right Join
  4. Cross Join

Optimize Your Queries For the Query Cache

Most MySQL servers have query caching enabled. It’s one of the most effective methods of improving performance, that is quietly handled by the database engine. When the same query is executed multiple times, the result is fetched from the cache, which is quite fast.

The main problem is, it is so easy and hidden from the programmer, most of us tend to ignore it. Some things we do can actually prevent the query cache from performing its task.

// query cache does NOT work
$r = mysql_query(“SELECT username FROM user WHERE signup_date >= CURDATE()”);

// query cache works!
$today = date(“Y-m-d”);
$r = mysql_query(“SELECT username FROM user WHERE signup_date >= ‘$today'”);

The reason query cache does not work in the first line is the usage of the CURDATE() function. This applies to all non-deterministic functions like NOW() and RAND() etc… Since the return result of the function can change, MySQL decides to disable query caching for that query. All we needed to do is to add an extra line of PHP before the query to prevent this from happening.

Use NOT NULL If You Can

Unless you have a very specific reason to use a NULL value, you should always set your columns as NOT NULL.

First of all, ask yourself if there is any difference between having an empty string value vs. a NULL value (for INT fields: 0 vs. NULL). If there is no reason to have both, you do not need a NULL field. (Did you know that Oracle considers NULL and empty string as being the same?)

NULL columns require additional space and they can add complexity to your comparison statements. Just avoid them when you can. However, I understand some people might have very specific reasons to have NULL values, which is not always a bad thing.

Index, Unique, Primary Keys and Group By

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.

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

Need to develop a website or app that represent your business? Experts at solace are there to help you for web development having large having large amount of data. They are trained in tricks of MySQL for effective development with large data. Contact us for web development that helps your business to grow.

Related Post