What are the indexes in mysql ?

An index helps to get the data from tables very fast. When we run the query to fetch data from a table, MySQL checks if the indexes exist, then MySQL uses the indexes to select exact physical corresponding rows of the table instead of scanning the complete table.

Indexes can be created using one or more columns. Columns use in your SQL queries need to be index. It will speed up your queries.

How to know the index available on the table:

SHOW INDEX FROM tableName.

Create an index on the table(You can use one or more columns to create an index):

CREATE UNIQUE INDEX indexName ON tableName ( column1, column2,...);

We can create the four type of indexes:

1) Ordinary Index

2) Primary Index

3) Unique Index

4) Full-Text Index

Below is the sample statements to add above four types of indexes:

1) Ordinary Index: This is an ordinary index in which any value may appear more than once.

ALTER TABLE tableName ADD INDEX indexName (columnList)

2) Primary Index: Below statement adds a PRIMARY KEY, which means that indexed values must be unique and cannot be NULL. 

ALTER TABLE tableName ADD PRIMARY KEY (columnList)

3) Unique Index:  This index must have unique value (with the exception of NULL values, which may appear multiple times)

ALTER TABLE tableName ADD UNIQUE indexName (columnList)

4) Full-Text Index: This creates a special FULLTEXT index that is used for text searching in tables.

ALTER TABLE tableName ADD FULLTEXT indexName (columnList)

How to drop an index?

ALTER TABLE tableName DROP INDEX (indexName);

mysql interview questions for 1 year experience

Q. Which one is the default storage engine in mysql ?
A.  InnoDB is the default storage engine in latest mysql.
      MyISAM  is the default storage engine before mysql 5.5.5

Q. What is difference between MyISAM and InnoDB ?
A. Below are the differences based on mysql 5.5.5

MyISAM       
    Storage limits:256TB   
    MVCC    : No       
    Full-text search indexes : Yes          
    Transaction support : No
    Clustered indexes : No 
    Locking granularity : Table
    Data caches : No
    Foreign key support : No
    Store table in 3 files : .frm (table format) extension, .MYD (data file) extension,
                                             .MYI (MYIndex) extension.
InnoDB
    Storage limits:64TB
    MVCC    : Yes
    Full-text search indexes : No
    Transaction support : Yes
    Clustered indexes : Yes
    Locking granularity : Row
    Data caches : Yes
    Foreign key support : Yes   
    Store table in one single system tablespace

Q. What are the difference b/w primary key and unique key ?
A. Only one primary key exists in one table while multiple unique keys can exist.
     Primary key doesn't allows null values while unique key can be null (only single
     null can be allowed)
     We can make unique keys with multiple columns.

Q. Write the query to set foreign key ?
A.
CREATE TABLE user (
    id int(11) NOT NULL,
    userName varchar(200) NOT NULL,
    password varchar(200) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE user_details (
    id int NOT NULL,
    userId int NOT NULL,
    fName varchar(255) NOT NULL,
    lName varchar(255) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (userId) REFERENCES user(id)
);

Q. Write the query to find the 2nd maximum salary ?
A. Take the example of Employee table.
     CREATE TABLE `tblEmp`
     (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `eName` varchar(200) DEFAULT NULL,
       `salary` int(11) DEFAULT NULL,   PRIMARY KEY (`id`)
     );

    and below are the sample data:
 
   id    eName    salary
    1    sh1            1000
    2    sh2            500
    3    sh3            4000
    4    sh4            2000
    5    sh5            700
    6    sh6            800
    7    sh7            1500
    8    sh8            3000
    9    sh9            200

    Now, the query to find the 2nd max salary :
 
    SELECT e.eName, e.salary FROM tblEmp e ORDER BY salary DESC LIMIT 1,1;


   Also, If you want go to get the nth maximum salary then use the below query :

    SELECT e.eName, e.salary FROM tblEmp e ORDER BY salary DESC LIMIT n-1,1;


Q. What are the joins ?
A. SQL Joins are used when we need data from two or more tables. Both tables must have related column. Below are the joins used in MySQL .

 INNER JOIN: Returns records that have matching values in both tables

 LEFT JOIN or LEFT OUTER JOIN : Return all records from the left table, and the 
 matched records from the right table.

 RIGHT JOIN or RIGHT OUTER JOIN: Return all records from the right table, and the
 matched records from the left table.

 FULL JOIN or FULL OUTER JOIN : Return all records when there is a match in either
 left or right table

Upgrade PHP from 5.3 to 5.4 on ubuntu 12.04

There are simple steps to upgrade your PHP version from 5.3 to 5.4 .

1)  Add the below repository in your system
      sudo add-apt-repository ppa:ondrej/php5-oldstable

2) Run the below command to update your packages
     sudo apt-get update

3)  Install the python-software-properties with below command, that need to be  
      necessary to add a package repository:
      sudo apt-get install python-software-properties

4) Run the below command now to upgrade version and follow the instructions .
     sudo apt-get install php5

How to install mongo on ubuntu 12.04

I got the error of MongoClient not found. I am using ubuntu 12.04 .
Below are the steps to install php-mongo driver .

sudo apt-get install php5-dev make php-pear
sudo pecl install mongo


Add extension mongo.so into php.ini file
extension=/usr/lib/php5/20100525/mongo.so

Parse error: syntax error, unexpected '['

Hi All,

I got this error when I pull some code from master.
This issue comes due to I have PHP5.3 version which doesn't support [] to create array.

<?php
// php version < PHP 5.4
$array = array(
    '0' => "aaaa",
    '1' => "bbbb",
);

// as of PHP 5.4
$array = [
    '0' => "bar",
    '1' => "foo",
];
?>
 

php strtotime function is similar to which mysql function.

Below are the options in mysql similar to PHP strtotime function.


                                        PHP                     |              MYSQL

       strtotime(date('Y-m-d'));                | select UNIX_TIMESTAMP(CURDATE()) fro dual ;
       strtotime(date('Y-m-d H:i:s'));      | select UNIX_TIMESTAMP(NOW()) fro dual ;

Prime number program in PHP.

Hi

Below is the program to extract the prime numbers from given array as well as you can check the
single number that is prime or not.


function isPrimeNumber($number)
{
        if ($number < 2) {
                return false;
        }
        for ($i=2; $i<=($number / 2); $i++) {
                if($number % $i == 0) {
                        return false;
                }
        }
        return true;
}

function checkPrimeNumber($arr)
{
$len = count($arr);

$pArr = array();
for($i=0;$i<$len;$i++) {
$checkPrime = isPrimeNumber($arr[$i]);
if($checkPrime == true)
array_push($pArr,$arr[$i]);
}
return $pArr;
}

Sample :
$arr = array(2,3,8,9,11,13,17,19,21,23,16,15);

$pArr = checkPrimeNumber($arr);
echo '<pre>';
print_r($pArr);

If you want to check that any number is prime or not , you can check by calling function isPrimeNumber($number) .