Use MySQL's Modulo Operator to Update in Batches

Sometimes you need to roll out your nefarious plan slowly and MySQL's modulo operator can help.  Here we do the first of ten batches:

Rails ActsAsList - Initializing position Values

Recently I worked on a project that had a model acting as a tree with the children acting as a list within the scope of the child's parent. The old school ActsAsTree and ActsAsList plugins used in combination, handle this scenario nicely. There was one problem however - we had a large "tree" to load in where only some of the children were ordered under their parent. The rest of the children had a NULL value sitting in the position column. Because ActsAsList works by setting "position = position + 1 WHERE position >= x" when insert_at(x) is called this wasn't going to work with the data as is. "NULL = NULL + 1" just doesn't seem to work out too well. I decided to pre-process the data putting the children in their natural position order. In this case the natural position order would be by the provided position with a secondary sort on short_name if the position was not given. Also, when a child list contains both numeric positions and NULLs the NULL children should go to the bottom. Given the number of rows I was processing I knew this was going to take some time so I decided to see how this would perform when implemented as a MySQL stored procedure. Here is the code I came up with shown in the migration that creates the stored proc:



And here is the rake task to call it:



The stored procedure took just over a minute to do the renumbering. After implementing I decided to compare this to a straight SQL version that would be portable to different databases - but not as fast. I tried to avoid an ActiveRecord tax here and did the SQL in raw execute statements. Here is the rake task:



The final results showed the stored procedure technique to be about 5 times faster than the SQL technique.

Quicker Rails Seed Data Loading

The word is that Rails 3.0 will feature a way to load seed data. This is sure to be a handy and needed feature, however, when loading large amounts of seed data you are probably going to have to abandon ActiveRecord and / or fixture style loading in your db/seeds.rb file in order to get the kind of performance you want. Recently I set up a way to load seed data for a Rails 2.2.2 project which exploits the "LOAD DATA INFILE..." command that MySQL provides. This cut the data loading time for my particular data set from over 15 minutes (I gave up waiting) to less than 30 seconds. This technique is likely to remain relevant for your future db/seed.rb files. The following rake tasks set up my Rails application to load ".psv" and ".yml" seed data files from the db/seed directory. The ".yml" files are normal YAML fixture files and are loaded via my rake tasks using an ordinary fixture technique. The ".psv" files are pipe separated files which are loaded use the above mentioned "LOAD DATA INFILE..." command. The way I have this set up here the order of the columns in your ".psv" needs to match the column order in your database so you may want to tweak this code a bit and provide parameters to specify a different column order. In other words, YMMV, anyway, code follows:



One thing to look at carefully when using this technique is what your database product does with "empty" values in your data set. MySQL didn't want to seem to let a empty numeric value be NULL which caused me to go with some data massaging before and / or after load.

Rails ActiveRecord Order by a Function

Of course you can pass functions in the order clause through ActiveRecord finders:



In the above case a Foo has fiscal_year and fiscal_month columns unfortunately stored as VARCHAR(255) containing, for example "2008" and "7". While the sorting will work that query will be able to leverage an index on these columns only if the database used can create indices that include functions. I'm not sure which databases can do this. From a quick look at the SQL Server and MySQL documentation it doesn't appear that either of these databases can do this.

Rails Caching and MySQL GROUP_CONCAT

A recent caching solution that we came up with involved using the hash of the concatenated "children" ids of another resource to identify when the identical and previously stored children payload could be delivered instead of constructing the payload from scratch (which is kind of expensive). Our current solution uses action caching and a custom ActionController::Caching::Fragments::FileStore. In our custom FileStore we override the real_file_path(name) method to use this "children signature" as part of the name. The class looks something like this:



This seems to work well and the next step is likely to look at using the children_sig value for the ETag as well.

One interesting thing that came out of this investigation was that I wondered if I could produce the hash using straight SQL - should be much faster right? Turns out that with MySQL there is a built in aggregate function that concatenates a column and it is called GROUP_CONCAT. To generate that hash in the database with MySQL the following will work:



Pretty cool! I'm not sure which other database products support this as a built in aggregate function but it doesn't appear that SQL Server does.

I found the following recent blog posts talking about other uses for GROUP_CONCAT as well:

http://tempe.st/2009/03/the-thrill-of-a-new-technology-couchdb/
http://www.christianmontoya.com/2007/09/14/mysql-group_concat-this-query-is-insane/
http://db4free.blogspot.com/2006/01/hail-to-groupconcat.html

SQL - Last Index Of lastIndexOf

Quick flush - lastIndexOf in two varieties of SQL plus using the technique to cut off the last segment in a materialized path:

Transfering mysql Data using mysqldump

In the last post I showed a script for truncating all the tables in a mysql database. In this post I will now show some command line techniques for loading the truncated database with data from another database. For this task you can use the myqldump command. For example to dump all the data from a database to a text file you can use a command like this:



Executing this command with prompt the user for a password, and then dump the data from database_name out to a text file called dump.sql. The -t switch makes it so that it only dumps data, and doesn't dump DDL statements that delete and recreate the tables before loading the data. You may or may not want to use this switch depending on your situation. This dumped data can now be loaded into another database, such as one we truncated with mysqltrunc, with the following command:



Remember that because of the -t option used previously, the dump.sql doesn't contain DDL command to drop and recreate the tables, thus your target database needs to have the same schema or the dump will fail. Once again you may or may not want to use the -t switch depending on your situation.

ssh allows you to execute commands remotely. This can allow you to quickly transfer data from a database in a remote server. For example:



With these techniques you should be able to do things like transfer production data to your local computer to replicate a problem with a minimal amount of effort.

Truncate all Tables in a MySQL Database

UPDATE: I've written a new post with an updated script that encourages safer password handling. You should go there instead.

Here is a handy script to truncate all the tables in a MySQL database. I use this sometimes to wipe out an entire database before loading in some production data to replicate a customer problem:



This script isn't perfect -- it should really prompt the user for his password so that these passwords don't get into bash history logs. In a later post I'm going to talk about using this command in conjunction with some command line MySQL command to load a remote database locally for testing.