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: [EMAIL PROTECTED]
University of Missouri - Rolla Phone: (573) 341-4841
Computing Services Fax: (573) 341-4216
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
