$dev4life = "Software Development Blog"

MySQL - Procedure Analyse

MySQL has a very useful built-in function that helps database administrators optimize the data structures and as a result, it can improve query performance and significantly decrease table size.

Procedure Analyse provides statistics on the data, such as maximum and minimum length of each data column as well as recommended optimal field type and length. In other words, if a field is set to a Signed INT (4 Bytes) and the maximum number that the field holds is no more than 255 and not less than 0, perhaps the field type should be changed to Unsigned TINYINT (1 Byte).
It also provides statistics on NULL values, empties or zeros. Using this information, database administrator can determine whether the field should be set to NOT NULL as well it may help pick the appropriate default value. Please refer to MySQL documentation for more details on data types.

This optimization is especially effective on large data sets since it could significantly decrease the size of the table and improve query performance.
Please note, in order to use PROCEDURE ANALYSE effectively, the table must be populated with some date.

Example:
SELECT * FROM transactions PROCEDURE ANALYSE(10,2000);

Procedure Analyse - database, monitoring, mysql, performance, tuning

MongoDB - Capped Collections

- Create collection
- View collection stats
- Convert existing collection to capped

One of the best features of MongoDB is ability to create capped collections.  Capped collection is a fixed size collection that deletes the old data while the new one is added.  This process is done seamlessly in the background.  Anyone familiar with MySQL and large data sets (over few Gigs) will know the frustration.  Administrator could simply set a collection to be of certain size (in bytes) and the database will take care of everything else.  There are however couple of things to note.  As of right now, capped collections can not be sharded.  Also, MongoDB pre-allocated space for capped collection.

Example
// Create collection
db.createCollection("normal_collection");

// Convert to capped collection (50MB capped)
db.normal_collection.convertToCapped(50000000);

// Check if converted to capped
db.normal_collection.isCapped();

// Create a new capped collection
db.createCollection("capped_collection", { capped: true, size: 50000000 });

// Check collection stats (shows size, count, index information, etc)
db.normal_collection.stats();

mongodb,capped collection, convertToCapped, createCollection, database, isCapped, stats