Database Indexing and How Indexes Work in MySql.

What is a Database Index?

• Database Indexing is a process or way to optimize and retrieve the query records from the DB faster.
• In general, whenever we query Database or Whenever Database process the query, disk Access is required. Query processing takes time if Indexing is not done, if the data is in large chunk.  
• Indexing is a Data structure method or technique which uses to query or locate the data faster from the database table by minimizing the usage of disk Access.
• Index is table which have only Two Column. First Column which always consist of Primary Key or Candidate Key.

Type of Database Indexes

1. Primary Indexing :

• By Default most of the table what we create in DB follow primary indexing
• Primary index uses primary key of the table which is unique to each record of the table and in the sorted order.
• Primary Key based indexing follow 1:1 relation between the records.

2. Clustering Index:

• Clustered Indexing is key value based index which helps in sorting the table record.
•   Per table only one Clustered Index can be created.
• In RDBMS, Primary key is used to create the Clustered Index based on specific column. 

For Example:  Suppose you created One Table named with Student_data and There Roll number is primary key in the table, then Clustered Index will be created automatically.  

3. Non-Clustered Index:

• Non-Clustered Index is Faster than Clustered Index. In single table you can create one or more Non-clustered index.
• Non-Clustered Index is an index structure separate from the table structure which uses one or more table-column for structuring.

How to Create Database Index in MySql 

Step 1 :Create Database with Name as student. (Follow the same code Written Below)

CREATE DATABASE student;

---

Step 2: Create Table Name as student_info and Insert Data in student_info 

CREATE TABLE student_info (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(90) DEFAULT NULL,
  `dob` DATE DEFAULT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO `student_info` (`name`, `dob`) VALUES('Kumar','2003-04-07');
INSERT INTO `student_info` (`name`, `dob`) VALUES('ISHAQ','2002-06-14');
INSERT INTO `student_info` (`name`, `dob`) VALUES('ANBUSELVAM','2004-06-18');
INSERT INTO `student_info` (`name`, `dob`) VALUES('MARTIN','2004-03-08');
INSERT INTO `student_info` (`name`, `dob`) VALUES('JAGAN','2003-04-10');

MySql Command to Create Index
The Syntax for creating Index is given Below

CREATE INDEX IndexName ON TableName;

Single-Column Index

In Single Column Index we use only one column of table for creating Index. Syntax is given below.

CREATE INDEX IndexName ON TableName (ColumnName);

Unique Indexes

Unique Indexing ensure the Data Integrity as well as enhance the Query Execution speed because Unique Index do not allow duplicate data to be inserted. Syntax is given below :

CREATE UNIQUE INDEX IndexName ON TableName(ColumnName);

Composite Indexes

We Create Composite Index using multiple column of the table. Syntax to create the Composite Index is below:

CREATE INDEX IndexName ON TableName (ColumnName1, ColumnName2);

Note: Whatever column you are using for creating Index (Either Composite or Single-Column Indexing) from table, Use only those column which is used frequently with WHERE Clause.    

Implicit Indexes

Implicit Index is default index created once the table is created. Implicit Indexing is created on Primary Key Constraints or Unique Key Constraints.

Note:-  When to use Indexing and when Not to use.

• Avoid Using or creating Index on small Table.
• We Must Not create index on those Table  column which have more NULL Values.
• Don’t Create Index on those table column which get manipulate frequently.

Database Index Option Type is of Four Type :

• FullText Type
• Unique Type Index
• Primary
• Key

Fulltext Type : While you create Index on table it will ask you the Index type or Index Choice, Where you will find FULLTEXT Type is as one option. FULLTEXT is one of the option in full-text Index. We can Select FULLTEXT option only for VARCHR, CHAR or TEXT Column type.

Unique Type Index:  While you select unique type index from the option you must select only those table column which have unique value.

Key Type Index: It is Normal Indexing type where you can select multiple Table column for the indexing purpose.

Primary Type: This indexing we create on primary key. As we know per table only one primary key is available in sorted order which help us in fetching row efficiently.

Database Indexing is one of the most frequently asked interview questions on DB Index. As a Developer we must know How indexing works in mysql, oracle etc which help us to optimize our query for the faster retrieval of data from the database.

What is a Database Index?

• Database Indexing is a process or way to optimize and retrieve the query records from the DB faster.
• In general, whenever we query Database or Whenever Database process the query, disk Access is required. Query processing takes time if Indexing is not done, if the data is in large chunk.  
• Indexing is a Data structure method or technique which uses to query or locate the data faster from the database table by minimizing the usage of disk Access.
• Index is table which have only Two Column. First Column which always consist of Primary Key or Candidate Key.

Type of Database Indexes

1. Primary Indexing :

• By Default most of the table what we create in DB follow primary indexing
• Primary index uses primary key of the table which is unique to each record of the table and in the sorted order.
• Primary Key based indexing follow 1:1 relation between the records.

2. Clustering Index:

• Clustered Indexing is key value based index which helps in sorting the table record.
•   Per table only one Clustered Index can be created.
• In RDBMS, Primary key is used to create the Clustered Index based on specific column. 

For Example:  Suppose you created One Table named with Student_data and There Roll number is primary key in the table, then Clustered Index will be created automatically.  

3. Non-Clustered Index:

• Non-Clustered Index is Faster than Clustered Index. In single table you can create one or more Non-clustered index.
• Non-Clustered Index is an index structure separate from the table structure which uses one or more table-column for structuring.

How to Create Database Index in MySql 

Step 1 :Create Database with Name as student. (Follow the same code Written Below)

CREATE DATABASE student;

---

Step 2: Create Table Name as student_info and Insert Data in student_info 

CREATE TABLE student_info (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(90) DEFAULT NULL,
  `dob` DATE DEFAULT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO `student_info` (`name`, `dob`) VALUES('Kumar','2003-04-07');
INSERT INTO `student_info` (`name`, `dob`) VALUES('ISHAQ','2002-06-14');
INSERT INTO `student_info` (`name`, `dob`) VALUES('ANBUSELVAM','2004-06-18');
INSERT INTO `student_info` (`name`, `dob`) VALUES('MARTIN','2004-03-08');
INSERT INTO `student_info` (`name`, `dob`) VALUES('JAGAN','2003-04-10');

MySql Command to Create Index
The Syntax for creating Index is given Below

CREATE INDEX IndexName ON TableName;

Single-Column Index

In Single Column Index we use only one column of table for creating Index. Syntax is given below.

CREATE INDEX IndexName ON TableName (ColumnName);

Unique Indexes

Unique Indexing ensure the Data Integrity as well as enhance the Query Execution speed because Unique Index do not allow duplicate data to be inserted. Syntax is given below :

CREATE UNIQUE INDEX IndexName ON TableName(ColumnName);

Composite Indexes

We Create Composite Index using multiple column of the table. Syntax to create the Composite Index is below:

CREATE INDEX IndexName ON TableName (ColumnName1, ColumnName2);

Note: Whatever column you are using for creating Index (Either Composite or Single-Column Indexing) from table, Use only those column which is used frequently with WHERE Clause.    

Implicit Indexes

Implicit Index is default index created once the table is created. Implicit Indexing is created on Primary Key Constraints or Unique Key Constraints.

Note:-  When to use Indexing and when Not to use.

• Avoid Using or creating Index on small Table.
• We Must Not create index on those Table  column which have more NULL Values.
• Don’t Create Index on those table column which get manipulate frequently.

Database Index Option Type is of Four Type :

• FullText Type
• Unique Type Index
• Primary
• Key

Fulltext Type : While you create Index on table it will ask you the Index type or Index Choice, Where you will find FULLTEXT Type is as one option. FULLTEXT is one of the option in full-text Index. We can Select FULLTEXT option only for VARCHR, CHAR or TEXT Column type.

Unique Type Index:  While you select unique type index from the option you must select only those table column which have unique value.

Key Type Index: It is Normal Indexing type where you can select multiple Table column for the indexing purpose.

Primary Type: This indexing we create on primary key. As we know per table only one primary key is available in sorted order which help us in fetching row efficiently.

Database Indexing is one of the most frequently asked interview questions on DB Index. As a Developer we must know How indexing works in mysql, oracle etc which help us to optimize our query for the faster retrieval of data from the database.

Different Type of DBMS Keys and Usage

Different Type of DBMS Keys and Example

• Primary Key is table-column which consist unique value and cannot be NULL. A table can have only one primary key in it. Primary Key is in sorted Order by Default.

• Unique Key is a table-column which consist unique value and it can be NULL. A Database Table can have many Unique Column in it.

• Foreign Key is a table-column or group of Table-column which set the relation between two table. Foreign Key of one table have the reference to the Primary key of another Table.

• Alternate Key is table Column or Group of column which identify each row in a table uniquely and not be NULL, But it is not a primary key. Alternate Key is Unique Identifier in table. In 

• Super Key is set key of one or more table-column which identify the table row uniquely. Super Key set in below table is {REGNO}, {ROLLNO}, {REGNO,ROLLNO}     

ID	REGNO	ROLLNO	FULLNAME	DOB
1	2020001435	10011	M Kumar	07-04-2003
2	2020001437	10012	MOHAMMED ISHAQ  H	14-06-2002
3	2020001478	10013	ANBUSELVAM S	18-06-2004
4	2020001487	10014	MARTINPRIYADOSS J	08-03-2004

• Candidate Key is a column or group of column which consist unique value and not be Null. We select primary key from the group of candidate Key. We can say candidate Key is minimal set of Super Key with no duplicate record. {REGNO}, {ROLLNO}

Points To Remember :

1. Primary Key can act as a Unique Key, but Unique Key can't be Primary key because it Can contain Null Value. 
2. Candidate key is Minimal set of Super Key with no redundant record.

Consider the Student_Info Table which contains Columns STUDENTID, ROLLNO, FIRSTNAME, LASTNAME, EMAILID. We will explain you the concept of keys in DBMS with example. Kindly go through the Student_Info table and explore your concept of all keys in DBMS below table.

STUDENTID	ROLLNO	FIRSTNAME	LASTNAME	EMAILID
1	08CS001	John	Doe	john@example.com
2	08CS003	Umesh	Prasad	umesh@gmail.com
3	08CS005	Nithya	V	nithya@gmail.com

Primary Key In DBMS : Defination, Rules

A Primary Key is a DBMS Table Key which is unique to each record in the table. It is Like unique Identifier tag of the table.

• Primary Key is a table Column which contain unique value and field can’t be Null.
• There will be Only One Primary key per Table.
• Primary Key can be considered as Candidate Key but Candidate key cannot be Primary Key.
• In Each Table Primary Key is sorted in Order.
• We can’t modify and update the primary key, if it is present in other table as a foreign Key.  

laravel Seeding and Datatbase from Scratch

Database Migration and Database seeding is one of the most amazing feature of Laravel Framework. Database seeding is feature through which we insert the dummy record / data using Seed Class in the DB table for testing purpose. Seed Classes name can be anything but it is good to follow the standard practice for naming Convention, Example of Seed Classes is UserTableSeeder, UserSeeder etc.

All the Seeder Classes is stored in database / seeds Directory folder. By default DatabaseSeeder is defined in seeds Folder.

Whenever we download the laravel project Folder at that time by default PHP Faker Package is also get installed in the Project folder. Php Faker package help us to generate the Fake data for Database seeding.

Artisan command to create the Seeder Class for populating Data in the table.

php artisan make:seeder seederName

SeederName whatever it get created using Artisan Command will be stored in database / seeds Directory. By Default Seeder class contain only one method that is run().

Artisan Command to run the seed Class is below for your Reference.

php artisan db:seed

php artisan db:seed --class UserTableSeeder

php artisan db:seed artisan command will run the DatabaseSeeder Class by default, which can call other seed class. If you want to run some particular Seed Class to populate the Database table then we have to use php artisan db:seed --class seederName

DatabaseSeeder Class code is below what we get in the database/seeds directory while installing the project.

use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()   // By Default Artisan Command will Run This
    {
        // $this->call(UsersTableSeeder::class);
    }
}

How to Implement The Laravel Database Seeding?

Step 1:  Create Model with migration using Artisan Command. Below is the command to create Model with migration.

  php artisan make:model model-Name  -m (-m is for migration )

Now check your Migration folder inside Database Folder in your laravel project.

In the migration folder the migration file will be created and if you open that you will get default up() function and down() function for the table whatever it is. I have modified the post_masters table by adding First_name and Last_name. 

   public function up()
     {
        Schema::create('post_masters', function (Blueprint $table) {
            $table->increments('id');
            $table->string('First_name', 50);
            $table->string('Last_name', 50);
            $table->timestamps();
        });
     }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('post_masters');
    }

Method 1st to implement the Database Seeding in Laravel

Step 2: Now go to database / seeds directory and open the default DatabaseSeeder.php file which we get by default. Below is the code for your reference purpose. Start writing the code in the run() function.

use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run() 
    {
        DB::table('post_masters')->insert([
            'First_name' => Str::random(10),
            'Last_name' => Str::random(10),
        ]);
    }
}

In the above code Str::random(10) will generate random strong which will  have the length 10 Character. Run the Artisan Command for Database table seeding, once it will run It will generate the random string that can be anything which will not have any sense. This Artisan Command will call the run() method by default.

php artisan db:seed

So now we will use PHP Faker package to generate some sensible data in below method.

Method 2nd to implement the Database Seeding in Laravel

In 2nd Method for each Migration Table we will create individual seeding class following the Naming convention using Artisan command and by using PHP Faker package for generating the data.

So Let's start with Example, create separate seeding Class for post_masters  table using Artisan Command. Below is the Command for your reference.

php artisan make:seeder PostMasterSeeder

Once you run the above Artisan command you will get the PostMasterSeeder.php file in database/seeds Directory. Below Code is the reference of  PostMasterSeeder.php. Now we will write the code for our Post_master table to create the fake Data for testing.

use Illuminate\Database\Seeder;
class PostMasterSeeder extends Seeder {     /**      * Run the database seeds.      *      * @return void      */     public function run()     {         //     } }

You can read more about the PHP Faker here Now Open the DatabaseSeeder.php file and call the PostMasterSeeder.php file for seeding.

use Illuminate\Database\Seeder; class DatabaseSeeder extends Seeder {     /**      * Run the database seeds.      *      * @return void      */     public function run()     {           $this->call(App\PostMasterSeeder::class, 50);
    } }

Now go to database / factories root folder there by default UserFactory.php will be there. We will create the PostMasterFactory using Artisan Command. Below is the Artisan command for your reference.

                                    php artisan make:model PostMasterFactory   

Above Artisan command will create the Factory for Post_master table. By Default it will give the code which is below for your reference. Above Command will create the Factory without model detail.

use Faker\Generator as Faker; $factory->define(Model::class, function (Faker $faker) {     return [             ]; });

                        php artisan make:factory PostMasterFactory --model=PostMaster

Above  Artisan command above will create the Factory with model which we can use for Database seeding for particular table.

use Faker\Generator as Faker; $factory->define(App\PostMaster::class, function (Faker $faker) {     return [         //     ]; });

Now add the code in the above PostMasterFactory.php file.

use Faker\Generator as Faker;

$factory->define(App\PostMaster::class, function (Faker $faker) {     return [         'First_name' => $faker->firstName,         'Last_name' => $faker->lastName,     ]; });

After doing all the previous Step run the Artisan Command to generate the record. Artisan command is below for your reference.

                                                        php artisan make:seed

Finally it will generate the sensible Fake data in Post_master table.