CORE FEATURES IN MySQL

 STRUCTURED DATABASE

                                            Structured database are traditional database that have used in many enterprises for more than 40 years , However data volume becoming bigger and bigger common need have take place data analytics.

                                           Basically data in a structured database has fixed filed, a dates, time, addresses, currencies  and so on


Release


 version 5.0 GA on 19th oct,2005

  version 5.1 GA on 14th nov,2008

  version 5.5 GA on 3th dec,2010

 version 5.6 GA on 5th feb,2013

 version 5.7 GA on 21th oct,2015

MySQL 8annouuced on 12th sep 2016


Database storage engines and types


INNODB:


 InnoDB is an default storage engine all other available storage engines its was relased with MySQL 5.1

    InnoDB tables support ACID-compliant commit,rollback and crush recovery capabilities to protact user data

    it also support row-level l:ocking

    it stored data in cluster indexes to reduce I/O operation

    for all SQL select queires based on primary key.it also support FOREGIN KEY 

    the max size of an  InnoDB table up to 64TB



MYISAM:
MyISAM was the default storage engine for MYSQL 

      MyISAM storage engine tables do not support ACID-compliant as opposite to inooDB

      MyISAM support table-level locking only

             MyISAM tables are not transaction safe

      MyISAM max size of growth up to 256TB

      MyISAM support full-txt indexing, using full-text indexes, we can index data stored in BLOB and TEXT data type


MEMORY 


             Memory is generally known as Heap storage engine

    It is used to access data extreme quickly

    This storage engine stored data in the RAM so it wouldn't need to I/O operation

    This engine is support  table-level locking

  important

     the memory table stored data in ram ,which very limiting storage capacity.if to try to write to much data into memory table, it will start swapping data in to the disk and then you lose the benefits of storage 

     This table don't support TEXT and BLOB data type

     memory table support B-tree indexes and Hash indexes


Archive


This storage engine is used to large amount of historical data without any index

   archive table do not have storage limit


    The archive storage engine is optimized for high insert operation 

   also support row level locking

    This tables stored data in a compressed and small format

    The archive engine does no support DELETE or UPDATE operation

    It only allow INSERT ,REPLACE and SELECT operation



BLACKHOLE


   This storage engine accepts data but does not stored it.

    This engine is useful for replication with large number of server 

    A blackhole act as a filter server between the master and slave server

   only apply

           Replicate-do-* and replicate-ignore-* rules and write a bin logs. These bin logs are used to platform replication slave server


MERGE


 this storage engine is also known as MRG_MYISAM storage engine. This storage engine merges a MyISAM tables

    For a merge table, all column are listed in the same order .these tables are good for data warehousing environment

    MyISAM tables have storage limit for the OS ,but a collection of MyISAM (merge)table do not have storage limit. so using a merge table would allow you split  data into multiple MyISAM tables, which can help in overcoming limit


Federated


   This storage allows you to create a single database on multiple physical server.

                 It open to client connection to another server and executes queries against the table there, retrieving and sending rows as needs


NDB cluster

The NDB cluster storage engine can be configured range of failover and load balancing

options but it easy to start storage level engine cluster level

Each part of the cluster is consider to be a node


                  search engine: NDBcluster

   transaction data: InnoDB

    session data: MyISAM or NDBCluster

    Localized calculations: Memory

    Dictionary:MyISAM

Comments

Post a Comment

Popular posts from this blog

Python if....elif....else statements

Image
python if... Statements:                python if..  statement is used for decision making operation.      It contains the body of code which will run only when the conditions given in the if statement is true if only ---- when condition True : if.. flowchart Example: #if only ------ condition true x = 100 y = 50 if x > y: print ( "greater then y" ) print ( "condition true" ) Output: greater then y condition true                In this example we have two variable x=100,y=50 and we create if expression [if x > y:] then we give statement [print("greater then y")]    if condition expression is true then statement is pass .then outside of if statement print will be execute. if only ---- when condition False:               I n this example we have two variable x=100,y=50 and we create if expression [if x < ...
Read more

What is InnoDB - Why InnoDB use - ACID properties

InnoDB innodb is a storage engine database management system (DBMS) MySQL and MariaDB InnoDB release on MySQL 5.5.5 in 2010 its provide ACID-compliant transaction features what is InnoDB in MySQL InnoDB has evolved from being a storage subsystem to a general-purpose storage engine for MySQL.                  Its combination of high performance and high reliability,                  It was made the default storage engine from Version 5.6 onwards.                MySQL can handle large volumes of reads and writes                A DBMS is a tool that allows you to store, index, and retrieve data in a table ACID:                  Atomicity, consistency, isolation, durability is a set of properties of data...
Read more

Object-oriented programming (OOPs)-python

 OOPs Concepts:                  An object-oriented programming is to design the program using classes and objects.     The object is related to real-word entities such as book, house, pencil, etc.     OOPs concept code writing is reusable code Principles of OOPs:                  C lass       Object       Method       Inheritance       Polymorphism       Data Abstraction       Encapsulation Class:         Class can be define as collection of object. The class have some specific attributes and methods.   For example: If you have school class, The it must contain an attribute and method     i.e an student name,roll num,email id,age, DOB Example:               ...
Read more