# Site optimization

As I wrote before, friendship-bracelets.net was turned off by my web host because of too much load on the server. Here is some things I’ve done to fix this. It can be a good guide for others to use in any application for more optimized PHP code.

## 1. Reduce # of SQL queries

Use as few SQL queries as possible. If you can use one query instead of two, do it. By using the JOIN command in Mysql, you can combine your SELECT results from different tables and reduce your # queries.

When I implemented the site some years ago, I didn’t think about optimization at all. For example, to get 10 usernames from the database I did

SELECT id FROM users LIMIT 0,10

…to get the ID’s of the users (every table I have is populated with an “id” column). After this, I did

SELECT * FROM users WHERE id=1
SELECT * FROM users WHERE id=2
SELECT * FROM users WHERE id=3

…and so forth for all 10 users to get their username. Why? Because the interface in PHP became so nice looking. It made things fast to implement.

The above queries selects all the information from the users (email, encrypted password, information etc) just to get their username. It also does 11 queries for this, which slows things down.

Anyway, I’ve replaced such code with their smarter equivalent, in this case,

SELECT username FROM users LIMIT 0,10

This is just one query and it selects nothing but the information asked for.

## 2. Compress images

I made a quick fix when it comes to images, too. In the newsfeed there is small images called thumbnails. The thing is… They were not thumbnails, they were the original images. Stupid but quick fix that doesn’t matter if you have just a few number of visitors that all have good internet connection.

Using the original images everywhere is stupid, it uses more than 10 times of what you really need. I implemented a module in PHP for compressing images and handling thumbnails to solve this problem.

## 3. Limit the use of PHP’s image functions

These image functions in PHP are heavy for the server. By limiting the use of them, my server went from really slow to really fast. At first, I turned them all off to be sure that they were a performance culprit. Then I implemented functions for limiting the use of these.

Friendship-bracelets.net contains many images generated by these functions. To generate one of these images, a call to a script is done. Let’s call it generate.php. This function was called on demand in my old version. Anyone could run the script 10 times in a second and crash the server. I didn’t think of this of course. In my new version, I first check if the target image file already is generated. If it is, generate.php will exit without doing the hard work. This ensures the server load to some extent, but I want to be sure. Therefore, the time of the last image generating is saved i a database. When generate.php starts, it checks if this value is more recent than 5 seconds. If it is, it exits. Now the script is abuse-free and will not generate images too often. Awesome.

## 4. AJAX and other client-side applications

Instead of clicking a link and updating the HTML page, the user can (in many cases) do equivalent things using JavaScript, AJAX and other client-side applications (like Flash). This saves A LOT of bandwidth.

## 5. Compress files that are loaded often

Did you know that you can compress JavaScript and CSS files? Well, you can. Do it as often as you can, because they are loaded very often. Below are two random compressors found by Google.

Compress CSS here:
http://www.cssdrive.com/index.php/main/csscompressor/

Compress JavaScript here:
http://jscompress.com/