View Brian Mansell's profile
MySQL Tip: Using BETWEEN rather than LIMIT
2007-05-08
I've found that when working with a large data set in MySQL (InnoDB) that uses an integer primary key, it can be easier to query a batch of rows from the set using the BETWEEN comparison rather than the LIMIT clause.
The LIMIT clause is helpful in selecting the first X number of rows from within a table, but when selecting X number of rows after Y number rows (e.g. LIMIT 4000000,20000) it can potentially take a very long time to generate as the database is forced to iterate through the first 4000000 before it gets to the batch of 20000 it needs. On the other hand using a BETWEEN comparison (e.g. WHERE id BETWEEN 4000000 AND 4019999) quickly utilizes the integer primary key index to find the rows relevant to the batch.
Note: This method does not insure consistent batch sizes if the tables are fragmented by row deletes.
Test Results
mysql> select id FROM table_a WHERE id BETWEEN 4000000 AND 4019999;
20000 rows in set (10.51 sec)
mysql> select id FROM table_a LIMIT 4000000,20000;
20000 rows in set (1 min 5.19 sec)As you can see the query time of the BETWEEN example is 84% that of the LIMIT example.
Labels: databases, innodb, mysql, optimization, sql
posted by Brian Mansell @ 6:51:00 PM, , links to this post
Easiest way to burn ISOs under Mac OS X
This morning, after downloading Ubuntu 7.04's ISO, I discovered a very easy method for burning an ISO file to a CDR.
- Start Terminal (or ITerm)
- Execute the following command (Note: In this example I'm using the Ubuntu 7.04 ISO)
hdiutil burn ubuntu-7.04-desktop-i386.iso
Within a few minutes your ISO will be ready to boot. Apparently, this method also works with .DMG files.
Labels: cd, iso, iterm, mac os x, ssh, ubuntu
posted by Brian Mansell @ 12:13:00 PM, , links to this post
Global search and replace with Vi/Vim
2007-05-01
Vi provides a few great shortcuts for handling search and replace of text within a file. On occasion, I've ran into situations where I've wanted to rename variables within a file while editting it in Vi.
First of all in order to indicate the entire file as the range of your change: utilize the '%' wildcard. In the example below, all references to 'foo' will be replaced with 'variable'. The 'g' represents that this change should be applied to all occurences (or globally):
:%s/foo/variable/g
If you just want to change references on lines 60 through 150 enter the following command:
:60,150 s/foo/variable/g
For more information on getting the most of Vi, please refer to the Best of VIM Tips.
posted by Brian Mansell @ 8:29:00 PM, , links to this post