Tuesday, December 22

Tuesday, December 22 – The Australian share market surged to a two-week high thanks to stronger offshore markets and a reduction in profit taking on US dollar carry trades that has restrained equities and commodities for the past few weeks.
The All Ordinaries rose 64.9 points (1.4%) to 4,724.4 while the S&P/ASX 200 firmed 69.1 points (1.5%) to 4,712.8.
BHP (BHP)
BHP said it has no plans to exit its nickel business, countering a media report that it could be considering selling mines in Australia and Colombia. “We have absolutely no plans to exit nickel,” a spokeswoman for BHP said. BHP strengthened 70 cents (1.71%) to $41.65.
Newcrest (NCM)
Cyclone Laurence has made landfall in Australia’s eastern Pilbara region morning, forcing the closure of Newcrest Telfer gold mine, while major iron ore export terminal Port Hedland has reopened. “At this stage, production is expected to resume during the day shift on Wednesday,” Newcrest said. NCM lost 72 cents (2.07%) to $34.15.
News Corp (NWS)
News Corporation announced it has agreed to subscribe to up to 49m new registered shares in Sky Deutschland AG via a capital increase that is expected to raise EUR110 – 120m. As a result, the company’s stake in Sky Deutschland will increase from its current position of 39.96% up to a maximum of 45.4%. The issue price of the new shares will be the greater of EUR2.25 or the volume-weighted average price on XETRA over the 5 trading days prior to the resolution of the capital increase less a discount of 3%. The capital increase is expected to occur in the course of January 2010. NWS rose 42 cents (2.4%) to $17.89.
Fortescue (FMG)
Fortescue said it has reached a settlement over a shipping dispute, agreeing to pay SK Shipping Europe US$20.34m in return for ending its legal proceedings against the miner. The company said it had agreed to pay SK Shipping US$10.17m for each of the two suspended ship charter contracts that are in dispute. As part of the settlement, Fortescue has restructured its future cargo obligations to SK Shipping and agreed freight rates at a base daily rate plus a hire incentive payment and profit share arrangement. Fortescue said it has now struck settlements with four counterparties with which it has had disputes and expects the total damages from all the disputes to be in line with previous guidance of about US$171m. FMG strengthened 4 cents (0.92%) to $4.37.
Foster’s Group (FGL)
Foster’s Group said Managing Director Alex Stevens, who oversees the Australian-Pacific beer business, has resigned due to ill health. Chief Executive Ian Johnston will act as managing director for the unit until further notice. FGL gained 10 cents (1.87%) to $5.46.
GPT Group (GPT)
GPT said its board has appointed former Australand Property Group Chief Executive Brendan Crotty as a director immediately, subject to investor ratification at their annual meeting next year. The company said Chairman Ken Moss won’t stand for re-election at the meeting and the board has named Deputy Chairman Robert Ferguson as his successor, subject to investors’ ratification as a director. GPT rose 1 cent (1.75%) to $0.58.
Asciano (AIO)
Asciano refuted suggestions by an Australian state finance minister that it entered into discussions earlier this year with QR, its main competitor in Queensland, to sell its coal haulage business. Asciano said that while it did have discussions with QR as part of its review of recapitalisation options before its $2.45bn capital raising in mid-2009, these were to discuss a potential joint venture in interstate intermodal rail. The firm said it was disappointed that the confidential discussions had been made public by the Queensland Treasurer Andrew Fraser, “in a way that has the potential to mislead or confuse.” AIO lost half a cent (0.29%) to $1.715.
Boral (BLD) and Crown (CWN)
Boral said that Chief Financial Officer Ken Barton has resigned from the company to take up a similar role at Crown. A formal process to identify and appoint a new chief financial officer is underway and Barton will remain in his role until early March to help with a smooth transition to a newly appointed officer, it said. BLD strengthened 14 cents (2.55%) to $5.64 while CWN lost 1 cent (0.13%) to $7.71.
Nufarm (NUF)
Sinochem has revised down its proposed takeover offer for Nufarm to $12 a share from $13, valuing it at $2.62bn, Nufarm said. Nufarm Chairman Kerry Hoggard said the company is disappointed that Sinochem hasn’t proceeded with the proposed purchase on the basis that was previously agreed. “The Nufarm Board has strong confidence in the future growth and success of the company and isn’t prepared to support a proposal that undervalues the business,” he said. NUF was placed in a trading halt with a last price of $10.56.
GrainCorp (GNC)
GrainCorp said it expects earnings before interest, tax, depreciation and amortisation this fiscal year ending 30 September 2010 in a range of $180m to $210m. The contribution from the newly-purchased malt unit to this earnings measure will be in a range $100m to $120m, the company reported. Managing Director Mark Irwin said drought in Queensland and New South Wales had cut receivals from a harvest of winter grains, but receivals from the harvest in Victoria are meeting budget expectations. GNC weakened 43 cents (7.4%) to $5.38.
The NZSX50 rose 29.38 points (0.93%) to 3,179.18 while the Nikkei added 144.17 points (1.42%) to be last quoted at 10,328.20 and the Hang Seng strengthened 243.38 points (1.16%) to 21,191.48.
The Australian dollar was last quoted at US$0.8789.

Australian Market, Monday 21 Dec 09

Monday, December 21 – The Australian share market finished a touch weaker in sleepy pre-Christmas trading after testing the upper end of its short-term range on the back of slightly positive leads from Wall Street.

The All Ordinaries fell 11.6 points (0.25%) to 4,660.3 while the S&P/ASX 200 weakened 12.8 points (0.28%) to 4,637.7.

BHP (BHP)

Tropical Cyclone Laurence forced the closure of a major iron ore export port in the Pilbara region of Western Australia as BHP warned of possible interruptions to its mining operations in the region due to the storm. “We expect that there may be some production interruption at some operations due to heavy rainfall but otherwise we are mainly keeping watch on the cyclone’s path at this stage,” a BHP spokeswoman said. BHP gained 35 cents (0.86%) to $40.95.

Qantas (QAN)

Qantas said for the first time that it expects to return to profitability this reporting period after on-year improvements in passenger numbers and average ticket prices. Qantas said it expects profit before tax of between $50m and $150m for the six months ended 31 December. QAN rose 14 cents (5.11%) to $2.88.

Asciano (AIO)

Asciano said it has completed a major $2.9bn debt repayment and entered into three new facilities totalling another $1.14bn, and confirmed it would not pay an interim distribution this fiscal year. The new facilities include an undrawn $500m revolving credit facility and $140m working capital facility that both mature in December 2013, and a $500m term loan maturing in December 2014. AIO fell 5 cents (2.83%) to $1.72.

Lend Lease (LLC)

Lend Lease said it will pay the New South Wales state government “several hundred” million Australian dollars for the right to develop the $6bn Barangaroo project on Sydney’s waterfront. Under a conditional contract, Lend Lease said it will make a series of payments totalling several hundred million dollars over eight years, including about $100m in the first 18 months. Lend Lease said construction on the 7.5 hectare site is expected to begin in late 2010 and the project will take 10-15 years to complete.

In other news, Lend Lease said that the company has closed its new wholesale fund, Lend Lease Real Estate Partners 3, after receiving $218m in commitments from investors. The fund will have capacity to invest more than $350m, with targeted gearing of between 30% and 50%, Lend Lease said. It also is expected to participate in the Lend Lease-led consortium as the preferred bidder for the $1.4bn ING Retail Property Fund, the company said. LLC rose 12 cents (1.29%) to $9.42.

Alumina (AWC)

Alumina said it will contribute about US$120m toward the development of a US$10.8bn aluminium project in Saudi Arabia. The deal will see Alcoa and Alumina provide alumina for the first stage of the project, due to come on line in 2013, with a bauxite mine and alumina refinery to be developed in the second phase. Alumina said it will consider a variety of debt funding options for its equity contribution, which will be contributed progressively between 2010 and 2014. AWC strengthened 1.5 cents (0.88%) to $1.715.

Arrow (AOE)

Arrow said that it has acquired a 35% stake in a Chinese coal seam gas block from Fortune Oil for US$13.3m and has an option to increase its holding to 75%. Arrow said the acquisition price includes US$6m of funds for a 2010 work program including the drilling of horizontal pilot wells. The block has proven, probable and possible reserves of 89 petajoules, Arrow said. AOE remained unchanged at $3.95.

UGL (UGL)

UGL said it has been awarded a contract worth $225m to build and maintain a communications network for the New South Wales state rail network. UGL said the project with RailCorp included the design, supply, installation and maintenance of a digital train radio system to cover 1,455km of rail track and 70km of tunnels across the Sydney metropolitan network, including the fit out of 675 train cabs with new radio equipment. UGL fell 7 cents (0.49%) to $14.16.

Economic News

APRA

The Australian Prudential Regulation Authority (APRA) issued proposed changes to prudential standards of banks to enhance the Basel II framework in Australia. APRA Chairman John Lake said the proposed changes aren’t expected to have a significant impact. Among the proposed changes to prudential standards, APRA wants higher capital requirements to capture the credit risk of complex trading activities. It also called for higher risk weights for so-called resecuritisation exposures to better reflect risk inherent in the products. The proposals also include increased credit conversion factors for short-term liquidity facilities provided to off-balance sheet conduits. The consultation package is a response to measures released in July 2009 by the Basel Committee on Banking Supervision to enhance the framework, APRA said.

The NZSX50 dropped 4.42 points (0.14%) to 3,149.81 while the Nikkei rose 41.42 points (0.41%) to be last quoted at 10,183.47 and the Hang Seng shed 135.69 points (0.64%) to 21,040.19.

The Australian dollar was last quoted at US$0.8843.

Cron Task For Automated Backups

What we’re going to cover in this tutorial, is how to set up a cron job on a Centos 5 linux server. The basic problem I had, was that our server’s /backup/cpbackup/ folder was getting filled with (automated) backups very fast, taking up tonnes of disk space very quickly (only 7.5GB total available on this VPS! – perhaps time to look for a better one?). We’ll learn how to use cron jobs here to achieve the following:

  • Execute a script at a specified time once a week, which peforms the following:
  • Create a zipped-tar backup of the folder /backup/cpbackup/ at /backup/cpbackup_ddmmyy.tar.gz
  • Clear the entire /backup/cpbackup/ folder of all contents, freeing up this space
  • Email the administrator that the new backup_x.tar.gz file has been created
  • (Optionally): Automatically copy this file over to another (larger capacity) server

Step 1: Install A Cron Task

A great little tutorial showing exactly how easy it is to install a cron task on any linux system, can be found here.

Step 2: Create the Shell Script

The script which performs the magic, looks like this:

#!/bin/sh
# Create a zipped-tar backup of the folder /backup/cpbackup/ at /backup/cpbackup_ddmmyy.tar.gz
# Clear the entire /backup/cpbackup/ folder of all contents, freeing up this space
# Email the administrator that the new backup_x.tar.gz file has been created
# (Optionally): Automatically copy this file over to another (larger capacity) server
 
EMAIL="youremail@gmail.com"
NOW=$(date +"%d-%b-%y_%H%M")
BFILE="/backup/wz_cbackup_$NOW.tar.gz"
MSG=""
SUBJECT=""
 
#create tar zip file:
tar -czf $BFILE /backup/cpbackup/
 
if [ "$?" -ne "0" ]; then
	MSG="tar -czf $BFILE /backup/cpbackup/ failed."
	SUBJECT="weeklyZip: $BFILE creation failed!"
else
  	MSG="$BFILE successfully created. Please either download or copy to new server."
  	SUBJECT="weeklyZip: $BFILE  successfully created."
 
	#Success, so we clear out the /backup/cpbackup/ folder:
	rm -R /backup/cpbackup/* -f
fi
 
/bin/mail -s "$SUBJECT" "$EMAIL" <<< "$MSG"

Handy Subversion Commands

Create And Import Into a New Subversion (SVN) Repository:
Replace ‘$repos_name’ with the name of your SVN repository – typically the name of the project folder.
(Note: For further repository admin functions and general SVN admin, see here).

$ svnadmin create /var/svn/$repos_name
 
#Note: To remove a repository, simply delete the directory directly as root user: (don't do this now unless you want to remove it :-):
sudo rm -R /var/svn/$repos_name

The recommended organization for a repository is to use the folders trunk, branches and tags, which we create using:

$ svn mkdir file:///var/svn/$repos_name/trunk
$ svn mkdir file:///var/svn/$repos_name/branches
$ svn mkdir file:///var/svn/$repos_name/tags

Now let’s import our project folder to this repository’s ‘trunk’ (main development line) folder): We do this the FIRST TIME we want to start tracking a project in the repository.

$ svn import localProjFolder file:///var/svn/$repos_name/trunk

Checking Out a Folder From a Repository:

Before having a local ‘working copy’ we need to check out the project folder for the first time:

$ svn checkout file:///var/svn/$repos_name/trunk {optional: localProjFolder}

Now we have a local working copy our project. We can make whatever changes we like, as in a usual file system structure. Note that any changes, APART FROM A STRAIGHT MODIFICATION TO A FILE (using a text editor for example), needs to be preceded by an ’svn’ to the command. For example, ’svn mkdir’ needs to be run to add a directory to the project. This is because SVN needs to be made aware of these changes.

Editing A File

Let’s say I have edited the file SecNew. java. We can now check the status of scheduled changes to our folder by running:

$ svn status
M      SecNew.java

In the above, we can see that the file SecNew.java was modified (in the working copy), and is due for modification in the repository.

Add A Folder To Our SVN Project

Let’s now add a folder to our project. I am going to add ‘custom’ to the folder SDCommon/src/org/:

$ svn mkdir ~/workspace/SDCommon/src/org/custom
A      custom

Note how I used ’svn’ before the usual ‘mkdir’ command. Remember, only pure editing of files does NOT require this preceding ’svn’ to be used.
The last line shows us that the folder was successfully created and is scheduled to be (A)dded to the repositoy.

Seeing What Changes We’ve Made To Our Folder: svn status & svn diff

Simply run the ’svn status’ command:

$ svn status
M      SecNew.java
A      custom

We can see the changes that are scheduled to be made to the repository on a commit.

Let’s now check out what specific changes have occurred to files that have been modified (SecNew.java in this case):

pacific@mainbox:~/workspace/SDCommon/src/org$ svn diff
 
Index: SecNew.java
===================================================================
--- SecNew.java (revision 6)
+++ SecNew.java (working copy)
@@ -5,6 +5,8 @@
 import org.apache.log4j.Logger;
 
 public class Security { //can be overridden if need be
+       protected DateTime StartDate = null;
+
        protected String ticker = null;
        protected String exchcode = null; //yahoo ec
        protected String name = null;

Lines that have a ‘+’ next to them, have been added. (a ‘-’ indicates lines that have been removed).

Subversion Using Apache And SSL

So you’d like to host a SVN repository which allows fine-grained access control and different users and projects? Look no further, as we’ll be covering how to achieve just that in this post. What we’ll be using is the Apache Web server (with SSL encryption), SubVersion and Trac which is a web-based software management package. I’ll be doing this on my Kubuntu Hardy Heron (Linux debian) machine, so if you’re installing it under a different version / OS instructions may vary slightly.

Note: You will need sudo access rights and a basic text editor (such as Kate) to do these.

Step 1: Install Apache httpd

This is as simple as installing the ‘apache2′ package using apt-get or aptitude:

$ sudo aptitude install apache2

For further detail on the installation of the Apache web server, see this.

Step 2: Install Subversion & Apache SVN Libraries
Similar to step 1, to install the subversion and the svn libraries for apache, simply run:

$ sudo aptitude install subversion libapache2-svn

Step 3: Enable SSL
First we enable the Apache ssl module, followed by adding ‘Listen 443′ to the Apache2 port configuration file (see below):

$ sudo a2enmod ssl
$ sudo kate /etc/apache2/ports.conf

After opening the ports.conf file, make sure that the following is in the file:

<IfModule mod_ssl.c>
        Listen 443
</IfModule>

Step 4: Generate SSL Certificate
Once we have enabled the SSL module, we need to generate a certificate to use:

#create dir where ssl certificates will go:
$ sudo mkdir /etc/apache2/ssl  
 
#create certificate (should launch application asking some basic questions)
$ sudo /usr/sbin/make-ssl-cert /usr/share/ssl-cert/ssleay.cnf /etc/apache2/ssl/apache.pem

Step 5: Create a Virtual Host

#copy default folder to svnserver folder
$ sudo cp /etc/apache2/sites-available/default /etc/apache2/sites-available/svnserver
 
#Open svnserver:
$ sudo kate /etc/apache2/sites-available/svnserver

Now, within the svnserver file, change the following:

"NameVirtualHost *" to "NameVirtualHost *:443"
"<VirtualHost *>"  to  <VirtualHost *:443>"

Directly under ‘ServerAdmin’, add the following:

SSLEngine on
SSLCertificateFile /etc/apache2/ssl/apache.pem
SSLProtocol all
SSLCipherSuite HIGH:MEDIUM

Step 6: Enable The Site

$ sudo a2ensite svnserver
$ sudo /etc/init.d/apache2 restart
#To avoid warnings when restarting apache2, add "ServerName $your_server_name" to /etc/apache2/apache2.conf

Step 7: Add Repositories
To host more than one repository, use the following configurations, replacing $REPOS with the name of your repository-folder, for example I’ve used ’svn-repos’

$ sudo mkdir /var/svn
$ sudo svnadmin create /var/svn/$REPOS
$ sudo chown -R www-data:www-data /var/svn/$REPOS
$ sudo chmod -R g+ws /var/svn/$REPOS

Step 8: Add Authenticated User
Add a user using the following, substituting $username with your selected username. You’ll be prompted for a password.

$ sudo htpasswd -c -m /etc/apache2/dav_svn.passwd $username

Step 9: WebDAV and SVN Configuration
Open the file: /etc/apache2/mods-available/dav_svn.conf using Kate, and either enable (uncomment) or add the following:

<Location /svn>
DAV svn
SVNParentPath /var/svn/
AuthType Basic
AuthName "Subversion Repository"
AuthUserFile /etc/apache2/dav_svn.passwd
Require valid-user
SSLRequireSSL
</Location>

Now restart the Apache2 webserver one final time:

$ sudo /etc/init.d/apache2 restart

Step 10: Test It !
Point your browser to: https://localhost/svn/$REPOS (where $REPOS is your specific svn repository)
You should now see something like “Revision 0: /” appear – this is our first SVN repository contents. Obviously it is empty, so let’s get right stuck into
testing it out with some SVN!

Handy Linux Commands

Recursively Remove All Files With a Specific Extension:

Suppose you want to delete, recursively, all files with a specific file extension (e.g. a Java folder containing .class files). Run the following command:

$ find folderToStart/ -name \*.extension -exec rm {} \;

Bulk Renaming All Files Within a Folder

To replace a string within all files within a folder, just run the following:

$ for i in *; do newname=`echo $i|sed 's/search_txt/replace_txt/g'`; echo $newname; mv $i $newname; done

For more handy applications to bulk renaming using regular expressions, see this.

Check Folder Sizes on System

Very handy tutorial on how to do all aspects of disk space usage, can be found here.

Linux Shell Scripting: Check if Folder Exists

If we want to check if a folder exists we simply create the following .sh file:

#!/bin/bash
 
if [ ! -d "myFolder" ]; then
	echo "Creating myFolder"
	sudo mkdir myFolder
fi
 
if [ ! -d "myFolder/subFolder" ]; then
	echo "Creating myFolder/subFolder"
	sudo mkdir myFolder/subFolder
fi

Now you can run that using sudo and for example create a new folder where one didn’t exist before, if its required by some other script, for example one mounting a windows XP drive over a network to the current Linux filesystem using Samba. See the post covering that for more details.

Algo-Trading 101 Part 2: Backtesting

One of the great benefits of quantitative trading models is that they can be backtested against historical price data to measure past performance. A great excercise is to find a strategy that you’re interested in (published somewhere, for example Futures & Options Trader magazine) and perform the backtest yourself. You should get the same results as the they did – this will ensure that you understand the strategy correctly and have avoided common pitfalls.

Historical Data

There are numerous sites offering various levels of quality historical stock price data. Below you can find some common sources of free historical data. Google is also your friend here, there’s lots of datasources out there.

Our aim will be to minimize costs

Optimizing MySQL 1: Using Indexes

Using Indexing To Speed Up MySQL

So recently I’ve come up against some issues with our website, webCV. The problem? Mysql tables were not correctly optimized, making the site run extremely slow since it was a heavily database intensive site. So after doing some reading in a great MySQL book, I decided to test some concepts, such as indexing, and that will be covered here: How to optimize your MySQL tables using indexes.

The exact workings of indexes will not be discussed here (for a more complete discussion of how they work, see http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html )

What we’ll also be looking at co-incidentally is how MySQL stored procedures can be used to populate tables quickly and easily with data (in this case random data).

Demo of Index Performance

In order to see how much indexes can improve query performance across multiple tables, we shall conduct a simple test involving three tables, t1, t2, and t3 with each table containing a column i1,i2 and i3 respectively and each having 1000 rows that are populated as follows: column i1 has rows with values 1 – 1000, i2 random values in range:  \{ 0, 20,000 \} , and column i3 the same as i2 (but different values).

The following MySQL procedure achieves the above by creating and populating the tables as needed. Simply import it from your Linux command line as you would any sql file:

The pop_random.sql file can be found here, exposing the init_tbl(’tablename’, no_random_entries) procedure call to us.

DELIMITER $
DROP PROCEDURE IF EXISTS init_tbl $
CREATE PROCEDURE init_tbl(IN tbl CHAR(64), IN upper INT)
BEGIN
	DECLARE count INT;
	SET count = 1;
 
	-- rem all first
	SET @s := CONCAT('DELETE FROM ', tbl, ' WHERE 1');
	PREPARE stmt FROM @s;
	EXECUTE stmt;
 
	-- Loop from 1 to upper--
	WHILE count &lt; (upper + 1) DO
		SET @s := CONCAT('INSERT INTO ', tbl, '(i0, i1,i2,i3) VALUES (',count, ', 0, 0, 0)');
		PREPARE stmt FROM @s;
		EXECUTE stmt;
		SET count = count+1;
	END WHILE;
 
	CALL pop_random(tbl);
END $
 
DROP PROCEDURE IF EXISTS pop_random $
CREATE PROCEDURE pop_random(IN tbl CHAR(64))
BEGIN
	SET @r := 0;
	SET @s := CONCAT('UPDATE ', tbl, ' SET ', tbl,'.i1= (@r := @r + 1) order by rand()');
	PREPARE stmt FROM @s;
	EXECUTE stmt;
 
	SET @r := 0;
	SET @s := CONCAT('UPDATE ', tbl, ' SET ', tbl,'.i2= (@r := @r + 1) order by rand()');
	PREPARE stmt FROM @s;
	EXECUTE stmt;
 
	SET @r := 0;
	SET @s := CONCAT('UPDATE ', tbl, ' SET ', tbl,'.i3= (@r := @r + 1) order by rand()');
	PREPARE stmt FROM @s;
	EXECUTE stmt;
 
	SET @msg := CONCAT(tbl, ' successfully created and populated.');
	SELECT @msg;
 
END $
 
DELIMITER ;
 
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `i0` int(11) NOT NULL,
  `i1` int(11) NOT NULL,
  `i2` int(11) NOT NULL,
  `i3` int(11) NOT NULL,
  PRIMARY KEY  (`i0`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `i0` int(11) NOT NULL,
  `i1` int(11) NOT NULL,
  `i2` int(11) NOT NULL,
  `i3` int(11) NOT NULL,
  PRIMARY KEY  (`i0`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3` (
  `i0` int(11) NOT NULL,
  `i1` int(11) NOT NULL,
  `i2` int(11) NOT NULL,
  `i3` int(11) NOT NULL,
  PRIMARY KEY  (`i0`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- _initialize the tables
call init_tbl('t1', 1000);
call init_tbl('t2', 1000);
call init_tbl('t3', 1000);

Running the above script will give us the three tables each with 3 columns i1-i3, each with 1000 lines of random, unique numbers. Stored procedures can make it easy to keep common procedures in SQL grouped together and perform complex operations. For example, if you’d like to populate the table ‘t1′ with more rows, simply run the query:

call init_tbl('t1', 20000);

Getting back to indexing, we’ll focus on the following three columns: t1.i1, t2.i2 and t3.i3. We write the following query to find all combinations of table rows in which the values are equal:

SELECT t1.i1, t2.i2, t3.i3 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.i1 = t2.i2 AND t2.i2 = t3.i3;

That query took 2 min 5.36 sec to finish – quite a while! By running this query without using indexes, we have to scan ALL rows to determine which rows contain which values. So all combinations must be tried to find the matches on the WHERE clause. The largest possible number of combinations is: 20,000 x 20,000 x 20,000 – a very large number! That’s alot of wasted effort. As the tables grow, the processing time increases even more in the absence of indexes, leading to poor performance. By using indexes, performance can be greatly improved due to the query being processed as follows:

  1. Determine the value of the first row from table t1;
  2. Now, using the index on t2, immediately find the value that matches the value from the 1). (Similarly, an index on t3 will allow the value from t2 to be found immediately).
  3. Go to the next row of t1 and repeat steps 1) – 2), until all rows in t1 have been processed.

So the above procedure still does a full scan of table 1, but indexed lookups on t2 and t3 will cause the query to run many many times faster. Lets create indexes on the tables and run our above query again to evaluate performance improvement:

#Do same for t3.i3
 ALTER TABLE `t2` ADD INDEX ( `i2` ) ;

Now running our select & join query, produces the same output as before, but in 0.22 secs – a speed increase of roughly 568 times. This increase will be even more the larger the tables are.

How To Decide Which Columns To Index

Columns that are used for searching, sorting or grouping are ideal candidates for indexing. Columns that are used for output should be left out. Thus, the columns that appear in the WHERE clause, columns named in join clauses or columns that appear in ORDER BY or GROUP BY clauses are the type of columns you want to index. Be careful not to over-index, since indexes can slow down insert/update operations.

Cabala-Fasting 1-Week Diet

OK so a good Canadian friend of mine has recommended the Cabala juice diet, based on his experience which has left him ‘cleansed’. It apparently also cures cancer. (Why they spending billions on research and chemo therapy again?)

I commenced this 1 week Cabala-fasting yesterday. The recipe can be found here: Cabala recipe.

Day 1:

Feeling mega-hungry. The fact that I went for a surf and a run probably didn’t help. Friend informed me I am not having enough juice.

Day 2:

Going much better, got tonnes more juice this time.

Will keep this post updated …

Day 7:

Ok So I’ve skipped a few days (of posting, not of taking the juice!) and while I am pretty sick of the juice, I can without a doubt say that I have lost several kilograms and feel fantastic and more energetic than ever. OK going to go get me a big fat juicy steak!

Highly recommend the diet, if only for its weight-loss powers!

SD