edit: I filed MySQL bug#46867 about this issue.

Ask anyone who has learned to count the following two questions. The answer to both of which should be yes.

Q:Is 5 between 1 and 10? A: Yes

Q:Is 5 between 10 and 1? A: Yes

Ask MySQL those same questions:

mysql>  (select 'Yes' 
           from dual 
          where 5 between 1 and 10
        ) 
        union 
        (select 'No'
        ) 
        limit 1;
+-----+
| Yes |
+-----+
| Yes |
+-----+
1 row in set (0.00 sec)

mysql>  (select 'Yes' 
          from dual 
         where 5 between 10 and 1 
        ) 
        union 
        (select 'No') 
        limit 1;
+-----+
| Yes |
+-----+
| No  |
+-----+
1 row in set (0.00 sec)

This is a problem because applications may produce BETWEEN clauses. I don't think most …

Beginning Database Design: From Novice to Professional

Beginning Database Design: From Novice to Professional. By Clare Churcher, Apress, 2007. Page count: 230 pages. (Here’s a link to the publisher’s site.)

My wife bought a copy of this book, and recently I took it off her bookshelf to give it a read myself.

I found the book very lucid and readable. The author does not drag us through a bunch of formalisms, nor does she attempt to force the book to be readable through the use of comics, pop-culture references, or other artificial devices. Instead, she draws on her real-life experience helping people design databases, and presents several examples that …

In the context of database programming, QEP is an acronym for Query Execution Plan. The database server analyzes every SQL query and plans how to use indexes and order tables to produce the result in the most efficient way.You can get a report of the QEP for a SELECT query using the EXPLAIN command in MySQL. This is an important tool to analyze your SQL queries and detect

I'm pleased to announce the release of Spider storage engine version 2.0(beta).
http://spiderformysql.com/
From this time, version 1.x is stable releases, version 2.x is development releases for new features.

The main changes in this version are following.
- Add table parameter "semi_table_lock_connection".
- Add server parameter "spider_semi_table_lock_connection".
  Spider has "spider_semi_trx", "spider_semi_trx_isolation", (for transactional tables like InnoDB) "spider_semi_table_lock" and "semi_table_lock" (for non transactional tables like MyISAM) options for consistent reading at remote servers during executing 1 SQL at local server. But you must use different connections for transactional tables and non transactional tables because "semi_table_lock" causes implicit transaction commit.
…

Before you think I’ve posted an animal story in my MySQL category please read on. For reference, The RAT and the CAT is something I wrote back in 2006, that explains the CAT part.

I was reviewing the website performance tonight of a client. I had access to an idle system so being lazy in collecting SQL after my initial audit of the system and MySQL, I turned on the General Query log and hit the home page. NOTE: Do not do this on a running production system, especially with any volume.

What I got was 77 lines of output (after I removed newlines in the queries). 74 were queries, and 72 were from the same table, via some unnecessarily complex sub-joins. I wish I could have laughed but it wasn’t really funny.

My Friday wisdom for writing SQL is this:

• Don’t write duplicate SQL statements
• Look the entire …

Hi!

Just a few hours ago, I arrived home after a very quiet and peaceful two-week holiday with my family. It was great! I didn't bring a computer on purpose. I brought a mobile phone, but didn't answer that on purpose too :) Result: absolute relaxation, with lots of time to hike, cycle, and read, and occasional visits to musea and historic sites. Bliss :)

Anyway, now that the bags are unpacked, and the kids are asleep, it's time to face the dragon better known as my inbox. What I found brought a big smile to my face:



Yes - it's true!! Copies of my and Jos' book Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL have hit the …

OK, I know that this is not really Web2.0, Business, Marketing or MySQL related, but I found a very inspiring story about an executive finding a job of his own dreaming.
I'm a lucky guy who has an interesting and fulfilling job, but paving the road for the career of your dreams is something that everyone should get used to, especially in this turbulent times.
His approach was a good one for tough times, so I wanted to pass it along to you. The part of the story that I like is the attitude of the hunter while trying to find a job:
"I visualized what I wanted this position to be like and wrote a list of bullet items about the job and my ideal company."I completely agree. Each of us, especially in this flat and continuously evolving world, can work to shape a position where he fits perfectly. If you mix your technical skills, leadership …

Our DBA team wants replication state on a MySQL slave to be more robust against machine crashes. There are some things I can do, but now is a good time to review the work in progress by the community that will eventually make replication much easier to support in production.

Replication in MySQL has many possible futures. I use MySQL out of convenience to mean official MySQL, MariaDB, Drizzle, XtraDB, the Facebook patch and the Google patch.

Drizzle has by far (leaps and bounds far) the cleanest interface for …

For the first time ever I took a two-week vacation. How can a person who has 100s of database servers, 100s of web servers, and a system that supports 100K tps across 20 TB of data is able to take a vacation? Easy, I have a great team that is very competent in managing the platform by following our cookbook routines and guidelines for new application interaction with the databases.

Where did I go? I went to NYC-stayed in Tribeca and only ate Pizza, Hotdogs, White Castle and Hala Food from vendors that are nearly on every street corner. I also lost 6 pounds! After three months of half-ass dieting to prepare for my Pizza diet, who would have known that just going for it was the key to success! How did I do it? Well, first I walked to Little Italy, bought 2-4 slices, ate them, and then walked around the city. That is it. As a Database Dude, I do not do much walking except for when I am too tired to run on the treadmill for 20 mins. I was …