Tag Archives: InnoDB

What are the different tables(Engine) present in MySQL, which one is default?

by

Following tables (Storage Engine) we can create :
1. MyISAM (Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension. )
2. InnoDB (The default storage engine IN MYSQL) (InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data.)
3. Merge
4. Heap (MEMORY) (The MEMORY storage engine creates tables with contents that are stored in memory. Formerly, these were known as HEAP tables. MEMORY is the preferred term, although HEAP remains supported for backward compatibility. )
5. BDB (BerkeleyDB) (Sleepycat Software has provided MySQL with the Berkeley DB transactional storage engine. This storage engine typically is called BDB for short. BDB tables may have a greater chance of surviving crashes and are also capable of COMMIT and ROLLBACK operations on transactions)
6. EXAMPLE
7. FEDERATED (It is a storage engine that accesses data in tables of remote databases rather than in local tables. )
8. ARCHIVE (The ARCHIVE storage engine is used for storing large amounts of data without indexes in a very small footprint. )
9. CSV (The CSV storage engine stores data in text files using comma-separated values format.)
10. BLACKHOLE (The BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty result)

Duplicates Records in Multiple Fields with MySql

by

Retrieving count of Duplicated Records

SELECT firstname, lastname, COUNT(*)
FROM staff
GROUP BY firstname, lastname
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;

Retrieving The Individual Duplicated Records

SELECT stafftable.firstname, stafftable.lastname
FROM staff stafftable
INNER JOIN (
SELECT firstname, lastname, COUNT(*)
FROM staff
GROUP BY firstname, lastname
HAVING COUNT(*) > 1) temp
ON temp.firstname = stafftable.firstname
AND temp.lastname = stafftable.lastname
ORDER BY firstname, lastname

Can we have multiple primary keys in a single table?

by

No we Cannot.
For example,

CREATE TABLE IF NOT EXISTS `usr` (
`id` int(11) NOT NULL,
`survey_id` int(11) NOT NULL,
`number` int(11) NOT NULL,
`count` int(11) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`,`survey_id`,`number`,`count`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Basically, this appears to be creating four primary key on one table.

We could create a number of unique keys in one table, but only one primary key.

Then how is it that your system is allowing the creation of multiple primary keys?

Think of it like it suggest, a ‘KEY’. So the key would be all of the columns specified. In your case you can have multiple rows with the same ‘ID’ and multiple rows with the same ‘survey_id’ but there shall never be two rows that have the same ‘ID’ AND ‘survey_id’.

So in this case it is not saying that the column ‘ID’ must be unique nor is it saying that ‘survey_id’ must be unique, but only the combination.

Like if you write an query

INSERT INTO `usr` (`id`, `survey_id`, `number`, `count`, `date`) VALUES (1, 1, 123456, 50, ‘2013-04-01’);

And after this you again run this query then you are getting an error “#1062 – Duplicate entry ‘1-1-123456-50’ for key ‘PRIMARY'”

What is the differences between InnoDB and MyISAM?

by

InnoDB

MyISAM
InnoDB implements row-level locking. MyISAM implements table-level locking.
Automatic Crash Recovery. No Crash Recovery
InnoDB doesn’t have FULLTEXT search indexes. MyISAM have FULLTEXT search indexes.
InnoDB also implements transactions, foreign keys and relationship constraints. MyISAM does not support transactions, foreign keys and relationship constraints.
InnoDB stores both data and indexes in one file. MyISAM stores indexes in one file and data in another.
InnoDB uses a buffer pool to cache both data and indexes. MyISAM uses key buffers for caching indexes and leaves the data caching management to the operating system.

In InnoDB the COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used) execute slower than in MyISAM because the row count is not stored internally.

Overall I would recommend InnoDB for most purposes and MyISAM for specialized uses only. InnoDB is now the default engine in new MySQL versions.