Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 7

Displaying posts with tag: null (reset)

Bulk insert into tables in sorted order to avoid deadlocks
+2 Vote Up -0Vote Down

Shard-Query inserts data into a “coordinator” table when answering queries.   When there is a GROUP BY on the original query, the coordinator table contains a UNIQUE KEY over the GROUP BY attributes.   Shard-Query uses INSERT .. ON DUPLICATE KEY UPDATE in combination with bulk insert (insert into … values (),(),() ) when inserting into the table.

For what would normally be efficiency sake, Shard-Query sends queries to the shards using ORDER BY NULL which disables the filesort operation. Of course, this often results in the rows being sent back from the shards in random order.

Because the results are in random order, the bulk insertion that the worker does into the coordinator table can deadlock with other worker threads when using InnoDB or TokuDB as the coordinator table. Right now I’ve just been using MyISAM for the

  [Read more...]
Two subtle bugs in OUTER JOIN queries
+7 Vote Up -0Vote Down

OUTER JOIN queries in SQL are susceptible to two very subtle bugs that I’ve observed a number of times in the real world. Daniel and I have been hammering out ways to automatically detect queries that suffer from these bugs, in a relatively new Maatkit tool called mk-query-advisor. It’s part of our series of advisor tools for MySQL. I wrote a blog post about it a while ago. Automated analysis of bad query patterns is a good thing to write tools to do, because catching buggy queries is hard work if you do it manually.

Let’s dive right in and analyze these subtle bugs. Warning: if you don’t understand how SQL handles NULL, you’re not going to understand the following. Many

  [Read more...]
The difference between a unique index and primary key in MySQL
+1 Vote Up -0Vote Down

There’s a really important difference between a unique index (MySQL’s answer to a “unique constraint”) and a primary key in MySQL. Please take a look at this:

CREATE TABLE `t` (
  `a` int,
  `b` int,
  `c` int,
  UNIQUE KEY `a` (`a`,`b`)
)

The combination of columns a, b should uniquely identify any tuple in the table, right?

select * from t;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 | 
| NULL | NULL |    1 | 
| NULL | NULL |    1 | 
| NULL | NULL |    1 | 
+------+------+------+

Wrong. Our arch-enemy NULL messes things up again:

A UNIQUE index creates a constraint such that all

  [Read more...]
MySQL – NULL vs ” vs ‘NULL’
+0 Vote Up -0Vote Down

Today, in one of my project I’ve to check empty fields.

The field name is: answer it’s type is TEXT and Default value is NULL

In my SQL query I was checked at first answer != NULL (if the answer is not null, i.e if the answer is not empty), But this was not showing the correct result.

Then I changed it to answer != ” ( i.e ” means empty string) then it showed the correct result.
Then I test with this answer != ‘NULL’, and it also showed the correct result.


Calculating the Nth percentile in MySQL
+0 Vote Up -0Vote Down
Yesterday, I was on the freenode ##pentaho irc channel when Andres Chaves asked me how to calculate the Nth percentile in MySQL. He saw a solution somewhere using subqueries, but wasn't too happy about it.

A while ago I wrote about calulating the median in MySQL, and it turns out the Nth percentile can be calculated using a similar, single-pass approach, not relying on subqueries, UDFs, or user-defined variables.

The percentile....


So, what is a percentile exactly? Here's what the wikipedia says:

A percentile is the value of a variable below which a certain percent of





  [Read more...]
How to avoid an extra index scan in MySQL
+0 Vote Up -0Vote Down

Is your MySQL server doing an extra index scan on queries that need to check a key for matches or NULL? It's easy for this to happen accidentally, but it's also easy to fix, especially in MySQL 5.0 and up. Here's how.

What's New in BlueDragon 7?
+0 Vote Up -0Vote Down

New Atlanta, just released Beta 1 of version 7 of thier BlueDragon CFML application server.

Below is a list of the new features - I think the IsNull function and null keyword will prove to be quite handy.

  • Multi-threaded programming (CFTHREAD, and related tags and functions)
  • Interfaces and Abstract CFCs
  • null keyword and IsNull() function
  • CFQUERY Enhancements
    • CACHEDUNTILCHANGE attribute
    • BACKGROUND attribute
  • Application.cfc
    • onClientStart() handler
    • onMissingTemplate() handler
  • CFDOCUMENT
  • CFCHART
  • CFSEARCH Enhancements
    • support for Word and PDF documents
    • support for multiple languages
  • UDF Forward References in
  [Read more...]
Showing entries 1 to 7

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.