Re: search_where and other searches

[prev] [thread] [next] [Date index for 2004/07/06]

At 07:37 2004-07-06, Jesse Sheidlower wrote:
>In some of my tables, I don't delete rows by actually deleting
>them, but rather by setting a "deleted" column to a non-null
>value, and all searches on this table then have the added
>"WHERE deleted IS NULL" clause. I achieved this (Tony had

This is not an answer to your question, but I'd like to pass on a lesson I 
learned a few weeks ago. I don't know what database you use or anything, so 
this may or may not apply.

Some databases (Oracle and PostgreSQL that I know of, I haven't found any 
other by Googling) don't index NULL values, which means your where clause 
will not use any index you created on the "deleted" column. Since this is 
probably used in most queries on that table... hello table scan.

(In Oracle you can use a bitmap index instead. It is good for indices with 
low cardinality (few different values), but slow in OLTP situtations with 
many inserts/updates).


In this case, an "is_deleted" column with a boolean value would work just 
as fine. In other cases e.g. where a row is active when the "end_time" 
column is null it may be necessary to add an indexed status column for the 
single purpose of fast access.


Like I said, I have no idea what database you use, so hopefully this wasn't 
at all interesting to you :)


/J

-------- ------ ---- --- -- --  --  -     -     -        -          -
Johan Lindström    Sourcerer @ Boss Casinos   johanl AT DarSerMan.com

Latest bookmark: "mod_perl- Getting Your Feet Wet with mod_perl"
http://perl.apache.org/docs/2.0/user/intro/start_fast.html
dmoz (1 of 7): /Computers/Software/Internet/Servers/ 181