image
icon

WEB HOSTING
Starting at $4.95/mo

Get your website going today and SAVE!

Web Hosting Accounts Start at $4.95/mo plus get FREE Domain Registration.

More

image image
icon

WEB HOSTING SPECIALS

Get FREE Domain Registration now with your web hosting account and Check out The Marketing Package!

More specials and deals found here.

More

image image
icon

WEB MARKETING

Have a business? Its time to get your business out there! Check out The Marketing Package. Its everything you need to start building traffic and customers today.

More

image

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