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 ‘DATABASE’
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