Hi,
On 28-8-2011 4:08, shawn wilson wrote:
On Sat, Aug 27, 2011 at 17:33, Arthur Fuller<fuller.art...@gmail.com> wrote:
I agree 110%. It is completely pointless to index a column with that amount
of NULLs. In practical fact I would go further: what is the point of a
NULLable column?
A NULL 'value' is special in most operations. It indicates that the
value is undefined, unknown, uncertain. In this regard it's actually not
a value.
SELECT 'Uncertain' = TRUE;
Result: 0
SELECT 'Uncertain' = FALSE;
Result: 1
SELECT 'Uncertain' = NULL;
Result: NULL
SELECT NULL = TRUE;
Result: NULL
SELECT NULL = FALSE;
Result: NULL
SELECT NULL = NULL;
Result: NULL
(Unfortunately someone decided to add the <=> operator:
SELECT NULL <=> NULL;
Result: 1
Even stranger is that it is documented as "NULL safe" !?!?)
The advantage to me for having NULL 'values' is that it is usually
handled as a truly undefined value. (When you compare an undefined value
with for example 2, the result cannot be TRUE or FALSE. The undefined
value might be equal to 2, or might not be equal to 2. The result can
only be undefined.)
To deal with NULL results inside expressions COALESCE() is a very useful
function.
how does null effect an index? i had always assumed that, since there
is nothing there, that record wouldn't go into the index hence
wouldn't be processed when utilizing the index.
MySQL can use NULL in indexes when executing a query. If there are not
enough different values in a column (low cardinality) it might be faster
to do a full table search instead of first reading the index and then
having to go through the table anyway.
--
Kind regards / met vriendelijke groet,
Jigal van Hemert.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
