DBMS Skills: Mysql Database Optimization Best Practices

Database operations often becomes headache for most web developers of today. It is not only the database administrator that have to be worried about adopting the best performance issues when build or interacting with database. We as programmers who will be building web applications day in day out need to do our part by putting the structures of the tables properly, optimized queries should be written with better code.

I built my first web application “Student Placement System” for Oyo state government as a case study in the year 2009 as a student but if I look over such project now, I will close my eye and break the laptop because I am really sure that it was badly optimized in form of database operations let alone of code maintenance which I will discuss in another topic.

Today, I’ll list few of the Mysql optimization techniques in learnt over the past years of developing web application as a programmer.

1. Optimizing the Queries from the Query Cache

Do you know that all the MySQL servers in database management system have ability to cache query that we do often.   One of the effective method of improving database performance that we always query is to be fetching it from the cache and this is super faster.

The hidden problem is that most programmers don’t know that it is easy to use and most of us tend to ignore it when writing our mysql queries. Due to laziness, Many of us has been writing queries that is disabling the query cache from working.

| Want to start an eBusiness and Grow it Globally with free IT, Legal, Internet Discounts,3 Months SME Startup Course, ePayment Integration, Biz Development Services, Free Website, Free SMS Units/Portal all done for you within 30 Days?

Start Here>> Click  >>> Start a Digital Business in Nigeria

Below two mysql queries will show you both the bad way and good way of optimizing queries using query cache.

//query cache will never work

$result=mysqli_query($mysqli,”SELECT U.surname FROM users U WHERE U.user_date >= CURDATE()”);

 

//query cache will work

$sign_up_date=date(“Y-m-d”);

$result= mysqli_query($mysqli,”SELECT U.surname FROM users U WHERE U.user_date >=’$sign_up_date’”);

The sole reason why the first query will fail for caching the sql statement is because of the usage of non-deterministic functions like curdate(),now() etc. The database engine will see the query as non-stop and it will never cache it

2. Power of LIMIT function

Whenever we want to select unique record from the database, we are always fond of just using WHERE statement since we know that it can’t fetch more than one record, but the best way and practices to improve our database operation is by using LIMIT 1, LIMIT 1 will tell the database engine to stop once a record is found that matches the WHERE statement instead of going through the whole rows when it is returning only a single record.

// bad practice

$result=mysqli_query($mysqli,”SELECT * FROM users WHERE username=’soutech’”);

 

//good and best practice

$result=mysqli_query($mysqli,”SELECT 1 FROM users WHERE username=’soutech’ LIMIT 1”);

3. Indexing the column

It is not only the primary key and unique keys that needed indexing, any fields that we are performing search operations upon should also have index placed upon it to faster the query operations.

// Bad practice is when you are performing search on username when it is not indexed

$result=mysqli_query($mysqli,”SELECT count(*) FROM users WHERE username LIKE ‘s%’”);

// Good practice is when you index the username column before performing query

$result=mysqli_query($mysqli,”SELECT count(*) FROM users WHERE username LIKE ‘s%’”);

Note: the first query can use up to 25 seconds to return result of 63285 found usernames while the second query can use only 6 seconds to return the same result

4. Using the same column types for table joining and also indexing such both columns

As a web developer who will be interacting with databases everyday, there is always a need to fetch records from more than two tables at a time and it will be bad and ugly of you writing separate sql statement per each query. Meaning that for a table of 10 , you are writing 10 different sql statements. We all know that the best practice is to write 1 sql statements by using table join alais.

But for most of us who is using the table joining method, I am afraid to inform you that you are still not doing it in the right way unless you are doing the following things.

When joining tables, always make sure that the columns that you are joining are indexed and are of the same data type otherwise, the database engine will spend a long period of time to return result. It is conventional to always follow the theory of “Indexed with same data type”

// Bad practice is when you are joining two tables to get the list of registered users based on state of origin but you didn’t index the unique/primary key using and the datatype of the two columns are different.

$result=mysqli_query($mysqli,”SELECT state_of_origin FROM users U INNER JOIN users_state US ON (U.state_of_origin=US.state_of_origin) WHERE users_uid=’$user_id’”);

The above sql statement must have indexes on both the state_of_origin in each tables and both the columns must have the same data type

5. NEVER USING RAND() FUNCTION WITH ORDER BY

Rand() function is an sql statement function that select record randomly from the pool of records and it must be ordered by a particular action.

When it is compulsory to use rand() function, always firstly know the number of records in the table and now, apply it against the rand() function so that when you are limiting the records, it will be faster unlike using ORDER BY rand() which will firstly sort the whole records inside the table before picking the single one.

//Bad practice

$result=mysqli_query($mysqli,”SELECT username FROM users ORDER BY RAND() LIMIT 10”);

 

//Best practice

 

$counter=mysqli_query($mysqli,”SELECT count(*) FROM users”); // counting total users

$total_rows=mysqli_fetch_row($counter);   // storing total users

$rand=mt_rand(0,$total_rows[0]-10);   // knowing the range to use

 

// the sorting will be done between $rand and 10

$result=mysqli_query($mysqli,”SELECT username FROM users LIMIT $rand,10”);

6. Avoiding SELECT * pitfalls

The more the record inside the table is much, the slower the query operation result will be retured. The best way to treat such bottleneck is to avoid the use of SELECT * when writing SELECT sql statement.

// Bad Practice

$result=mysqli_query($mysqli,”SELECT * FROM users ORDER BY RAND() LIMIT 10”);

 

// Good Practice

$result=mysqli_query($mysqli,”SELECT username FROM users LIMIT $rand,10”);

You can now see that it is good to always use the column to be fetch when using SELECT than selecting from the columns.

7. Always have ID column when building database tables

Primary key is a popular feature in our database table construction because it gives us a huge opportunity to have an auto-incremental facility and itself can be a primary key that most unique keys will build upon.

So each time you are building a new table, always use a primary

Want to become a Web Development Expert TODAY?

Enroll Now >>

Website Development Training in Nigeria

| Want to start an eBusiness and Grow it Globally with free IT, Legal, Internet Discounts,3 Months SME Startup Course, ePayment Integration, Biz Development Services, Free Website, Free SMS Units/Portal all done for you within 30 Days?

Start Here>> Click  >>> Start a Digital Business in Nigeria