MySQL performance optimization

Let us see some of the most simplest mysql optimizations and best practices,

Rue: Use MEDIUMINT for storing user Id for intranet applications. You can store -8388608 to 8388607 signed values or 0 to 16777215 unsigned values.

Rue: You can consider using BIGINT UNSIGNED for storing user Id for internet based applications. You can store -9223372036854775808 to 9223372036854775807 signed values or 0 to 18446744073709551615 unsigned values.

Rue: Always use UNSIGNED numeric types unless you want to store negative numbers.

Rue: Always have an primary key id column which is one of the INT types and UNSIGNED.

Rue: Use ENUM type over VARCHAR for storing predefined string values, e.g. "inactive", "inactive", "male", "female" etc.

Rue: Use DATE type instead of DATETIME if you want to store only date.

Rue: Use VARCHAR for variable string values.

Rue: Use INT UNSIGNED for IP4 values.

Rue: Using NOT NULL saves 1 bit per column.

Rue: Use binary type to store md5 value with 16 bytes instead of 32 bytes of varchar type.

Rue: Consider fixed format table structure (no varchar, no blob or no text columns) for tables with more write operations.

Rue: Use LIMIT in your SELECT, UPDATE statements if you already know your are looking for only one match, e.g. while checking for username and password match.

Rue: Avoid SELECT * FROM and specify only the interested column names.

Rue: Use index only when needed, indexes are good for reading and bad for storing data quickly. Identify the proportion of read and write operation on every single table in your database.

Rue: Consider storing image data outside the database and store only the reference of the image in the database. You can reconsider this approach if you want to support replication of servers.

For more information please refer the following sites,
http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html
http://dev.mysql.com/doc/refman/5.1/en/optimization.html
http://www.slideshare.net/ronaldbradford/top-20-design-tips-for-mysql-data-architects-presentation
http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/

Improving the performance of web page | web application

The first step in improving the performance of your web page is to measure the current performance of the web page.

If you are using Google Chrome then try Speed Tracer. If you are using Firefox then try
Page Speed.

You can also try online performance testing at webpagetest.

Let us see some simple rules to improve the performance of the web page,

Rule: Remove broken links (<a>).

Rule: Combine the external javascript links into 2 or three links to reduce the HTTP request.

Rule: Use inline javascript for fewer lines of script.

Rule: To make the home page load faster have only the scripts needed for the home page either as external link or inline.

You can also try to defer the loading of javascript.

Rule: Try to defer the loading of javascript if possible.

Refer Browserscope for knowing the we browsers parallel loading capabilities of javascripts and other resources.

Rule: Combine the external css into 2 or three files to reduce the HTTP request.

Rule: Use inline css for fewer lines of css.

Rule: To make the home page load faster have only the css needed for the home page either as external link or inline.

Rule: Compress and compact the html, css and javascript resources to reduce the number of bytes sent over the network.

Rule: Minimize DNS lookups to reduce the resolution requests. Use URL paths e.g. host your site on www.xyz.com/abc instead of abc.xyz.com. Serve the startup javascript and other resources from the same host.

Rule: Minimize redirection from one URL to another.

Rule: Use image compressor. This would reduce number of bytes sent over the network.

Rule: Make the styles (css) load before the scripts (javascript). This would enables better parallelization of downloads and speeds up browser rendering.

Refer Browserscope for knowing the we browsers parallel loading capabilities of javascripts and other resources.

Rule: Parallelize the loading of resources.

Rule: Have the inline style and external style sheets in the "head" section.

Rule: Remove the unused css, javascript and html. This would eliminate unwanted bytes sent over the network.

Rule: Specify the size (height and width) of the image in the img tag.

For more details please refer Web Performance Best Practices and Let's make the web faster
.
Now try applying the above rules to your web page and measure the performance.

Watch the below two videos for more on improving web page | web application performance.

Speed Tracer



Page Speed

Reading GPS data from Nokia phones

To read GPS data from your Nokia phone follow the below steps,

Make sure your Nokia phone supports inbuilt GPS receiver.

Install Aptana with Nokia WRT Plug-in for Aptana Studio.

Create a new project in Aptana with WRTKit.

1) Paste the below code in the index.html file.
2) Package the widget.
3) Download the package (yourprojectname.wgz) to your Nokia phone and install.
4) Run the application and wait the application to establish connection with the satellites.
5) Have fun!

Javascript example

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>GPS Tracker</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />        
<script type="text/javascript" src="WRTKit/WRTKit.js"></script>        
<META NAME="Generator" CONTENT="Nokia WRT plug-in for Aptana Studio 2.3.0" />
<style>
body{background-color:#ffffff;font-size:15px;}
#btnGetLocation{width:100%;margin:5px;height:50px;width:100px;}
#latLabel{text-align:left;margin:5px;width:150px;}
#longLabel{text-align:left;margin:5px;width:150px;}
#statusLabel{text-align:left;margin:5px;width:150px;}
#gpsStatusLabel{padding:5px;text-align:left;margin:5px;height:20px;width:150px;}
.gpsStatusLabelA{background-color:lightgreen;}
.gpsStatusLabelIA{background-color:red;}
#gpsStrengthLabel{text-align:left;margin:5px;width:150px;}
</style>
<script>
var interval=1000;   
var TXT_ACTIVE = "Active";
var TXT_INACTIVE = "Inactive";  
var TXT_RUNNING="Running";
var TXT_GPSSTATUSLABEL="gpsStatusLabel";
var GPS_INACTIVE = 0;
var GPS_ACTIVE = 1;

var locDataTimer = null;
var serviceObj = null;
var distanceCriteria = null;
var trackCriteria = null;

function updateElement(name,value)
{
 document.getElementById(name).innerHTML=value;
 if(name == TXT_GPSSTATUSLABEL)
 {     
  if(value == TXT_ACTIVE)
  {  
   document.getElementById(name).setAttribute("class", "gpsStatusLabelA");  
  }
  else
  {  
   document.getElementById(name).setAttribute("class", "gpsStatusLabelIA");
  }
 }
}
function reset()
{
 updateElement("gpsStatusLabel",TXT_INACTIVE);
 updateElement("gpsStrengthLabel","");
 updateElement("statusLabel","");
 updateElement("latLabel","");
 updateElement("longLabel","");    
}
function initUI()
{
 reset();
}
function initSO()
{
 try
 {
  serviceObj = device.getServiceObject("Service.Location", "ILocation");
 }
 catch (ex) 
 {
  updateElement("statusLabel",ex);
  return;
 } 
 // The user cancelled the service object initialization
 if (serviceObj.ILocation == null) 
 {
  return; 
 }        
 // Specify that location information need not be guaranteed. This helps in
 // that the widget doesn't need to wait for that information possibly
 // indefinitely.
 var updateOptions = new Object();
 updateOptions.PartialUpdates = true;
 
 // Initialize the criteria for the GetLocation call
 trackCriteria = new Object();
 trackCriteria.LocationInformationClass = "GenericLocationInfo";
 trackCriteria.Updateoptions = updateOptions;
 // Set the timer to tick (update the location data) at one second intervals
 locDataTimer = setInterval("tick()", interval);
}
function init()
{
 initUI();    
 initSO();
}

// Called when the locDataTimer's interval elapses
function tick() 
{
 updateElement("statusLabel",TXT_RUNNING);
 try 
 {
  var result = serviceObj.ILocation.GetLocation(trackCriteria);
  displayData(result);    
 }
 catch (ex) 
 {
  updateElement("statusLabel",ex);  
 }
}
// Displays the location data
function displayData(result) 
{
 if (result.ReturnValue == undefined) 
 {
  return;
 }

 var latitude = result.ReturnValue.Latitude;
 if (!isNaN(latitude)) 
 {
  updateElement("latLabel", latitude.toFixed(4) + " \u00B0");
 }       
 var longitude = result.ReturnValue.Longitude;
 if (!isNaN(longitude)) 
 {
  updateElement("longLabel", longitude.toFixed(4) + " \u00B0");
 }   
 if (!isNaN(latitude) || !isNaN(longitude)) 
 {
  // Either latitude or longitude information is received, so we can be
  // sure that the GPS is active
  changeGPSStatus(GPS_ACTIVE);
 }
 else 
 {
  changeGPSStatus(GPS_INACTIVE);
 }
 
 var numOfSatellites = result.ReturnValue.SatelliteNumView;
 if (numOfSatellites == undefined) 
 {
  numOfSatellites = 0;
 }
 updateElement("gpsStrengthLabel",numOfSatellites);
}   
//Changes the GPS status on the status pane
function changeGPSStatus(newStatus) 
{       
 if (newStatus == GPS_ACTIVE) 
 {
  updateElement("gpsStatusLabel",TXT_ACTIVE);
 } 
 else 
 {
  updateElement("gpsStatusLabel",TXT_INACTIVE);
 }
}
</script>
</head>
<body onload="init()">
<h3>GPS Tracker</h3>
<table border="1px" width="100%">
<tr>
<td>
GPS:
</td> 
<td align="center">
<div id="gpsStatusLabel"></div>   
</td>
</tr>
<tr>
<td>
GPS Strength:
</td> 
<td align="center">
<div id="gpsStrengthLabel"></div>   
</td>
</tr>
<tr>
<td>
Lat:
</td> 
<td align="center">
<div id="latLabel"></div>   
</td>
</tr>
<tr>
<td>
Long:
</td> 
<td align="center">
<div id="longLabel"></div>   
</td>
</tr>   
<tr>
<td>
Status:
</td> 
<td align="center">
<div id="statusLabel"></div>   
</td>
</tr>
</table>       
</body>
</html>