From: Johan De Meersman
Date: August 31 2011 11:24am
Subject: Re: utility of an index
List-Archive: http://lists.mysql.com/mysql/225621
Message-Id: <8abdccc2-85f9-41c2-942b-ef1e3841901e@zimbra>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
----- Original Message -----
> From: "Dave Dyer"
>
> It depends on if you want the forest or the trees. A frequently
> executed query asks for just the robots. An index is an efficient way to
> select 6 of 20,000. As for why the rest are null, null is just a a
> random everything else value, left over from before the "is_robot" column
> was added.
>
> Is there a better way to mark a small number of "special" records in
> a big data set?
No, you're doing it the right way, and the index is used exactly for what it's intended.
The NULL is out of place, though - is_robot sounds (and probably is) binary; use it as such and set all the NULLs to 0 instead. NULL means "I really, honestly don't know, there simply is no value for this field". In this case, there is a value: "no, it's not a robot".
You may never run into problems with it, or you could slam headfirst into weirdness at some point - NULL has some very peculiar properties that are best avoided if you don't need them.
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel