List:General Discussion« Previous MessageNext Message »
From:Nathan Neulinger Date:November 15 2002 5:25pm
Subject:feature suggestion - indexes with "where" clause or similar
View as plain text  
Assume I have a mysql table (myisam most likely) with a few hundred
thousand rows in it. One of the columns indicates success or failure.
99.9% of the rows will have "0" in that column. But a small number will
have 1. I need to be able to fetch those rows quickly, without slowing
everything else down, but ideally without doing a full table scan. 

I can create an index on that column, but I am under the impression that
this a really bad/slow type of index to create/maintain, since one of
the values will cover most of the table.

I'd like to be able to say something like:

	create index failures on dumps(status) where status!=0;

If the sql query being run isn't compatible with the restriction on the
index, then it cannot be used. For example, if I query for status=2, it
would be ok, but status=0 would not be able to use the index. Simpler
may be to only allow the index to be used if the query contains exactly
the same restriction. i.e. the "where status !=0" index could only be
used if I had "status != 0" in my select query. 

Or alternatively, if you can suggest some other means for accomplishing
this efficiently... 

(Yes, I know I can make a temporary or results table updated
periodically, which I will likely do in the meantime, but would be nice
to have an efficient way of accomplishing this with live data.)

-- Nathan

------------------------------------------------------------
Nathan Neulinger                       EMail:  nneul@stripped
University of Missouri - Rolla         Phone: (573) 341-4841
Computing Services                       Fax: (573) 341-4216
Thread
feature suggestion - indexes with "where" clause or similarNathan Neulinger15 Nov
  • re: feature suggestion - indexes with "where" clause or similarEgor Egorov18 Nov
    • Re: feature suggestion - indexes with "where" clause or similarJeremy Zawodny18 Nov
Re: feature suggestion - indexes with "where" clause or similarDaniel Koch18 Nov
  • RE: feature suggestion - indexes with "where" clause or similarDean Harding18 Nov
    • Re: feature suggestion - indexes with "where" clause or similarJeremy Zawodny18 Nov
RE: feature suggestion - indexes with "where" clause or similarNathan Neulinger18 Nov
  • Re: feature suggestion - indexes with "where" clause or similarMichael T. Babcock18 Nov
    • Re: feature suggestion - indexes with "where" clause or similarDaniel Koch19 Nov
      • Re: feature suggestion - indexes with "where" clause or similarBenjamin Pflugmann19 Nov
        • Re: feature suggestion - indexes with "where" clause or similarDan Nelson19 Nov