Wednesday, August 01, 2007

MySQL

What a day. Billie's always giving me a hard time for running queries in Access, even going so far as to describe Access as "a tool that lets people who don't know what they're doing do things they shouldn't."

Hmph.

Well, I'm always looking to learn new skills, and I try to be open to using new tools. Besides, I spend a lot of time waiting on my computer to finish running a query, so I'm definitely interested in upgrading. So I downloaded MySQL and started playing with it.

These are my discoveries so far:

1. The wizards did a great job of importing all my tables, but none of my queries made the transition, unless they're hidden somewhere. So I have all my data, but none of the tools I use to organize it or run reports. Recreating those will be a lot of work.

2. For data that updates regularly, I routinely just link the table in Access to a text file that I refresh. Saves a lot of time and effort. I'm sure there must be some way to do this in MySQL, but I'll be damned if I can find it.

3. For really big datasets, I'll import the text. But if I'm dealing with fewer than ten thousand records, I'll usually just copy and paste them into Access. The MySQL Query Browser I'm using doesn't seem to support this.

4. Speaking of building queries, I tend to think of databases in visual terms. So when I'm putting something together than pulls from a variety of different tables, I essentially need to visualize it, then figure out the SQL, then build the query. In Access I can build as I visualize. Much easier, much faster. I know there are so-called visual query builders for MySQL, but I've got no idea which ones are good and which ones suck. Anybody out there in the Intertubes got any advice?

5. Speed. I wrote a very simple query using MySQL, having it go through a list of titles and pull out the ones where the publisher was ______. Now, in Access, I could have scrolled down the list until I found an example of the kind of title I needed, copy the pubkey, paste it into the query. In MySQL, I had to write the damn thing from scratch. Fine. Different tools, different techniques. But Access showed me the results in about 15 seconds, and MySQL took three minutes! Of course, the results screen told me the query ran in 0.04 seconds. Which is nice, I guess, but what was the program doing with the other 179.96 seconds?

MySQL is really more of a platform than it is a piece of software, so maybe it's that I'm just using the wrong applications. Anybody out there got recommendations for good ones?

But I suspect that the real problem is that I'm an analyst and a dataminer, not a programmer. I don't need something to serve up webpages or maintain a dozen different user accounts, all of which I'm confident MySQL does very well.

I run reports. Granted, they're fairly complex reports. Some of them pull data from more than a dozen tables, and some of those tables contain millions of records. I'd love to find something better than Access for what I'm doing, but I've got a feeling that using MySQL to run these reports is like using a water jet to cut bread.

No comments: