SIVA BLOG - Web Development

Optimize SQL Queries:

Indexes
Symbol Operator
Wildcard
NOT Operator
COUNT VS EXIST
Wildcard VS Substr
Index Unique Column
Max and Min Operators
Data Types
Primary Index
String indexing
Limit The Result
In Subquery
union vs OR

Indexes

To create Index for your column is a common way to optimize your search result.

Symbol Operator

Symbol operator such as >,<,=,!=, etc. are very helpful in our query. We can optimize some of our query with symbol operator provided the column is indexed.

example:
SELECT * FROM TABLE WHERE COLUMN > 16
SELECT * FROM TABLE WHERE COLUMN >= 15

Wildcard

In SQL, wildcard is provided for us with ‘%’ symbol. Using wildcard will definitely slow down.

We can optimize our query with wildcard by doing a postfix wildcard instead of pre (or) full wildcard.

Note: That column must be indexed for such optimize to be applied

#Full wildcard SELECT * FROM TABLE WHERE COLUMN LIKE '%hello%';

#Postfix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE 'hello%';

#Prefix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE '%hello';

NOT Operator

To Avoid NOT operator in SQL.

Positive Operator much faster than negative operator.

Using a positive operator just stop immediately once the result has been found. But negative operator check the all the records

Positive operators:

LIKE, IN, EXIST (or) = symbol operator

Negative operator:

NOT LIKE, NOT IN, NOT EXIST or != symbol.

COUNT VS EXIST

"To use Exist operator instead of Count operator" to determine whether a particular data exist.

SELECT COLUMN FROM TABLE WHERE COUNT(COLUMN) > 0

The above query is very bad query since count will search for all record exist on the table to determine the numeric value of field ‘COLUMN’.

The better alternative will be to use the EXIST operator where it will stop once it found the first record. Hence, it exist.

Wildcard VS Substr

"To use wildcard instead of substr". The column should be indexed for using wildcard.

~~SEARCH FOR ALL ROWS WITH THE FIRST CHARACTER AS 'E'~~

#BAD
SELECT * FROM TABLE WHERE substr ( COLUMN, 1, 1 ) = 'E'.

#BETTER
SELECT * FROM TABLE WHERE COLUMN = 'E%'.

Index Unique Column

MySQL search better with column that are unique and indexed.
Hence, it is best to remember to index those columns that are unique.

Max and Min Operators

Using Max and Min operators should help speed up the searching. If you are going to use min and max, The column must be indexed.

Data Types

Use the most efficient (smallest) data types possible.
VARCHAR will be better than long text to store an email or small details.

Primary Index

The primary column that is used for indexing should be made as short as possible. This makes identification of each row easy and efficient by the DBMS.

String indexing

It is unnecessary to index the whole string when a prefix or postfix of the string can be indexed instead.
Especially if the prefix or postfix of the string provides a unique identifier for the string, it is advisable to perform such indexing.
Shorter indexes are faster, not only because they require less disk space, but because they also give you more hits in the index cache, and thus fewer disk seeks.

Limit The Result

Another common way of optimizing your query is to minimize the number of row return.

SELECT * FROM TABLE

SELECT * FROM TABLE WHERE 1 LIMIT 10

In Subquery

Using dummy table is better than using an IN operator. Alternative, an exist
operator is also better.

SELECT * FROM TABLE WHERE COLUMN IN (SELECT COLUMN FROM TABLE)

Doing this is very expensive because SQL query will evaluate the outer query first before proceed with the inner query. Instead we can use this instead.

SELECT * FROM TABLE, (SELECT COLUMN FROM TABLE) as dummytable WHERE dummytable.COLUMN = TABLE.COLUMN;

Utilize Union instead of OR

Indexes lose their speed advantage when using them in OR situations in MySQL at least. Hence, this will not be useful although indexes is being applied

SELECT * FROM TABLE WHERE COLUMN_A = 'value' OR COLUMN_B = 'value'

Union: run faster

SELECT * FROM TABLE WHERE COLUMN_A = 'value'
UNION
SELECT * FROM TABLE WHERE COLUMN_B = 'value'

SQL Performance Tips

use index in the column.
use primary index.
use index unique
use symbol operators like, <,>,=
use positive operator like IN, EXIST instead of NOT IN, NOT EXIST
use full/pre wildcard operator
use prefix/postfix string index.
use LIMIT operator.
use EXIST instead of COUNT
use small data types VARCHAR
Use Slow Query Log (always have it on!)
Don't use DISTINCT when you have or could use GROUP BY
Use LOAD DATA instead of INSERT
Don't use ORDER BY RAND() if you have > ~2K records
Use SQL_NO_CACHE when you are Selecting frequently updated data or large sets of data.
use UNION instead of OR
Avoid using IN(...) when selecting on indexed fields, It will kill the performance of SELECT query.
Use stored procedures to avoid bandwidth wastage
enable key_buffer, query cache, table cache.

What is an Abstract Class?
An abstract class defines the basic skeleton for the class. It contains attributes and members but
some members are incomplete and is waiting for some other class to extend it through inheritance
so that the derived class provides a full functionality for the incomplete methods. A abstract class
cannot be instantiated and it can only be extended. A class prefix with “abstract” keywords are
abstract class. If a method is defined as abstract then it cannot be declared as private (it can only be
public or protected).
Syntax:
< ?
abstract class classname
{
//attributes and methods
.
.
abstract function methodname
}

class derived extends classname
{
function methodname
}
?>
Example 1 – Basic Abstract Class Usage
< ?
abstract class employee
{
protected $empname;
protected $empage;

function setdata($empname,$empage)
{
$this->empname = $empname;
$this->empage = $empage;
}

abstract function outputData();
}

//extending abstract class
class EmployeeData extends employee
{
function __construct($name,$age)
{
$this->setdata($name,$age);
}

function outputData()
{
echo $this->empname;
echo $this->empage;
}
}

$a = new EmployeeData("Hitesh","24");
$a->outputData();
?>
Output:
Hitesh
24
Explanation for the Example 1:
• Here i have made employee class an abstract class
• Employee class has 2 data members $empname and $empage. It also has a abstract method
outputData() and public method setData()
• Now i am extending the employee class in EmployeeData. It contains the functionality for
the abstract method outputData defined in employee class
Example 2 – Abstract Class Error
< ?
abstract class employee
{
protected $empname;
protected $empage;

function setdata($empname,$empage)
{
$this->empname = $empname;
$this->empage = $empage;
}

abstract function outputData();
}

class EmployeeData extends employee
{

function __construct($name,$age)
{
$this->setdata($name,$age);
}
}
$a = new EmployeeData("Hitesh","24");
$a->outputData();
//Will generate error as the EmployeeData class doesn't has the abstract
method defined
?>
Output
This will give you an error “Class EmployeeData contains 1 abstract method and must therefore be
declared abstract or implement the remaining methods (employee::outputData)”
Example3 – Multiple Abstract Class
< ?
abstract class employee
{
protected $empname;
protected $empage;

function setdata($empname,$empage)
{
$this->empname = $empname;
$this->empage = $empage;
}

abstract function outputData();
}

abstract class EmployeeData extends employee
{
abstract function setDataForEmployee();
}

class Payment extends EmployeeData
{
function setDataForEmployee()
{
//Functionality
}

function outputData()
{
echo "Inside Payment Class";
}
}

$a = new Payment();
$a->outputData();
?>
Output: “Inside Payment Class”

Interface :
This article will guide through the Interface Class in Object Oriented Programming. In simple
words Interface is a class with no data members and contains only member functions and they lack
its implementation. Any class that inherits from an interface must implement the missing member
function body. Interfaces is also an abstract class because abstract class always require an
implementation. In PHP 5 class may inherit only one class, but because interfaces lack an
implementation any number of class can be inherited. In PHP 5, interfaces may declare only
methods. An interface cannot declare any variables. To extend from an Interface, keyword
implements is used. PHP5 supports class extending more than one interface.
Syntax:
interface interfacename
{
function name()
function name1()
}

href='http://www.hiteshagrawal.com/php/oops-in-php5-polymorphism'>Polymorphism
in PHP5
class temp implements interfacename
{
public function name
{

}
}
Example1 – Interface Class in PHP5
< ?
interface employee
{
function setdata($empname,$empage);
function outputData();
}

class Payment implements employee
{
function setdata($empname,$empage)
{
//Functionality
}

function outputData()
{
echo "Inside Payment Class";
}
}

$a = new Payment();
$a->outputData();
?>
Output: “Inside Payment Class”
Explanation for the above Example:
Here i have a interface class called employee having two methods setData() and
outputData()
I am implementing the interface class on Payment class. Payment class also contains the
functionality for the methods defined in the interface.
Example2 – Combining Abstract Class and Interface Class
< ?
interface employee
{
function setdata($empname,$empage);
function outputData();
}

abstract class Payment implements employee //implementing employee interface
{
abstract function PaymentInfo();
}

class PaySlip extends Payment
{
function collectPaySlip()
{
echo "PaySlip Collected";
$this->outputData();
}

function outputData()
{
echo "Inside PaySlip Class";
}

function PaymentInfo()
{
echo "Inside PaySlip Class";
}

function setData($empname,$empage)
{
//Functionality
}
}
$a = new PaySlip();
$a->collectPaySlip();
?>
Output:
PaySlip Collected
Inside Payment Class

Inheritance :
This tutorial will guide you through one of the main feature of Object Oriented Programing which
is called Inheritance. Basically Inheritance is a mechanism where a new class is derived from the
existing base class. The derived class shares/inherit the functionality of the base class. To extend the
class behavior PHP5 have introduced a new keyword called “extends“.
While performing Inheritance operation Access Specifiers specify the level of access that the
outside world (other objects and functions) have on the data members / member functions of the
class. During Inheritance operation the derived class can access the parent class attributes having
protected/public access specifier. To access private data of parent class from derived class you will
have to call public/protected method of the parent class which will access the private variable and
return the data to the derived class. In PHP5 only single inheritance is allowed. i.e. You can inherit
only one class.
Example 1 – Parent Keyword in Inheritance Error
< ?
//Generates an error
class parent
{
private $firstname;
private $lastname;
}

class children extends parent
{
function __construct()
{
echo $this->firstname;
echo $this->lastname;
}
}
$a = new children();
?>
Output: Generate error in PHP5 as parent is keyword in PHP5.
Explanation: Parent is the Keyword in PHP5 and it cannot be used in PHP5 for declaring
classname.
Example 2 – Basic Inheritance Call
< ?
class parent1
{
protected $firstname = 11;
protected $lastname = 23;
}

class children extends parent1
{
function __construct()
{
echo $this->firstname;
echo $this->lastname;
}
}
$a = new children();
?>
Output: 1123
Explanation: Parent1 class have extending its functionality to the children class. Now the children class can access the
$firstname and $lastname attributes.
Example 3 – Accessing Private Data Member through Inheritance
< ?
class parent1
{
private $firstname = "hitesh";
protected $lastname = 23;

protected function getData()
{
return $this->firstname;
}
}

class children extends parent1
{
function __construct()
{
echo $this->getData();
}
}

$a = new children();
?>
Output: hitesh

OOPS in PHP 5 – Polymorphism

Polymorphism in PHP5 is a technique where the function to be called is detected based on the class object calling it at runtime. The basis of Polymorphism is Inheritance and function overridden.

Example – Basic Polymorphism

< ?

class BaseClass

{

 public function myMethod()

 {

      echo "BaseClass method called";

 }

}



class DerivedClass extends BaseClass

{

   public function myMethod()

   {

        echo "DerivedClass method called";

   }

}



function processClass(BaseClass $c)

{

   $c->myMethod();

}



$c = new DerivedClass();

processClass($c);

?>

Output: DerivedClass method called
Explanation:
Here i am declaring BaseClass and DerivedClass but i am calling the the processClass with the Derived Class Object.

Encapsulation

Encapsulation is just wrapping some data in an object. The term "encapsulation" is often used interchangeably with "information hiding".

<?php



class App {

     private static $_user;



     public function User( ) {

          if( $this->_user == null ) {

               $this->_user = new User();

          }

          return $this->_user;

     }



}



class User {

     private $_name;



     public function __construct() {

          $this->_name = "Joseph Crawford Jr.";

     }



     public function GetName() {

          return $this->_name;

     }

}



$app = new App();



echo $app->User()->GetName();



?>