Yes hints are evil, because they circumvent the entire beauty of runtime query optimizers. However if you really do know whats best and know that your query optimizer really does not know whats best, even if you make sure the table stats etc are all in order you can decide that "hardcoding" the query execution plan is best.

Now SQL tuning books devote entire chapters on how to add redundant where conditions, redundant additions and multiplications. The last such chapter I have read was in "SQL tuning". There it gave a tip on how to control the order in which joins are done using the following trick:

... AND T1.Key2_ID=T2.Key2_ID
AND T1.Key3_ID+0*T2.Key2_ID=T3.Key3_ID ...

Now this is getting insane! It seems obvious that there is a demand for being able to restrict or even hardcode the entire query execution plan! However the SQL standard is …

I’m preparing to speak at the next MySQL Brisbane Users Group in Febraury 2006. My topic will be Know your competitor - A MySQL Developers Guide to Using Oracle Express Edition.

Having a strong background in Oracle, and having been using MySQL for the past 5 years, the release of Oracle Database 10g Express Edition (XE) as a Free offering (with limitations of 1 CPU, 1GB Ram, and 4GB disk) is an interesting move by Oracle.

I’ve written a number of recent comments on various Oracle/MySQL things including Responses to some Oracle v?s MySQL Questions, How can Oracle 10g …

In the “Australian Technology and Business Magazine” - December 2005 edition there was an article on comparing database products. Here are my comments, which I also plan to forward to the editor.

BTW: I’ve since also found this articles content on another site here. It seems that most if not all is the same.

In response to your cover story article “The Usual Suspects Four databases we suspect your business could be quite interested in.” which appeared in the December 2005 edition, I would have to sum up your article in one word “Disappointing”. Let me provide some feedback from my perspective.

You start by defining a scenario, which is the only approach you can take for a suitable comparision of database products due to diversity of features available in …

Having a sysadmin background, I am fairly familiar with cron. I’m amazed at how many companies set up cron jobs and assume everything will just work. If root mail is not checked (or forwarded to someone who actually reads it), a cron job could fail and you would never know.

Having an automated task fail silently is a Bad Thing ™. I take commands which should have no output normally (such as mysqldump > dumpfile.sql and mysql < maintenance.sql) and pipe the output into a script called mailif. The script will e-mail a specified recipient if standard input is not blank.

In plain English, if the automated task has ANY output or error code, it e-mails me.

One of my current tasks is going through all the database maintenance scripts and making sure it e-mails me if there’s an error.

Standard cron entries have 6 …

There was a brief discussion on adding ways to hint selectivity in #postgresql this morning. Often the database has trouble figuring out a proper plan for a given query. One solution to this problem is to let the RDBMS analyze the data in the table. But even that falls flat in some situations, even if it has reasonably up to date table stats. Maybe the effort is just too high to make it worthwhile to figure things out at runtime.

Even generating those stats can take ages if you have a lot of data. In that case you can sometimes get by with analyzing sample data, sometimes this does not work though. Also some applications, I hear SAP is one of them, loves temporary tables. So these tables are just filled during some operation and then purged again. However in order to generate proper table stats you need to run the analyze on the table in exactly the state that is most relevant to performance. Otherwise you can totally obliterate your …

Right now, I’m craving the INFORMATION_SCHEMA database. Specifically, to compare tables for similarity. Sure, I can diff the results from SHOW CREATE TABLE, or mysqldump, but having the INFORMATION_SCHEMA will be much more handy.

Sigh.

What is Web 2.0? Well the definitions out there aren’t clear and precise. Tim O’Reilly from O’Reilly Publishing has a detailed description at http://www.oreillynet.com/lpt/a/6228. (More notes from this below) His compact description is:

“Web 2.0 is the network as platform, spanning all connected devices; Web 2.0 applications are those that make the most of the intrinsic advantages of that platform: delivering software as a continually-updated service that gets better the more people use it, consuming and remixing data from multiple sources, including individual users, while providing their own data and services in a form that allows remixing by others, creating network effects through an “architecture of participation,” and going beyond the page metaphor of Web …

So, yesterday, just one day after writing a short article about ENUM and SET and having it pointed out to me that data does not have a place in data structures (which is absolutely correct!), what did a developer want me to do at work but change an existing ENUM to add one item?

Well, having learned a bit, I suggested an alternative. The performance of our site depends on using ENUMs in the way I described, so we must continue to use them. Additionally, I was listening to Robin Schumacher making comments about how some folks “over-normalize” their data and need to do too many JOINs as a result.

Technically speaking, it’s not possible to “over-normalize” a data structure. But I’ll let that slide, because I know exactly what he meant.

At any rate, the alternative I suggested was to add …

I recently picked up two O'Reilly books for $20 each: "High Performance MySQL" and "SQL Tuning". I am almost done with "High Performance MySQL". Overall I would say it’s a good book. Not suprising from the original publication date it is focused on MySQL 4.1 and therefore does not cover a lot of the advances made with MySQL 5. Especially replication comes to mind, but also little things like index usage for MIN() and MAX(). It does however contain a few notes here and there about changes/enhancements to expect with MySQL 5.

There are a few things I hope will be improved in the 2nd edition.

• The books reads well, almost like a normal discussion. The authors give anecdotes to explain challenges and then show solutions. However sometimes I would have wished for fewer anecdotes and more examples. Like in the chapter 5 where EXPLAIN is covered. I would have wished for way more sample outputs and maybe a short note about what the …

So, I have not been able to find a MySQL magazine in print. Why is that? We’ve got great blogs out there, you might argue, and http://www.planetmysql.org is practically its own magazine, though not in print.

Still, it seems like it would be worth the effort. Thoughts? (http://mysql-journal.com does not seem to be working for me)