List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:August 4 2006 3:47pm
Subject:Re: Another question on Cardinality??
View as plain text  
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

Thread
Another question on Cardinality??Ratheesh K J4 Aug
  • Re: Another question on Cardinality??Martin Jespersen4 Aug
  • Re: Another question on Cardinality??Brent Baisley4 Aug
    • Re: Another question on Cardinality??Philip Mather4 Aug
  • Re: Another question on Cardinality??Brent Baisley4 Aug