MySQL vs MongoDB: Comparison Between RDBMS and Document Oriented DB

MySQL vs MongoDB (NoSQL) database

MongoDB vs MySQL: Comparison Between Relational and Document Oriented Database


Both are used for storing the data and free to use that is both comes under open-sources software.
MongoDB might be unfamiliar at least to some of us as it is a relatively new compared to other established databases such as MySQL, PostgreSQL or Oracle.

Continue reading

How to import an SQL file using command line in MySQL?

Import MySQL database using command line


If you have a very large SQL dump file to import and you know very well that it is not very easy to import using phpMyAdmin, even if you split it in many pieces, it is also be hard using PHP script as it will time out after a particular time.


In this case there is only one acceptable and easy solution is import the file directly from MySQL command line.
As command Line will always be the most efficient, though not the most user-friendly way to accomplish a database export and import.


Follow the below easy steps to make your life easy.


The below example I am showing is executed on my local server(local host) and using XAMPP, but same will also work on Linux if you are using Linux for your application but you need to make sure that the file you want to import is of a .sql(.gz, .gzip) format.


The below method is perfect if


  • You do not have phpMyAdmin installed.
  • You do not have remote web access to the database server.
  • You do have SSH/Putty access.
  • The database file size is very large.
  • You want total control over the process.

Let’s start


1. Open a command prompt (or shell in Linux) with administrative privileges.


2. If you are in Windows set character set to Unicode. Linux is using UTF-8 by default.

chcp 65001


3. Connect to a MySQL instance using command line (If your MySQL path is different rather than drive D: as I have, you should be in that folder only)

D:\xampp\mysql\bin\mysql -h --port=3306 -u root -p

If you are in local host you do not need host and port

D:\xampp\mysql\bin\mysql -u root -p


4. It will now ask for password: enter the password
—> <your-password>


5. You are now in MySQL shell. Set network buffer length to a large byte number. The default value may throw errors for such large data files

set global net_buffer_length=1000000;


6. Set maximum allowed packet size to a large byte number. The default value may throw errors for such large data files.

set global max_allowed_packet=1000000000;


7. Disable foreign key checking to avoid delays, errors and unwanted behavior

SET foreign_key_checks = 0;


8.  Select your database as below

use <your-db-name>;


If you have not created the database yet, in which you want to import the database then below the above you can created the database using the below MySQL command

create database <your-db-name>; and after then run use <your=db-name>;


9. Provide your dump file full path name

source <DbName.sql>

If your file is not is the location you are currently in then provide the complete path of the file as below

source C:\scriptarticledotcom_db\dbdump150404.sql


It’s DONE!!

Remember to enable foreign key checks when process is complete else it will set for all of your databases as well and it will work improper!


Run the below MySQL commands for reset the global MySQL settings

SET foreign_key_checks = 1;


Hope the above will work, share you feedback or comments if you face any issue or have more suggestion to make the article more useful for developers.



MySQL Interview Questions & Answers

MySQL interview questions for experienced

MySQL query for finding max and second max and nth max salary for a employee table using MySQL

Suppose you have a table ’employee’ given as below
| EmpId        |    Salary      |
| 2                  |    2000        |
| 3                  |    5000        |
| 5                  |    2000        |
| 8                  |    3000        |
| 9                  |    4564         |
| 12                |    3000        |
| 13                |    2000        |
| 15                |    8000        |
Now the question is : find max salary and 2nd max salary of employee (MySql Query)

1. Highest salary

SELECT MAX(Salary) FROM employee

2. 2nd highest salary

SELECT MAX(Salary) FROM employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM employee )


SELECT Salary from employee order by Salary desc limit 1,1

You can fetch both as below

SELECT (SELECT max(Salary) from employee) as max_sal, (SELECT Salary from employee order by Salary desc limit 1,1) as max_2nd

SELECT (SELECT MAX(Salary) FROM employee) max_sal, (SELECT MAX(Salary) FROM employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM employee )) as max_2nd

nth height Salary

SELECT * FROM employee Emp1 WHERE (n-1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM employee Emp2 WHERE Emp2.Salary > Emp1.Salary)

SELECT EmpId, Salary FROM employee  ORDER BY Salary DESC  LIMIT (n-1) , 1


MySQL query for case sensitive string comparison on MySQL.


According to MySQL Manual (, It’s depends on what collation you have chooses during the column creation.

You can have a look on various collation available for column data storage. If it’s ‘_ci’ (case-insensitive) or ‘_cs’ (case-sensitive)

If you want the comparison to be case sensitive, you could add COLLATE just like this in your SQL query:
WHERE `colname` COLLATE latin1_general_cs = 'KeyWord'


You can also do by cast the keyword of column value to binary as when one side in the comparison is cast to binary the comparison is done binary and it will be case-sensitive

SELECT * FROM your_table WHERE BINARY description = 'KeyWord'


SELECT * FROM your_table WHERE description = BINARY 'KeyWord'


To determine whether a value will compare as a non-binary or binary string, use the COLLATION() function. This shows that VERSION() returns a string that has a case-insensitive collation, so comparisons are case insensitive or vice-versa:


According to MySQL 5.0 Manual(, If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation.

ALTER TABLE your_table CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];


ALTER TABLE employee CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci;


For modifying the collation for a column only you can follow as below

ALTER TABLE employee MODIFY  name VARCHAR(255) CHARACTER SET latin1  COLLATE latin1_swedish_ci;


What are Heap tables?


HEAP tables are present in memory and they are used for high speed storage on temporary basis. These tables are commonly known as memory tables.

Below are some feature of Heap tables.

  • BLOB or TEXT fields are not allowed
  • Only comparison operators can be used =, <,>, = >,=<
  • AUTO_INCREMENT is not supported by HEAP tables
  • Indexes should be NOT NULL


Differences between HEAP and Temporary tables?

The heap tables are shared among clients while temporary tables are not shared.

Heap tables are just another storage engine, while for temporary tables you need a special privilege (create temporary table).


What is the default port for MySQL Server?

The default port for MySQL server is 3306.


What is the difference between primary key and candidate key?

Every row of a table is identified uniquely by primary key. There is only one primary key for a table.

Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.


How many columns can you create for an index?

You can create maximum of 16 indexed columns for a standard table.


What does myisamchk do?

myisamchk is a command line tool for checking, repairing and optimizing non-partitioned MyISAM tables.


What happens when the column is set to AUTO INCREMENT and if you reach maximum value in the table?

It stops incrementing. Any further inserts are going to produce an error, since the key has been used already.


Where MyISAM table will be stored and also give their formats of storage?

Each MyISAM table is stored on disk in three formats:
The ‘.frm’ file stores the table definition
The data file has a ‘.MYD (MYData) extension
The index file has a ‘.MYI’ (MYIndex) extension


How to enter Characters as HEX Numbers?

If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox).

A HEX number string will be automatically converted into a character string, if the expression context is a string.


What is the different between NOW() and CURRENT_DATE()?

NOW() command is used to show current year, month, date with hours, minutes and seconds while CURRENT_DATE() shows the current year with month and date only.


What is SQLyog?

SQLyog program is the most popular GUI tool for admin. It is the most popular MySQL manager and admin tool. It combines the features of MySQL administrator, phpMyadmin and others MySQL front ends and MySQL GUI tools.


What is a trigger in MySQL & how many TRIGGERS are allowed in MySQL table?

A trigger is a set of codes that executes in response to some events.
SIX triggers are allowed in MySQL table. They are as follows:



Some Common SQL Function?

CONCAT(A, B) – Concatenates two string values to create a single string output. Often used to combine two or more fields into one single field.

FORMAT(X, D) – Formats the number X to D significant digits.

CURRDATE(), CURRTIME() – Returns the current date or time.

NOW() – Returns the current date and time as one value.

MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() – Extracts the given data from a date value.

HOUR(), MINUTE(), SECOND() – Extracts the given data from a time value.

DATEDIFF(A, B) – Determines the difference between two dates and it is commonly used to calculate age

SUBTIMES(A, B) – Determines the difference between two times.

FROMDAYS(INT) – Converts an integer number of days into a date value.


Explain CSV tables.

They special tables for which the data is saved into comma-separated values files. They cannot be indexed.
CSV table stores data in plain text and tabular format. It typically contains one record per line. CSV tables are most widely used to store phone contacts to Import and Export and can be used to store any sort of plain text data.


Explain advantages of InnoDB over MyISAM.

InnoDb supports locking of rows while MyISAM supports only table locking.
Data integrity is more in InnoDB.
Transactional nature of InnoDB enables easy and online backups.


Explain advantages of MyISAM over InnoDB.

Data is not cached by the MySQL query browser.
MyISAM is a simple storage engine.
MyISAM provides more optimization.
MyISAM has a low relative memory use.


How large can a MySQL database become?

Thus, as long as your file system supports a maximum file size of at least 256 TB,
you will never run into any file system limitations before MySQL runs into other internal limits.


How to get the date after 7 days using MySQL query

select DATE_ADD(NOW(), INTERVAL 7 DAY) as last_date.


Define system tables.

All the user and permission information is stored in the mysql database in a set of tables known as the grant tables.

The information_schema database really is not a database but an interface to various system metadata


MySQL command for showing current configuration variables



For updating these configuration variable values

SET sort_buffer_size=10000;

SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;


CDN: Content Delivery Network Overview

Content Delivery Networks (CDN)


Hi Everybody, I think you all aware about the CDN (Content Delivery Networks).


If not, don’t worry I am going to explain you what is CDN and how it works and how to know whether you site need CDN or not.


In Short: A content delivery network (CDN) places files in different locations so that the person using your webpage can receive the nearest copy.


Let’s discuss more about CDN?
A CDN (content delivery network) is a system of distributed servers (network) located in different parts of the globe that stores files to be used by your website visitors, It deliver web pages and other Web content to a user based on the geographic locations of the user, the origin of the webpage and a content delivery server may differ.


The reason why it used is, there is a measurable amount of waiting time (webpage load) for a website user who is visiting a page that may be hosted thousands of miles away. There are also routing issues that can occur when a user is visiting such a webpage. If someone in Jaipur is using a webpage that is hosted in London they are seeing a slower version of that webpage because of the above mentioned routing issues and sheer distance the files have to travel.


By having your files on several servers across a geographical area you can make sure the user is loading files that are near them, not all the way across the country or ocean.

This service is effective in speeding the delivery of content of websites with high traffic and websites that have global reach. The closer the CDN server is to the user geographically, the faster the content will be delivered to the user. CDNs also provide protection from large surges in traffic.


How it works?
In a CDN, content exists as multiple copies on strategically dispersed servers. A large CDN can have thousands of servers around the globe, making it possible for the provider to send the same content to many requesting client computing devices efficiently and reliably even when bandwidth is limited or there are sudden spikes in demand.


Whenever a visitor request a webpage to open, the CDN will redirect the request from the originating site’s server to a server in the CDN that is closest to the user and deliver the cached content. The CDN will also communicate with the originating server to deliver any content that has not been previously cached.


CDN management software dynamically calculates which server is located nearest to the requesting visiter and delivers content based on those calculations. This not only eliminates the distance that content travels, but also reduces the number of hops a data packet must make. The result is less packet loss, optimized bandwidth and faster performance which minimizes time-outs, while improving overall user experience (UX) and site health. In the event of an Internet attack or malfunction at a junction of the Internet, content that’s hosted on a CDN server will remain available to at least some users.


The process of bouncing through a CDN is nearly transparent to the user. The only way a user would know if a CDN has been accessed is if the delivered URL is different than the URL that has been requested.


Why the CDN is so much popular now a days?
The real reason that so many businesses and webmasters are now using CDNs is because Google has started using page speed as a ranking factor. Content delivery networks however provide a faster experience for users, and that means happier users who buy more things and click more ads. An additional reason for CDN use is the explosion of tablet and mobile users who depend on speed more so than desktop users who have stable internet connections.


Does your site need a CDN?
Content delivery networks are used for B2B interactions and in serving content to consumers. CDN is well suited for delivering large streaming audio, video, and Internet television (IPTV) programming, although an Internet service provider (ISP) may also use one to deliver static or dynamic Web pages.


You can also host your site on CDN, below are some outstanding improvement if CDN used:

  1. Improvement the page speed
  2. Amazing site performance for your users
  3. Make your site mobile ready and available for some geographical user even server is going down or slow.


Some types of websites I not recommend for CDN:

  • Local business sites (DJ, restaurants, city hair salon, city shops etc.)
  • Websites that have their main visitors or traffic in only one geographic region.


How much it costs for you?
CDN is a paid service provided by many hosting providers and just like every web service offered, there are almost levels of cost for content delivery networks from free to thousands of dollars depends on the service they provides.


Below are the list of some CDN provided listed according to popularity (visit their website for more information, cost and configuration/setup along with customer service).

  1. Akamai
  2. Max CDN
  3. Amazon CloudFront
  4. CloudFlare CDN (this has free option as well)
  5. CDNetworks


How do you set a CDN up?

Whenever you buy a CDN it provide a complete guide and customer service for setting it up. However below are some steps that generally followed.


Sign up for a service


Know what files you are using :
In many cases you are using a CDN to serve things like your images, CSS, and JavaScript files. Such files cause many problems for your users visiting your website from far off places. Having these assets on servers closer to your users is the entire point of having a content delivery network. There are different offerings from different CDNS, but in most scenarios your HTML will come from your webhost and your assets will come from the CDN.


Getting your files to the CDN servers:
You will need to get the files you want to serve to the CDN web servers. Some places will have options to do it for you almost automatically, some won’t.

Many have automatic setups if you are using WordPress, Joomla, Drupal or other common CMSs (content management systems). The basic premise however is the same across all CDNs. You will need to have your files hosted by the network and this is usually a one step process of loading those files somewhere once and the content delivery network will then replicate those files across their network.


Decide on how to name your URLs (CNAME):
Once your files are on the CDN servers they will have a different URL than they once did. Let’s take an CSS fill as an example. Your old CSS file was at “”, well now that you are using a CDN your CSS file is located at a URL that may look as ugly as this “dfg23345678449dhhty74/style.css” 🙁


Instead that name you can choose to use a name of our own choosing (the most common being “”) and then use something called CNAME to make sure it looks correct and nice to our users. This is a simple step that will result in our CSS file coming from “”.


Ensuring your web pages are calling (using) the files you sent to the CDN:
You can basically just tell your CMS to swap out the files automatically using common caching systems. An example for WordPress would be to use W3 TOTAL CACHE. Any blog should be using a caching system already and if your isn’t then your blog is probably slow. Using caching properly is an important step in improving page speed and will offer massive benefits to your users.


Testing after setup:

Most CDNs will have a page or dashboard where you can see the usage of your files. Other ways of testing would be to use


Whether it affect my website SEO (Search Engine Optimization)?

Yes, in some extent as the file name changes it is going to scare you :-). However the reputable CDNs have good solutions for any common SEO problems.


I think above article will help you to understand the CDN and host your site on CDN and some common configuration settings.


Automatic session timeout/logout using PHP after X Minutes of Inactivity/Idle time

PHP Automatic Session Expire after X Minutes of Inactivity/Idle time

 Automatic session timeout/logout using php


Session timeout is a notion and the only way you make you sure that no session ever will survive after X minutes of inactivity. Session timeout or Session expire depends on the server configuration or the relevant directives (session.gc_maxlifetime) in php.ini.


Typically the default is 1440 seconds(24 minutes), but you can alter the default to something else. Below are some Session configurations.


You can update this easily and without writing custom logic.


If your sessions are implemented with cookies (which they probably are), and if the clients are not malicious, you can set an time limit on the session duration by tweaking certain parameters. If you are using PHP’s default session handling with cookies, setting session.gc_maxlifetime along with session_set_cookie_params should work for you like this:


// server should keep session data for 1 hour
ini_set('session.gc_maxlifetime', 3600);

// each client remember their session id for exactly 1 hour

session_start(); // ready to go!


You can also put this in .htaccess file with a slight change in syntax.


php_value session.gc_maxlifetime 3600

php_value session.gc_probability 1

php_value session.gc_divisor 1


session.gc_probability, and session.gc_divisor directives: PHP has garbage collection it uses to clean up sessions that have expired, otherwise on a site with a lot of users accessing the site could cause a huge amount of session files to be continually generated. Garbage collection does not happen automatically and needs to be incorporated into your system maintenance routines.


You can also make a custom script that automatically logout a user if user is inactive (not performed any action or idle) for X minutes.


1) When user logged in, start session, start session expiry time, like this:

 $_SESSION['expire'] = time() + X*60; 

We took current time, added X minutes in it and stored this in session.


2) At every page check that if X minutes (for above script) have been passed or not make a file as include that in that page, like this:

If yes, clear session and logout, like this:

if(time() > $_SESSION['expire']){
    $_SESSION = array();


And then redirect to login page.


3) In else statement (if X minutes have not passed), reset time (takes current time and add X minutes in it and restore in session named ‘expire’) stored in session, like this:

else { $_SESSION['expire'] = time()+X*60; }


and do nothing – don’t clear sessions, don’t redirect to login page, so that user may stay at website, as much time as he is active.


You can also do this purely using JavaScript. Start an countdown timer. Then wait for activity and reset this timer. If there is no activity and timer goes off, you can call your logoff sequence.

/* Resets the timer. The timer is reset on events
   (mouse-move,mouse-click,key press,scrolling),
   these events occurs indicates that user is active on the application:

<body onmousemove="reset_interval()" onclick="reset_interval()" onkeypress="reset_interval()" onscroll="reset_interval()">

<script type="text/javascript">
//the interval 'timer' is set as soon as the page loads

var timer = setInterval(function(){ auto_logout() }, 20000);

// the figure '20000' (20 seconds) indicates how many milliseconds the timer be set to.

//e.g. if you want it to set 5 mins, calculate 5min= 5x60=300 sec => 300,000 milliseconds.
function reset_interval(){

    //first step: clear the existing timer
    //second step: implement the timer again
    timer = setInterval(function(){ auto_logout() }, 20000);
    //..completed the reset of the timer


function auto_logout(){

    //this function will redirect the user to the logout script
    if(confirm("You have been logged out from the application, Press OK to login again!")){



Hope it will works, let me know by your valuable comments if you need any more assistance.

PHP Traits

One of the problem of PHP as a OOP language is, the fact that you can only have single inheritance. This means your class can only inherit from one other class.


PHP Traits (new feature was added in PHP 5.4) is kind of like a Mixin, allows you to mix Trait classes into an existing class. This means you can reduce code duplication and get the benefits whilst avoiding the problems of multiple inheritance.


As per the PHP Documentation, a Trait is intended to reduce some limitations of single inheritance by enabling a developer to reuse sets of methods freely in several independent classes living in different class hierarchies. The semantics of the combination of Traits and classes is defined in a way which reduces complexity, and avoids the typical problems associated with multiple inheritance and Mixins.


A Trait is similar to a class, but only intended to group functionality in a fine-grained and consistent way. It is not possible to instantiate a Trait on its own. It is an addition to traditional inheritance and enables horizontal composition of behavior; that is, the application of class members without requiring inheritance.


Let’s take some example to make more clear about Traits.


A trait is similar to an abstract class which cannot be instantiated on its own (though more often it’s compared to an interface).


trait Helloworld
      private $message;

      function sayHello() {
        echo "Hello";

      function sayWorld() {
       echo "World";

      abstract function define();


It can have functions, data members, abstract function etc same as class you have.


PHP have some set of rules defined to use Traits in his best way like Precedence, Use of Multiple Traits, Conflict Resolution, Method Visibility etc.

To understand the Traits and how these can be used to make your life easy, I have googled and found some very good example/posts with explanation that will really help you.


One is PHP Documents itself (Bible for all PHP Developers)


Second one is found on SitePoint


Hope these will helps!!


Benefits of Traits

  1. Traits reduce your code duplication whilst preventing complicated class inheritance that might not make sense within the context of your application.
  2. It allows you to define simple clear and concise code and then mix in that functionality wherever needed.
  3. It somehow reduce the situation as generally possible in the multiple inheritance supporting languages called as “Diamond Problem“.

Drawbacks of Traits

  1. Entire class abilities are NOT in one location visually.
  2. You alter the trait thinking it will alter one type of class using it, but may introduce problems in another class also using it. Meaning you will have to know all classes using the trait.
  3. Introduces the idea of multiple inheritance, which bring with it the need to worry about conflicts in naming. (Yes you can solve it using insteadof, but now you have to worry about that and take precautions like specific naming related to the trait or to use this keyword).


So now after have a look on all features and benefits and drawback now a question is, Should you use Traits?
You should, as Traits allow you to create cleaner, simpler, and more efficient code fragments horizontally across multiple classes which do not have to be within the same inheritance hierarchy.

As a developer we have some bad or good situation for all code and logic, so it is your choice how will you play with Traits?