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.
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 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.
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.
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.
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.
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.