• Contact
• Support FAQs
• Resources
• Front Page Users

MySQL tricks and tips

MySQL is an extremely stable, highly popular open source database that ranks high on speed rating. It is the default choice for most web hosts to provide to their customers because of its ease of deployment and use.

However, a good database can only be as good as it's administrator is. Of course not all of us are seasoned database administrators so this article is meant for novice and not so novice users. It provides some simple tricks and tips to enhance the performance of your database.

• If you would like to use Foreign keys then use table type = InnoDB which supports foreign keys. This feature is supported in mySQL version 3.23.44 and upwards.

A simple example is

CREATE TABLE parent(id INT NOT NULL,

PRIMARY KEY (id)

) TYPE=INNODB;

CREATE TABLE child(id INT, parent_id INT,

INDEX par_ind (parent_id),

FOREIGN KEY (parent_id) REFERENCES parent(id)

ON DELETE CASCADE

) TYPE=INNODB;

• Seek time is the time required for a disk to find a piece of data. This is a bottleneck of the disk and can be improved by distributing data over many disks.
• To reset the auto increment value if you have deleted rows and want to add new ones, use the command : TRUNCATE TABLE (TABLENAME)
• When creating a new column in your database table, use the smallest data type that will hold the information you expect to store. Most of the time, using INT is overdoing it as MEDIUMINT, SMALLINT, or TINYINT have enough range. Using a smaller data type saves space and speeds things up.
• Declaring columns as NOT NULL, unless you specifically plan to store NULL values is advised as it will save space and speed things up.
• Date format can be done on the fly by using the built in mySQL function DATE_FORMAT.

Back to Tech Support Topics