Improving Database Response Time Using Index

I had an experience when our web application has taken time to load a page the gets data from database. It’s around 6 seconds before the user can see the page. When it was new, less records, everything was fast.

I did an audit to the code checking the time of every process looking for the part that is taking a lot of time. The problem was some queries are joining tables with large rows.

The solution was to create column indexes.

From around 6 seconds it is able to load around 0.200 ms now.

Consider query below:
SELECT users.* FROM users
JOIN addresses ON addresses.user_id =
JOIN cities ON = addresses.city_id
JOIN provinces ON = addresses.province_id

If it takes time to load you could consider indexing address table
ALTER TABLE addresses ADD INDEX(user_id,city_id,province_id)

Note, the order of column index in addresses table is important. First is user_id, followed by city_id then province_id as also shown in the query.

While indexing speeds the read of query, it also has negative impact on write query as indexes needs to be updated when a new row is added or modified.

Combine indexing and cache technology to further increase response time.

Unify www and non-www site

Create .htaccess inside document root and add
RewriteCond %{HTTP_HOST} ^www\.
RewriteRule ^(.*)$ https://%{HTTP_HOST}%{REQUEST_URI} [L,R=301]
RewriteCond %{HTTPS} off
RewriteRule ^(.*)$ https://%{HTTP_HOST}%{REQUEST_URI} [L,R=301]

The first RewriteRule does permanent redirect (301) to traffics if it starts with www to

The second RerwiteRule does permanent redirect (301) to traffics if it’s an http request to

Www, non-www, https and non-https are treated different site by search engines.
So it’s important to unify them for SEO purposes.


Serving web content from user’s home directory.

I had a problem getting "Forbidden You don't have permission to access /index.php on this server." after confirming all virtual host settings and folder permission are correct.

I’ve set $ sudo chown -R apache.apache /home/anthony/ and $ sudo chmod -R 755 /home/anthony/ but I still get the error.

When I looked /home, $ cd /home $ ls -l I saw the problem, anthony has 700 permission,
drwx------ 3 anthony anthony 115 Feb 7 22:54 anthony
so apache can’t get through anthony directory. My solution is to make anthony directory 750 sudo chmod 750 /home/anthony and add apache user to anthony group sudo usermod -a -G anthony apache

Then restart apache
sudo service httpd restart

That solved the problem and I can now see my website.

Below is my VirtualHost Config

<VirtualHost *:80>
    DocumentRoot /home/anthony/
    <Directory “/home/anthony/”>
        AllowOverride None
        # Allow open access:
        Require all granted
    <Directory “/home/anthony/”>
        AllowOverride All
        Options Indexes FollowSymLinks
        Require all granted


Integrate Laravel and Chat Server using WebSocket

Create a file chat-server.php
use Ratchet\Server\IoServer;
use Ratchet\Http\HttpServer;
use Ratchet\WebSocket\WsServer;
use App\Http\Chat;
require 'vendor/autoload.php';
//load laravel framework
require __DIR__.'/bootstrap/autoload.php';
$app = require_once __DIR__.'/bootstrap/app.php';
$kernel = $app->make(Illuminate\Contracts\Http\Kernel::class);
$response = $kernel->handle(
$request = Illuminate\Http\Request::capture()
$server = IoServer::factory(
new HttpServer(
new WsServer(
new Chat()
'' //allow none-localhost ip

App\Http\Chat.php is a class that implements Ratchet\MessageComponentInterface. Detailed implementation is found here

To run, open commandline and type $ php chat-server.php If you update the file and related to it make sure to restart the chat server using Ctrl+C then running the command again.

If you encounter “Could not find driver” while using chat-server.php that means there is no enabled mysql driver in php terminal. You can find solutions here

Add Virtual Host to Xampp for

In C:\xampp\apache\conf\extra\httpd-vhosts.cnf file add new VirtualHost block

DocumentRoot "C:\xampp\htdocs\app_folder"

DocumentRoot "C:/xampp/htdocs"
ServerName localhost

Then restart apache

Open hosts file, in Windows it’s located in C:\Windows\System32\drivers\etc (make sure to show all files) and append

Save and close hosts file

You should be able to browse which loads the content from your disk

DateTime() is better than strtotime()

I’m using Windows 64 bit and PHP 5.6

strtotime() can’t go beyond 2038 year

$number_of_years = 2;
$start_date = '2037-12-31';
$end_date = strtotime('+' . $number_of_years . ' year', strtotime($start_date));
$end_date = strtotime('-1 day', $end_date);
echo 'strtotime() Result: '.date('Y-m-d', $end_date);

echo "<br/>";

$d = new DateTime($start_date);
$d->modify("+".$number_of_years." year");
$d->modify("-1 day");
echo 'DateTime() Result: '.$d->format('Y-m-d');


strtotime() Result: 1969-12-31
DateTime() Result: 2039-12-30

Usage of DateTime() is also easier.

Run Cron Job on Intervals

#Minutes Hours Day of Month Month Day of Week Command
#0 to 59 0 to 23 1 to 31 1 to 12 0(Sunday) to 6(Saturday) Shell Command
#run every 15 minutes
*/15 * * * * curl >/dev/null 2>&1

#run every 3 hours
0 */3 * * * curl >/dev/null 2>&1

#run every after 2 days at 1AM
0 1 */2 * * curl >/dev/null 2>&1

#run every after 2 months on day 1
0 0 1 */2 * curl >/dev/null 2>&1

#run every Tuesday,Thursday,Saturday at 1AM
0 1 * * 2,4,6 curl >/dev/null 2>&1

>/dev/null 2>&1 suppresses the output of curl

Doing Daily Remote Backup

Scenario 1

I have 2 remote servers, Server1 is live and Server2 is backup.
Server1 is already live and cron job is not running. I don’t want to install cron job in Server1 for some reasons.
Server2 has cron job running and is going to connect to Server1 and execute the backup script every 1 AM.

Open crontab in Server2
$ crontab -e
Press Insert button in the keyboard to edit.
#Minutes Hours Day of Month Month Day of Week Command
#0 to 59 0 to 23 1 to 31 1 to 12 0 to 6 Shell Command

Append the command to run
0 1 * * * ssh user@Server1 sh /mnt/extradisk/
Press ESC button to quit editing, type :wq! press Enter to save changes and quit editing.

Below is the script for
weekday=$(date +"weekday_%u")
mysqldump -u user -ppassword --all-databases | gzip > $file
scp -P 10022 $file user@Server2:~/folder-daily-backups/
tar -cpzf $domain -C / usr/share/glassfish3/glassfish/domains
scp -P 10022 $domain user@Server2:~/folder-daily-backups/

The above script dumps all mysql databases and zip them into a file.
It also backups glassfish files and zip them.
Both zips are copied from Server1 to Server2 for remote backup.

Scenario 2

Both servers have running cron job.

Server1 is going to execute it’s backup script every after 3 hours
0 */3 * * * sh ~/backups/ code below
hour=$(date +"hour_%H")
mysqldump -hipaddress -u user -ppassword database | gzip > $file

Server2 will get Server1’s backups every 1 AM
0 1 * * * scp -P 10022 user@Server1:~/backups/* ~/BACKUPS/project/

Note: Server1 has Server2’s public key in its authorized_keys, vice versa.

$ date --help
to see more date formats

Cleaning Malicious Scripts Injected in WordPress PHP files

Note: This only works if the scripts are injected to the first line of every PHP files.

First backup the files
$ tar -zcvf public_html.infected.tar.gz public_html

Then go inside public_html to execute the code there
$ cd public_html

Find all files with .php extention and execute the sed command.
sed will do an infile search and replace. -i will backup the file to be edited and add .infected suffix.
'1 s/.*/<?php/' does search in first line from .* (means all of first line) and replace with <?php

Note: There’s a possibility that the site may function, so be ready to fix it.
The problem I encountered with this is that few php files that only have html contents are having errors bec of <?php in the first line.
Check the server log for details errors.

Another error I encountered from wordpress pages is that <?php get_header();?> in the first line is replaced with <?php

Execute the cleanup code below
$ find . -type f -name "*.php" -exec sed -i.infected '1 s/.*/<?php/' {} \;

Check the command has no adverse effect on the site.
After checking the site is still working, delete infected files

$ find . -type f -name "*.infected" -delete