Database Optimization

Posted: January 29, 2010 in Uncategorized

Target Visitors: Beginners & Intermediate

Basically, you need to increase the query execution time, so that DB will be optimized. In PHP, SQL Fetch cycle is 99% responsible for PLT (Page Load Time).

1. If you have plenty of records, then make the table engine MYISAM. Implement index type “FULL TEXT SEARCH”, when ever possible. But, do not use multiple full text search, in case, any column (with datatype TEXT obviously) is empty or duplicate it self several times.

2. Let say, you search query taking 2-3 columns to search i.g, where name=$name and age=$age…like this. In this situation, you can make a conjugate key using “name” & “age” column.

3. If you are using Stored Procedure or function, then it’s a nice idea but I will not recommend it, as MYSQL is still amateur in security level.

4. Use BTREE indexing for making conjugate keys.

5. Do not make conjugate keys on more than 5 columns of a table. You can notice that, improper indexing will push into mess & the site will become more slower. But indexing in a proper way will increase the PLT surprisingly.

To test, if you are using “PHP MYADMIN”, then run a query & note the fetch time. Again, notice it after indexing. You will found a surprising result.

Example to make conjugate indices;

ALTER TABLE `db_name`.`table_name` ADD INDEX `index_name(`column1`, `column2`);

Recommended Mysql GUI Tools:
1. Mysql Administrator (Best for making indices, procedures & functions)
download it from;
It’s free & easy to use.

2. EMS SQL MANAGER. It’s my favourite one.
Download it from;

3. Navigat MYSQL
Download it from;


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s