MySQL is Open Source database means its free to use and reliable. that's why lot of application use mysql as a database. But when we going to create a new database, lot of doubts comes in mind like what will be database maxsize, Database engine and number of tables in database.
Before you create you databases, you must think below important points.
- Why I am creating database.
- What are most use of database means going to add/update statement or search the list
- What type of record will be stored means in english character or chinse/japense character
- Maximum records in table.
- What are different types of database engine supported by MySQL.
- What tables will use for tractions
Following are list of different database engines types
| Name | Vendor | License | Transactional | active development |
|---|---|---|---|---|
| Archive | Oracle | GPL | No | Yes |
| Aria | Monty Program | GPL | No | Yes |
| Berkeley DB | Oracle | No | ||
| BlitzDB | GPL | No | Yes | |
| CONNECT | Monty Program | GPL | Yes | |
| CSV | Oracle | GPL | No | Yes |
| Falcon | Oracle | GPL | Yes | No |
| InnoDB | Oracle | GPL | Yes | Yes |
| MyISAM | Oracle | GPL | No | No |
| NDB | Oracle | Yes | Yes | |
| InfiniDB | Calpont | GPL | Yes | Yes |
| TokuDB | TokuTek | Modified GPL | Yes | Yes |
| XtraDB | Percona | GPL | Yes | Yes |
| Federated | Oracle | GPL | No | |
| FederatedX | Monty Program | GPL | Yes | |
| CassandraSE | Monty Program | GPL | No | Yes |
| sequence | Monty Program | GPL | Yes | Yes |
| mroonga | Monty Program | GPL | Yes | |
| SphinxSE | Sphinx Technologies | GPL | No | Yes |
Following are Difference between MyISam and InnoDB
MyISAM Advantage
1. Faster than InnoDB for search results
2. support Full-text indexing
3. When use "select" statement, gives faster results as compare to Innodb
InnoDB Advantage
1. Support for transactions (ACID: Atomicity, Consistency, Isolation, Durability).
2. Better "Row-level locking"
3. Foreign key constraints
4. Repair better incase of table corruption
5. Support Rollback abilities
6. Automatic crash recovery
MyISAM Disadvantage
1. Cascading delete/update not support
2. NOt support ACID compliance
3. Row limit 4,284,867,296
4. When we use indexing, insert/update/delete become bit slow
5. Not Support Row Locking
InnoDB Disadvantage
1. Not support full text indexing (<5.6 mysql version)
2. Can't compressed
3. Search Slow as compare to MyISAM
ALTER TABLE `tableName` ENGINE = MYISAM
Change table engine to innodb
ALTER TABLE `tableName` ENGINE = innodb
