Home ›

Database Design

 
Printer-friendly version
Syndicate content
Podcast

MongoDB: A Simple User Directory

Submitted by Matt Gibbs on 15 June 2010
in

In my last post, I glossed over MongoDB from a very high level. While it's useful to know how MongoDB works, I didn't cover exactly how to use it.

There are a growing number of NoSQL systems, and one of MongoDB's greatest strengths is its ease-of-use. It takes a couple of minutes to install the server, and commands are given using the familiar JSON syntax.

MongoDB (NoSQL): An Architectural Overview

Submitted by Matt Gibbs on 8 June 2010
in

MongoDB is one of the forerunners in the NoSQL movement, an effort to promote non-relational, schema-free data stores. It lacks any table JOINs, which avoids performance bottlenecks seen with traditional SQL servers. 

MySQL: Open Source Solution for Data Warehousing

Submitted by Chris Hall on 14 May 2010
in

At Forum One, MySQL is the open source database that powers most of our internet solutions. It's flexible, powerful and free. Our confidence extends to one one of the more complex and growing needs facing many of our clients: Data warehousing.

How to Pass MySQL Table and Column Names as Stored Routine Arguments

Submitted by Dan Crowder on 29 March 2010
in

If you are working with or exploring MySQL 5's stored routines you may come across an instance where you would like to pass along a table or table column name as a routine argument. Natively stored routines does not support this behavior but with the help of user variables and prepared statements we can get around this limitation.

Speed Up Your MySQL Queries Using MD5 and Indexing

Submitted by Dan Crowder on 20 July 2010
in

So I created a poorly constructed 'SELECT' query in a stored procedure and I was up late last night trying to figure out how I could best optimize it. After sitting in my 94 degree basement for a few hours, I thought of using hashed values via CONCAT and MD5 functions to create an index of multiple columns to improve 'SELECT' queries using 'JOINS.' The performance increase was instant after I made the necessary adjustments. To save others the time and headache, I'm going to share with you my solution and the resulting performance improvements.

How-to Selectively Import Data from a Data File in MySQL 5

Submitted by Dan Crowder on 4 November 2009
in

If you ever need to import a selected number of columns from a data file in MySQL then this how-to is for you! Recently I had to import a data file (tab delimiter) containing geo-coding data for zip codes into a table that had less columns than the data file and the columns were not in the same order as my database table. Using MySQL's LOAD DATA INFILE 'path_to_file' syntax and @User_Variables I was easily able to get the data that I needed into the table.

Data file colums:

Visualization: Modeling SQL Queries with UML

Submitted by ncowham on 10 July 2008
in

Many people struggle with complex data models that often require multiple tables to be joined together to satisfy a single query. Joining up to three tables is usually not a problem for most DBAs and developers. However, queries that may involve 6 or more tables (including joins on the same table) can often confuse even the most stalwart developer. Troubleshooting such a query can be a major test of faith and fortitude as you scan lines of JOIN expressions and WHERE clauses.

Temporary Tables, SubQueries and Removing Duplicate Records - MySQL 5

Submitted by Dan Crowder on 16 June 2008
in

Sometimes it's just not wise to work on a database after midnight when your senses are not fully alert!

Anyhow, I don't know how I did this but I inserted 7,000+ records for a single content type (restaurants) half of which were duplicate entries.

Success with MySQL and non-Western Character Sets

Submitted by Sandy Smith on 16 April 2008
in

Database Optimization & Vertical Partitioning

Submitted by Dan Crowder on 26 February 2008
in

After doing some poking around on how to optimize databases for quicker reads I came across a rather simple approach called Vertical Partitioning.

This approach is rather useful in taking advantage of the Query Cache as you will ultimately reduce the frequency in which the Query Cache is invalidated (blown away!).

Syndicate content