When you see your average page loading time going from 0.08 sec to 1.40 sec you know it’s time to do someting about it. I’ve run into this situation a few days ago and made some tweaks to my database. Now the average loading time is even lower at 0.05 sec. Here is how to do it.

1. Create indexes

Indexes are a way for mySQL to scan through tables very fast. The greater the number of rows, the greater the speed improvement. The mySQL manual claims that in a table with 1000 rows, an index could speed up data retrieval by 100 times.

Let’s see how it works in action. Let’s assume for example that we have a simple application that stores all books in a public library. We will need a books table and an authors table to store our data. Since an author may have written many books, and some books can have multiple authors (like many tech books) this is a many to many relationship. So we also need a books_authors table for relating books to authors. So our tables will be:

1. books

CREATE TABLE books (
  ID INT,
  title VARCHAR(255),
  ISBN VARCHAR(255),
  date_published (date)
)

2. books_authors

CREATE TABLE books_authors (
  ID INT,
  book_id INT,
  author_id INT
)

3. authors

CREATE TABLE authors (
  ID INT,
  name VARCHAR(255),
)

Primary keys are automatically indexed by mySQL so we will turn our attention to the books_authors table. The reason is that there will be a lot of work done by mySQL in that table, since it acts as the glue for the other two. Depending on the type of your select queries you may want to index other columns as well, such as the ISBN column if you want to search for books by their ISBN. However if you make the ISBN unique it will be also automatically indexed by mySQL. This is a great idea since ISBNs are unique anyway.

However, back to our example. Let’s see the code that will create the indexes.

CREATE INDEX book_id_index ON books_authors (book_id);
CREATE INDEX author_id ON books_authors (author_id);

As you may have guessed, the syntax goes like this.

CREATE INDEX index_name ON table (column);

2. Query cache

MySQL has the feature of caching select queries along with their results. To make things even better, MySQL serves the same cached results to different clients. A great advantage over classic server side HTML caching. Although query cache is on by default you have to configure the cache size in order for it to work.

To check out the size of the query cache connect to MySQL and issue the following command:

SHOW VARIABLES LIKE 'query_cache_size';

The size of query cache should depend on your server’s RAM. A nice practice is setting it to about half the memory size to allow resources for other services.

To set query cache size to 1MB just do:

SET GLOBAL query_cache_size = 1000000;

The first time a select query is executed it will run at normal speed. However consecutive queries will be returned almost instantly since they will be cached. This will speed up even the most simple applications since routine queries, like “select the 10 latest posts” will practically always be cached.

Conclusion:

These two simple tips can have an enormous impact on your web application’s performance. However you should be aware that database optimization is a huge topic and it’s worth digging deeper into it. Your next stop from here should be database normalization.

Reference:

How MySQL Uses Indexes
http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

When To Use Indexes In MySQL
http://www.howtoforge.com/when-to-use-indexes-in-mysql-databases

Query Cache Configuration
http://dev.mysql.com/doc/refman/5.1/en/query-cache-configuration.html

If you got comments/suggestions or want updates on new posts be sure to follow me on twitter.