Mysql Perfomancing Blog had a nice article on using delayed JOIN to optimize count(*) and LIMIT queries. I just personally optimized a couple of nagging queries with the “delayed limit” approach… very nice.
Posts Tagged ‘TIPS’
Writer’s Block Anyone?
June 8, 2007 – 5:05 pm
Good ole CopyBlogger gives some practical writing tips that might get the prose orientated area of your brain working again.
Used Books & Coupon Codes
May 23, 2007 – 11:01 pm
If you’re buying books on a regular basis, consider buying used on Amazon.com. Used savings is probably in the neighborhood of 30% on average but if you’re lucky you can do much better.
Get additional savings from online coupon codes as well. Finding online coupons can often be a hit or miss type of thing. I find that many sites returned by Google often contain only codes that have expired or have ridiculous restrictions.
I came across a new online coupons codes site called Coupon Chief that seems to organize things in a pretty intuitive fashion (here is the Coupon Chief link to Amazon coupons). If you know of any other sites out there that you prefer or find helpful please share!
This Post Is Sponsored
How-To: Connecting to MySQL Remotely from Windows with SSH
May 21, 2007 – 12:02 pm
Connecting to MySQL Remotely from Windows with SSH
Read the following for an introduction: http://dev.mysql.com/doc/refman/5.0/en/windows-and-ssh.html
Specific Instructions for Connecting with SSH Secure Shell:
(can anyone recommend a better client?)
- Create an initial connection in SSH and verify you can connect.
- ‘Edit Profiles’ and select the ‘Tunneling’ tab for that connection.
- Add an ‘Outgoing’ tunnel as such:
Display Name: MySQL
Type: TCP
Listen Port: 3307 (I used this instead of 3306 because I have a local instance running on 3306 already)
Destination Host: localhost
Destination Port: 3306
Then, if you’re using MySQL Query Browser or Administrator
Create a new connection with the following parameters
- Connection: (enter whatever you prefer)
- Username & Password: (should be your local access, not root)
- Hostname: localhost
- Port: 3307 (see above note for why I chose this port)
That’s It! Enjoy your secure connection!
Delicious Bookmark Tip
May 16, 2007 – 12:05 pm
I like the idea behind del.icio.us (referred to as delicious going forward) but managing all the content or “bookmarks” can quickly become an annoyance as they increase in number. If you use delicious already and haven’t already integrated the newest Firefox plugin, I highly recommend doing so. The initial kinks the plugin suffered from seem to have since been ironed out.
Often times, I’ll find myself wanting to quickly save a site into delicious without taking the time to tag it appropriately. I was previously tagging those sites as “tagme” but have since discovered a better method.
Instead of having a tag like “tagme”, “unread” or even no tag at all, make use of the networking tags that delicious offers and tag it as “for:yourname” (where yourname is your delicious user name.
When using the firefox plugin this becomes a two-click action because the “for:yourname” option becomes listed under the network tags.
As an added benefit, the sites you bookmark in this manner can be found in your delicious inbox which remain marked as unread until you open that folder. Nice huh?
MySQLDump Restore Error
May 4, 2007 – 8:23 am
I’ve been working with MySQL for almost 10 years now. There, now that that’s out of the way, here’s my bonehead move:
Yesterday I wanted to throw a mysqldump created file into a local database so I issued the command:
mysqldump -uuser -p target_database < source_file.sql
MySQL spit out a result I didn’t expect:
-- MySQL dump 10.10
--
-- Host: localhost Database: jrob00_fshub
-- ------------------------------------------------------
-- Server version 5.0.27-community-nt
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
Read More
MySQL 5: Comma & JOIN Precedence
February 28, 2007 – 7:11 pm
I use MySQL 5 on my local development machine which is probably both a good and bad idea at the same time.
It’s a good idea because the SQL I write and execute in the Query Browser will be forward compatible if our production machine ever makes the leap to version 5.
However it’s also a bad thing because syntax that works on older versions doesn’t always work on version 5.
I guess I’ve been lucky so far to not have encountered a query that follows that last rule until just today when I executed a presently known-to-work query that kept failing on my local machine.
It’s a simple query really:
SELECT t1.a, COUNT(*) FROM t1, t2 LEFT JOIN t3 ON t3.a=t1.a LEFT JOIN t4 ON t4.a=t3.a GROUP BY t1.a;
But it yielded the error: “Unknown column ‘t1.a’ in ‘on clause’”
In an attempt to troubleshoot I stripped it down bit by bit until I found that it worked. Basically it worked fine until I attempted to join any tables to the first cross-joined tables.
After banging my head on the table for much longer than I should have, I discovered that the reason this fails is in fact new to MySQL 5. Specifically, it fails because the precedence of commas and joins has been changed.
In previous versions, the comma and join had the same precedence. Begining with version 5.0.12 however, joins were given a higher precedence than commas in order to comply the SQL standard.
Thus the above query actually means:
SELECT t1.a, COUNT(*) FROM t1, (t2 LEFT JOIN t3 ON t3.a=t1.a LEFT JOIN t4 ON t4.a=t3.a) GROUP BY t1.a;
You can read more about it in the MySQL manual (search for ‘Previously, the comma’) but basically this change affects statements that use an ON clause because that clause can refer only to columns in the operands of the join. Since our statement is now trying to join t2 and t3 with a t1 column - it fails.
The fix is to simply supply parenthesis around our first two table calls to override the precedence interpreter.
SELECT t1.a, COUNT(*) FROM (t1, t2) LEFT JOIN t3 ON t3.a=t1.a LEFT JOIN t4 ON t4.a=t3.a GROUP BY t1.a;
I hope this helps someone else. Good Luck
Learning Strategies for Developers
January 12, 2007 – 7:10 pm
I normally link to other articles by submitting them to my ‘asides’ section but I felt this particular article was of unusual interest to readers of this blog.
Ferdy Christant, a developer whom i have referenced in this web space before, has written an article about his philosophy toward balancing the time needed to dedicate toward learning technologies with .. well, the rest of life.
I’m sure you will find it, as I did, to be a helpful and interesting read.
You can find the article here.
Technical Blogging isn’t Easy! Avoid These Mistakes!
October 19, 2006 – 4:02 pm
Okay, I have absolutely no readership right now so maybe this isn’t the best forum to ask for help or tips… but here’s what I’ve learned so far from my new technical blog.
Little or no blogging experience + starting a readable and engaging blog = very difficult (especially true for tech blogs IMHO)
My last post, which talked about version control using Subversion, really really sucked. I’m not quite sure what it is about it that I don’t like but the more I think about it the more I’m sure it is something I hate.
Oh well, chalk it up to a learning experience and try to use it as an example of what not to do in the future, blah blah blah…
How to Start off on the Wrong Foot
1. Liberal use of Cutesy Animal Pics from Flickr
I liked the photo I used from Flickr for my very first post but I think that always trying to work in pics where maybe they don’t belong is a bad idea. Compound that error with a lapse in judgement to use fuzzy wild animal pics and I think we’ve identified a definite formula for nausiating readers.
2. No Paragraph Headlines or Discernable Outline to Follow
I don’t know about the vast majority of blog readers out there but personally, I find that it is only the rare occasion where I am able to read a blog from start to finish without the assistance of paragraph headlines (preferably in BIG BOLD LETTERS). It’s almost like I need something shiny and descriptive to grab my attention and hold my hand through the rest of the article. Otherwise I usually find myself skipping through paragraphs and inevitably bailing out and hitting the back button.
3. Jumping into a Topic Without Introducing It
I can think of a handful of exceptions to this such as…
- you’ve blogged about the topic before
- you know your readers are already informed
- or, the topic is self-explanatory
I’m sure there are a half-a-dozen more exceptions to this but the point is - just because your readers are say, technical readers, that does NOT mean they inherently understand ALL technology! It drives me crazy when people learn I’m a “computer guy” and then ask me to come over and fix their DVD player (note to mother-in-law: please take no offense at that remark; p.s. you’re a great cook).
4. Sounding Like a Freakin Nerd
If you try to communicate technical things to technical people - you do so in a technical way right? Sure, of course you do; technical people speak like that because it’s the most effecient way of communicating. By using technical words and acronyms you’re actually communicating more than you’re saying - which is why non technical people usually say something like, “um… you lost me at hello”.
I’m not sure the best way to avoid this when speaking technically because the last thing you want to do is water down or even skip over technical concepts.
At the same time however, I don’t think it’s fair to assume that all technical people learn in the same fashion. Introducing appropriate visual aids or metaphors might actually enhance the readability of your article to ALL readers! :O
5. You Need to Lighten up Buddy!
It all depends on your writing style of course but a bit of humor never hurt anyone. Okay, that’s completely untrue. If you’re not a humorous person, don’t try to be - you’ll only come across as an un-funny person trying to be something they’re not.
I don’t mean to simply say “be who you are”, because you can get that sort of writing advice anywhere. MY point is, keep it loose. Don’t write like a stiff, even other stiffs don’t like to read that stuff (my apoligies to any stiffs who may disagree).
In Conclusion…
We can apply these points and discover a few things we ought to keep in mind when writing a technical blog post.
- Have you used a cutesy animal pic in the last month? Only resort to this if you’re desperate.. or if you’re blogging on the technical merits of taking cutesy animal pics.
- Use BIG shiny bold headings every three or four paragraphs
- Try to include an introductory sentence or two when rolling out a new topic (or at least a link to Wikipedia)
- Visualize a person telling someone else what you are writing. If those people are wearing pocket-protectors then you might want to consider adjusting the tone of your article.
- Who would sound better reading your article? Jon Stewart? Or Ben Stein? “…anyone…. anyone….Bueller… ” (i love that guy)
…Here’s to learning the hard way! Post your tips/comments!