Skip to Navigation

Database Design

Keeping Solr Search Index Updated with Shared Database Sites

Sites using shared databases for tables like shared users are great for sharing users between sites. It works for many other entities as well. The tricky part is using Solr indexing on this content.

Sites using shared databases for tables like shared users is great for sharing users between sites. It works for many other entities as well. The tricky part is using Solr indexing on this content.

Speed Up Your MySQL Queries Using MD5 and Indexing

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.

MongoDB: A Simple User Directory

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

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

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

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.

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

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

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

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.

Syndicate content