It depends on the data, not the situation. How's that?
FLD_4 is doubtful that you would want/need and index on it. This assumes an even
distribution of both values (ie. male/female).
Since you would be scanning half the table anyway, an index won't really help. Now if it's
not an even distribution, like people
under/over 70, and you will always be searching on people above 70, then an index may help
since it will narrow the records down
considerably.
You really have to match cardinality with distribution of values. An index should allow
you to quickly narrow the set of records
that need to be analyzed. Cutting out half the records isn't going to help much, it's
quicker just to read through the entire file
sequentially than jump around to 50% of the records individually.
----- Original Message -----
From: "Ratheesh K J" <ratheesh.kj@stripped>
To: <mysql@stripped>
Sent: Friday, August 04, 2006 6:15 AM
Subject: Another question on Cardinality??
Hello all,
Another question on cardinality.
Consider a table with 1000 rows and columnns. Details of the columns are as below:
FLD_1 - int - cardinality 1000 - PRIMARY KEY
FLD_2 - tinyint- cardinality 400
FLD_3 - varchar - cardinality 10
FLD_4 - varchar - cardinality 2
FLD_5 - varchar - cardinality 5
Assuming that cardinality exactly is the number of distinct values for that column, Which
are the fields that is best for indexing
for the table.
Is it meaningful for me to index FLD_3, FLD_4 and FLD_5 knowing that their cardinality is
always going to be the same?
There are certain scenarios wherein I have queries on the tables as below:
1) Select * from table where FLD_4 = 1;
2) Select * from table where FLD_5 = 3;
3) Select * from table where FLD_3 >1 AND FLD_5 < 6;
considering all the above cases, what should I conclude? should I have indexes on these
three fields?
Looking for a specific answer than a "depend on situation" kind of an answer.
Thanks
Ratheesh Bhat K J
