Deprecated: Function set_magic_quotes_runtime() is deprecated in /home/mwexler/public_html/tp/textpattern/lib/txplib_db.php on line 14
The Net Takeaway: Sometimes, you need a quick database...


Danny Flamberg's Blog
Danny has been marketing for a while, and his articles and work reflect great understanding of data driven marketing.

Eric Peterson the Demystifier
Eric gets metrics, analytics, interactive, and the real world. His advice is worth taking...

Geeking with Greg
Greg Linden created Amazon's recommendation system, so imagine what can write about...

Ned Batchelder's Blog
Ned just finds and writes interesting things. I don't know how he does it.

R at LoyaltyMatrix
Jim Porzak tells of his real-life use of R for marketing analysis.






Sometimes, you need a quick database... · 03/28/2008 02:48 AM, Database Analysis

I was working with one of the smart guys here at Y!, and he was trying to merge together a bunch of files and run some counts; the combined file size was approach 3 million lines across 32 columns. He was using MS Access 2003 as his database, and it was groaning under the strain.

MS Access has been around for a long time, and it’s JET engine age is starting to show. Even with the upgrades to the Microsoft Desktop Engine (MSDE) (which itself was replaced by the SQL Server 2005 Express Edition and then something called the SQL Server 2005 Compact Edition), it’s still kind of screams “Look, I’m better than dBase III, so you should just be happy, sit there, and shut up”.

I decided to do some digging and see if we could find something faster but still easy to use. I also wanted it to be low impact on the system, something that could be blown away easily without messing up the box.

It turns out to be harder than it looks. There are a collection of Portable WAMPS (Portable meaning installs in one directory, suitable for a USB drive; WAMPs are Windows Apache MySql Perl/Python stacks) like XAMPP but if you just want MySQL, its hard to track just that., my usual source, was lacking this (though, as usual, I downloaded a bunch of other useful apps in portable format: great to unzip, use, blow away…)

I finally found a good one here at called PortableDB. Its a nice MySql 5 distro for Windows with simple tray-based start and stops. Biggest issue: it uses a different port from the usual MySql installs (33060 instead of 3306) so some programs get confused.

Ok, database: check… but what about access tools? Portable and free were my requirements, but not much out there to support Import, Export, and a good Sql query window.

If the tool is a “native MySQL” tool, it may let you make new databases. Java and “generic” tools may not let you do this (though they can make new tables, etc.), but it’s pretty easy to get the MySQL command line up and running and use that to make the database. You may be able to do it from within the tool’s query window, but its just safer to do this from the MySQL command line prompt.

The other gotcha is that some of these tools limit the rows returned to reduce memory consumption (it takes lots of memory to set up those cute grids). It’s worth checking the Preferences and hunting around to see if they aren’t putting some kind of LIMIT or other restriction. Sometimes you really need to get down in the data and not being able to get more than, say, 4,000 rows can cause an issue… esp. if you miss the fact that it’s your query tool stopping you; there might be nothing wrong with the data.

Some options:

Yes, MySql has its own graphic tools, but they are both hard to use and underpowered (see MySQL Query Browser for example). Yes, you could install a WAMP and use PHPMyAdmin which is an ok idea for simple stuff, but analytic queries benefit from a gui with copy/paste, sorting, multiple windows, etc. Yes, you could use MSAccess via ODBC, but that’s kind of defeating the purpose, isn’t it? Another option might be to try OpenOffice’s Base, which includes both HSQL and ODBC, giving you two options, but it suffers from trying to be too much like Access.

So, HeidiSQL seems to be the winner, esp. in the PortableApps version. But it might be worth playing with some of the others to see if they meet your needs, esp SQLYog in the Lite/Community edition.

BTW: interesting find, might be cool:
Toad for Data Analysis and Toad for Data Analysis

PS: To import CSV in HeidiSQL, you first need to make the table (which can be a pain) and then configure the Import like this:


terminated by ;
enclosed by “ (check the Optional box)
escaped by \\

terminated by \r\n
ignore 1 Lines (if your first line is a header)

If you want other ways, consider this sql statement:

load data local infile ‘uniq.csv’ into table tblUniq
fields terminated by ‘,’
enclosed by ‘”’
lines terminated by ‘\n’
(uniqName, uniqCity, uniqComments)

LOCAL means take from local filesystem. “Ignore Row “can be used to skip the header.

There is also a “wrapper” program that MySQL includes which tries to give you many steps in one command line:
mysqlimport —fields-terminated-by=”,” —fields-optionally-enclosed-by=”\”“ —lines-terminated-by=”\n” geoip /mnt/www/csv.csv -u root -p

Note that you still need to create a table ahead of time, which is a pain: mysql -e ‘CREATE TABLE tlbUniq(id INT, n VARCHAR)’ test

I am kind of amazed: there is no real free “import wizard” ala the Excel or SPSS wizard. It would be pretty easy:
Read file, print the first few lines, try to guess format of the data, llet the user correct, and then create the table and import. Yet no one has done it yet? Surprising…

* * *


  1. “MyJSQLView doesn’t look great, but it appears to be trying
    to put the right features in and could be a contender if
    they keep improving.”

    Well not sure if much more will be done to make MyJSQLView
    look great, but we’ll keep working on putting in the
    features that forward the team’s agenda. Not great review,
    but good enough. Thanks for the feedback.

    MyJSQLView Project Manager

    danap    Apr 2, 03:29 PM    #

  2. Fair point. From a GUI pov, compare yours to, say ExecuteQuery. I think EQ looks much cleaner and easier to use. However, your functionality is pretty good and I think that as your GUI improves, you will have a really great tool.

    Michael Wexler    Apr 2, 03:50 PM    #

  3. Well, maybe MyJSQLView should take another look at
    the GUI. I agree with you about ExecuteQuery and did
    do a review of their tool. I found it also to be very
    nicely put together. specifically News: 04/11/2007 MyJSQLView Tool Review & Development

    In the last release for MyJSQLView an import for CSV
    was updated to allow updates as well as inserts. I
    seriously reflected on putting together a visual type
    of wizard like the office word processors have as you
    suggest but the project got tied up in more major goals
    for the release.


    danap    Apr 2, 07:59 PM    #

  4. hey, thanks for your tips but i got some thing greater than this. sqlyog filled with great features and offered under open source for free. Its with improved gui and efficient tool. just take a look

    Martin Digg    Jul 8, 10:59 AM    #

  5. I actually do mention SQLYog which I call the best of the “Lite” versions. It is a commercial product by default; the “community” open source edition is missing much functionality compared to both it’s commercial counterpart and other open source tools.

    Still, if you want a “Lite” version of a commercially supported tool so you have an upgrade path, SQLYog is the best of what’s out there.

    I suggest starting with some of the open source tools such as HeidiSQL to see if they meet your needs, however. If not, SQLYog would be the next step.

    See Comparison of SQLYog Community vs. Enterprise for more details.

    Michael Wexler    Jul 8, 11:20 AM    #

  Textile Help
Please note that your email will be obfuscated via entities, so its ok to put a real one if you feel like it...

powered by Textpattern 4.0.4 (r1956)