Neulinger,
Friday, November 15, 2002, 7:25:27 PM, you wrote:
NN> Assume I have a mysql table (myisam most likely) with a few hundred
NN> thousand rows in it. One of the columns indicates success or failure.
NN> 99.9% of the rows will have "0" in that column. But a small number will
NN> have 1. I need to be able to fetch those rows quickly, without slowing
NN> everything else down, but ideally without doing a full table scan.
NN> I can create an index on that column, but I am under the impression that
NN> this a really bad/slow type of index to create/maintain, since one of
NN> the values will cover most of the table.
NN> I'd like to be able to say something like:
NN> create index failures on dumps(status) where status!=0;
NN> If the sql query being run isn't compatible with the restriction on the
NN> index, then it cannot be used. For example, if I query for status=2, it
NN> would be ok, but status=0 would not be able to use the index. Simpler
NN> may be to only allow the index to be used if the query contains exactly
NN> the same restriction. i.e. the "where status !=0" index could only be
NN> used if I had "status != 0" in my select query.
NN> Or alternatively, if you can suggest some other means for accomplishing
NN> this efficiently...
NN> (Yes, I know I can make a temporary or results table updated
NN> periodically, which I will likely do in the meantime, but would be nice
NN> to have an efficient way of accomplishing this with live data.)
If I've got you right status can have values 0 or 1. In this case you
can just use " SELECT ... WHERE status=1 .." (index wil be used) or "SELECT .. WHERE
status=0 .." (index will not be used, because scan the whole table
will be faster to retrieve 99,9% of rows) depends on what you want to get.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ Egor.Egorov@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
