My Sql Engine

-
MySQL Storage Engines
MySQL is very different from other databases, in that it's storage engine is pluggable, what I mean by this is that the MySQL server core code is separate from the storage engine, which means that you can have a pluggable storage engine that fits your application. MySQL has over 20 storage engines, here is a list of the common ones



Storage Engine
Transactional Support
Locking Level
Online Non-blocking Backup
Server Version(s) available
InnoDB (default)
Yes
Row
Yes
 5.1, 5.5, 5.6
MyISAM/Merge
No
Table
No
 5.1, 5.5, 5.6
Memory
No
Table
No
 5.1, 5.5, 5.6
Marta
Yes
Row
No
 5.1, 5.5, 5.6
Falcon
Yes
Row
Yes
 5.6
PBXT
Yes
Row
Yes
 5.1, 5.5, 5.6
FEDERATED
No
n/a
n/a
 5.1, 5.5, 5.6
NDB
Yes
Row
Yes
 5.1 and available in MySQL Cluster
Archive
No
Row
No
 5.1, 5.5, 5.6
CSV
No
Table
No
 5.1, 5.5, 5.6




MyISAM was the default engine until recently, this engine has been used since MySQL version 3.2, it is a non-transactional engine and does not implement any additional locking mechanisms, this may cause problems when you have a large number of concurrent writes to a table. If your application has a lot of write activity the writes will end up blocking the reads which will cause performance problems, thus you should use either a InnoDB or Falcon engine.


MyISAM has three files associated with it(.MYI for index file , .myd for data file , .frm for format file, because the three files represent a table they can be simply copied to another server and used, however to avoid corruption you should take down mysql server before copying. The other server must have the same endian format as the source server, so you cannot copy from linux X86 server to a Sparc server for instance.


MyISAM has the following features
  • Non-transactional
  • No foreign key support
  • FULLTEXT indexes for text matching
  • No data cache
  • Index caches can be specified by name
  • Implements both HASH and BTREE indexes
  • Table level locking
  • Very fast read activity, suitable for data warehouses
  • Compressed data (with myisampack)
  • Online backup with mysqlhotcopy
  • Maximum of 64 indexes per table


Comments

Post a Comment

Popular posts from this blog

Triggers in MySQL

-
Image
Trigger MySQL: Trigger can be used in two ways with before and after with Delete ,insert and update command .Let me show you a example of before update   . I have created two table employee and employee_audit . employee table is table where my raw data will be stored and employee_audit table is table where to store data after changes or can say trigger execution . Table structure of employee and employee_audit table : CREATE TABLE   `test`.`employee` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `name` varchar(20) DEFAULT NULL,   `lstname` varchar(10) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; CREATE TABLE   `test`.`employee_audit` ( `id` int(11) NOT NULL AUTO_INCREMENT,   `name` varchar(20) DEFAULT NULL,   `lstname` varchar(20) DEFAULT NULL,   `action` varchar(50) DEFAULT NULL,   `upd_datetime` datetime DEFAULT NULL,   `newname` varchar(20) DEFAULT NULL, ...
Read more

MySQL 8.0.2 Windows Function

-
Q : What is sql windows function  ? Ans  : a windows sql function will perform aggregation for each row in result set  , letting  each row in  the result set , leatting each row retain its identity . Example  : mysql> CREATE TABLE t(i INT); mysql> INSERT INTO t VALUES (1),(2),(3),(4); mysql> SELECT SUM(i) AS sum FROM t; +------+ | sum  | +------+ |   10 | +------+ mysql> SELECT i, SUM(i) OVER () AS sum FROM t; +------+------+ | i    | sum  | +------+------+ |    1 |   10 | |    2 |   10 | |    3 |   10 | |    4 |   10 | So  you can see only by  using over()  windows function  , sum is showing along with  each value that  has been summed . Q: what are types of windows sql function  ? Ans :  sql aggregate function can be used in ...
Read more

list in python

-
# declaring a list list_t = [ "my" , "name" , "is" , "abhishek" ] print (list_t) # accessing first list item print (list_t[ 0 ]) # accessing all item of list for x in list_t: print (x) # inserting item at position in list . list_t.insert( 0 , "hello" ) print (list_t) # extending a list lastname = [ "rathor" ] list_t.extend(lastname) print (list_t) # removing item from list list_t.pop( 0 ) print (list_t) list_t.remove( "rathor" ) print (list_t) # index of item in list print (list_t.index( 'name' )) # sorting an index list_t.sort() print (list_t) # reverse is also possible in index list_t.reverse() print (list_t) # constructing a list with constructor list2 = list (( "abhishek" , "pratap" , "singh" , "rathor" )) print (list2) # tuple and list both behave same except tuple are unchangeable and ordered collection # so inserting and removing ...
Read more