melfneerg.com

About the Author
Tudor Davies

author Tudor is a techie turned manager who fights like mad to keep his tech skills honed and relevant. Everything from web hosting, networking, *nix and the like. Constantly developing and co-ordinating with others to make the web a better (and easier to use) place.

Been working on speeding up some of my websites recently.

Step 1 - minify all your CSS and JS files

Step 2 - add compression to the .htaccess file.

AddOutputFilterByType DEFLATE text/plain

AddOutputFilterByType DEFLATE text/html

AddOutputFilterByType DEFLATE text/xml

AddOutputFilterByType DEFLATE text/css

AddOutputFilterByType DEFLATE text/javascript

AddOutputFilterByType DEFLATE application/xml

AddOutputFilterByType DEFLATE application/xhtml+xml

AddOutputFilterByType DEFLATE application/rss+xml

AddOutputFilterByType DEFLATE application/javascript

AddOutputFilterByType DEFLATE application/x-javascript

Step 3 - sort out slow mysql queries and/or add indices to tables

Step 4 - dont use RAND() in mySQL queries

There are 2 code snippets below that I ran using "time php -q x.php" and the results are posted below:

1.php

$hostName = "localhost";

$userName = "blah";

$password = "blahblah";

$dbName = "blah";

mysql_connect($hostName, $userName, $password) or die("Unable to connect to host");

mysql_select_db($dbName) or die("Unable to select database $dbName");

$query = "SELECT * FROM Catalog ORDER BY RAND() LIMIT 0, 100";

$result = mysql_query($query) or die(mysql_error());

mysql_close();

2.php

$hostName = "localhost";

$userName = "blah";

$password = "blahblah";

$dbName = "blah";

mysql_connect($hostName, $userName, $password) or die("Unable to connect to host");

mysql_select_db($dbName) or die("Unable to select database $dbName");

$result = mysql_query("SELECT COUNT(*) FROM Catalog");

while ($row = mysql_fetch_array($result)) {

$total_recs = $row["COUNT(*)"];

}

$exnums = """;

for ($cnt=0; $cnt<=100; $cnt++) {

$exnums .= rand(0, $total_recs)."","";

}

$exnums = substr($exnums, 0, -2);

$query = "SELECT * FROM Catalog WHERE ID IN (".$exnums.");";

$result = mysql_query($query) or die(mysql_error());

mysql_close();

The results?

time php -q 1.php



real    0m2.561s

user    0m1.157s

sys     0m0.058s



time php -q 2.php



real    0m1.234s

user    0m1.166s

sys     0m0.057s

mySQL RAND() is officially rubbish!

My site now returns results fast enough to keep up with a steady click on the button :)

- 'cos life is like that

Random mySQL Records