Skip to main content

What are the effects of indexing on database tables?

Image
By
The working of Index in database tables is the same as index work in a Text Book. It will help to browse the desired content faster in a book. we use to see the index on the first page then we check the page where it is in the book and then we directly come to that page, instead of going through all pages.

Now databases, same happen with tables, if you are applying select query on the table, it will also check for the index and then return you the required field. It will return the field faster on which index is applied.
Or in language,
  • we can say that the index one query will execute faster instead of the non-indexed one.
  • Join will execute faster on indexed one.
eg- Consider a Table Product with `id` as Primary Key
  •  id
  • name
  • price
  • category_id 
  • location_id           
    Table Info-

Now  Applying Index on category_id 



SELECT category_id FROM product WHERE category_id='5';




SELECT category_id FROM product WHERE location_id='10';




First one will excute faster than the second one.



















Labels:






























Comments











Post a Comment




















Popular posts from this blog



















PHP function for checking IMEI












By
























 Luhn algorithm for IMEI Check    public function __checkIMEI($imei){    if(strlen($imei)==15){              $imeia=($imei[1]*2);          if(strlen($imeia)==2){$imeia=str_split($imeia,1);  $imeia=$imeia[0]+$imeia[1]; }             $imeib=($imei[3]*2);             if(strlen($imeib)==2){$imeib=str_split($imeib,1); $imeib=$imeib[0]+$imeib[1]; }             $imeic=($imei[5]*2);             if(strlen($imeic)==2){$imeic=str_split($imeic,1); $imeic=$imeic[0]+$imeic[1]; }             $imeid=($imei[7]*2);             if(strlen($imeid)==2){$imeid=str_split($imeid,1); $imeid=$imeid[0]+$imeid[1];}             $imeie=($imei[9]*2);             if(strlen($imeie)==2){$imeie=str_split($imeie,1); $imeie=$imeie[0]+$imeie[1]; }             $imeif=($imei[11]*2);             if(strlen($imeif)==2){$imeif=str_split($imeif,1); $imeif=$imeif[0]+$imeif[1]; }             $imeig=($imei[13]*2);             if(strlen($imeig)==2){$imeig=str_split($imeig,1); $imeig=$imeig[0]+$imeig[1]; }      $IMEI=             ($ime...




















2 comments









Read more




























PHP code for Implementing LRU cache. 












By
























<?php interface LRUCache{          /**         •@param string $key         •@param string $value         •@return bool $result          •          •Stores value against the key in the cache */         public function insertIntoCache($key,$value);           /**         •@param string $key         •@return string $value         •Gets the value of a key from the cache */          public function getFromCache($key);           /** Purge the entire cache */          public function purgeCache();           /**         •@return int $count         •Gets the number of successful cache hits so far */          public function allCacheHits();           /**         •@return int $count         •Gets the number of unsuccessful cache hits so far          **/                  public function allCacheMissed();  }   class Cache implements LRUCache{          // int the max number of elements the cache supports         private $capaci...




















Post a Comment









Read more




























Magic Function in PHP (__sleep() and __wakeup() )












By
























 There are many magic methods in PHP like    __construct(), __destruct(), __callback(), __get(), __set(), __sleep(), __wake() and many more. But we will be takingon  on  __sleep() and  __wake().   __sleep() : serialize()  checks if your class has a function with the magic name __sleep(). If so, that function is executed prior to any serialization. It can clean up the object and is supposed to return an array with the names of all variables of that object that should be serialized. If the method doesn't return anything then NULL is serialized and E_NOTICE is issued.        serialize() is used for the representation of the storage class for storing the value. Serializing   an object means converting it to a byte stream representation that can be stored in a file.    The use of __sleep()  to commit the pending task. If a bulk data is being inserted then at that time __sleep can be used. it will not release the object unless the work is not completed....




















Post a Comment









Read more