Database Partitioning vs Sharding

 

Partitioning is dividing your logical data into multiple entity to achieve more performance,Availability or maintainability. It has two types:

 

  1. Vertical Partitioning:-“Vertical partitioning” is the act of splitting up the data stored in one entity into multiple entities – again for space and performance reasons.  For example, a customer might only have one billing address, yet I might choose to put the billing address information into a separate table with a CustomerId reference so that I have the flexibility to move that information into a separate database, or different security context, etc.
  2. Horizontal Partitioning(Sharding):- When you shard a database, you create replica’s of the schema, and then divide what data is stored in each shard based on a shard key.  For example, I might shard my customer database using CustomerId as a shard key – I’d store ranges 0-10000 in one shard and 10001-20000 in a different shard.  When choosing a shard key, the DBA will typically look at data-access patterns and space issues to ensure that they are distributing load and space across shards evenly.This also improves the write speed because Sharding is like Shared nothing architecture.

Summary: Partitioning is generic term which is dividing the logical data into multiple entities. Either you can do it horizontally by dividing the data of an Entity by replicating the schema over multiple machine and dividing the data over multiple machine based on the shard key (Sharding). or we can divide the data into multiple entities on the same machine which is Vertical Scaling.

 

Finding Nth highest salary using SQL interview question

This is one of very common question to find the Nth highest salary using sql.

Create table Command:-

 

  1. Generic Solution:- This solution use correlated subquery. Below given query can be use to find 2nd, 3rd……nth highest salary in an employee table. This solution will work on any database but it can little slower due to subquery.

     
  2. Mysql Specific Solution:- In Mysql we can use limit to solve this problem and it is fast solution but it is vendor specific.

     

    Please add comment if there is any better solution. Thanks in advance.

Mysql like AES_ENCRYPTION and AES_DECRYPTION in groovy/Java

In my recent project there was a requirement of encrypting the data in Database but same was needed in the application level as well so at the mysql end i used AES_encryption() and AES_decryption().

e.g.

and in the Groovy/Grails/Java (application end) same can be achieved using the following code :

The main thing to note in the code given above is that for encryption first you need to encrypt data and then apply hex and in case of decryption unhex the data and then decrypt the same.

The main thing to note in the code given above is that for encryption first you need to encrypt data and then apply hex and in case of decryption unhex the data and then decrypt the same.

This helps me hope that helps you guys!!!

Thanks,

Anuj Aneja

Removing/replacing special character from database.

Recently in one of my project i faced a problem that database was having special character, which is shown as space on the User Interface. So as to solve this issue i found a very simple solution which consists of following steps:

1. First, you just need to identify which type special character to be removed/replaced like in my case it was shown as space but stored as <?> in the db.

For that you can use the query e.g.

and you can also refer to this link for ASCII code of character. In my case it was 160.

2. Now, next step is simple just run this query.

Thats it !!!

It helps me a lot!!! Hope that helps you guys!!!

Anuj Aneja