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" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
