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:

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.

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.

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

Despite the many strategies that are being promoted for optimizing MySQL 5 databases there is one basic strategy that I believe to be important, optimization with proper use of data types. Granted that other strategies are great, but they essentially rely on a database with tables designed in an intelligent fashion.

The two primary categories which I am going to focus on are Numeric Data Types and Character String Data Types for these are used most often and are usually the ones which need optimizing the most.