I didn't say consider a table with a thousand rows, must have been someone elses response. But anyway, it comes down to knowing your
data. If you know your data, then you can create the best set of indexes.
I would almost never create an index on a field with a cardinality of 2. Howev
Brent,
Given that...
You really have to match cardinality with distribution of values.
...sounds like hard work (well you actually have to think about it) and...
considering all the above cases, what should I conclude? should I have
indexes on these three fields?
Looking for a specific
7;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 o
on 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
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
--
Totally 26 rows
Now when I saw the Cardinality of this col ( COLUMN1 ) by doing a SHOW INDEX on
TBL_XXX, It shows 93.
How can this be possible, as Cardinality should be the number of distinct
values ( 26 in this case ) for that column right
Nobody to explain me that?
From: "mel list_php" <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Subject: mysql index cardinality
Date: Fri, 25 Feb 2005 16:47:12 +
Hi,
A strange thing with index, I thought the cardinality was automatically
updated (like for a primary key for e
Hi,
A strange thing with index, I thought the cardinality was automatically
updated (like for a primary key for exemple).
When I use a primary key in a table, insert a row, the cardinality is
increased of 1 as well.
I just tried to do that with an INDEX, and the cardinality is none unless I
As a preventive maintenance and the handle fragmentation running a nightly
mysqltablecheck may prove to be beneficial.
-Original Message-
From: Jude Insley
To: [EMAIL PROTECTED]
Sent: 5/21/04 7:10 AM
Subject: RE: Index cardinality lost
> After what action are you indexes becom
> After what action are you indexes becoming invalid? Are you running nightly
> maintenance? What is the table type?
We are not running nightly maintenance but we do run the INSERT DELAYED as
a batch during the night. The loss of cardinality seems to happen after a
number of days but w
After what action are you indexes becoming invalid? Are you running nightly
maintenance? What is the table type?
-Original Message-
From: Jude Insley
To: [EMAIL PROTECTED]
Sent: 5/21/04 6:00 AM
Subject: Index cardinality lost
We have a set of tables which are losing the cardinality on
We have a set of tables which are losing the cardinality on the first part
of the primary key. A simple CHECK TABLE or ANALYZE table restores the
cardinality. We are running MySQL 4.0.18 on Solaris 8.
*** 1. row ***
Table: UserSessions
Hello,
I was able to duplicate the following sequence in both
4.0.14-max and 4.0.16. It happens in all my tables. I do not understand
why the cardinality for the date_created field does not = 223284.
mysql> select count(distinct date_created) from POST
Use analyze table to calculate cardinality.
- Dathan Vance Pattishall
- Sr. Programmer and mySQL DBA for FriendFinder Inc.
- http://friendfinder.com/go/p40688
-->-Original Message-
-->From: Hsiu-Hui Tseng [mailto:[EMAIL PROTECTED]
-->Sent: Thursday, November 06, 2003 4:46
Hi,
Some time I saw cardinality in SHOW INDEX is null. Why this happening? Then,
I did a OPTIMIZE TABLE on the table and the number was getting back.
We are going to switch all of our table to innodb. Will innodb has this
problem? If it happend, how to fix it in innodb. Using OPTIMIZE TABLE? Is
(C) THEN lookup 2 entries (B) and THEN
lookup 10K entries (A). But however exactly this is implemented is
beyond my knowledge.
As for general indexing and tuning, it is always better to use keys with
high cardinality than keys with low one.
Cheers
/rudy
-Original Message-
From: Dathan
with a large cardinality. Is this true? Is this a hook
for the optimizer? What is the internal logic for mySQL that makes this
so? Is this a myth and have I been misled?
--
Dathan
t;
> Regards,
> Tobias Lind
It's ok, as "cardinality" is meaningless for FULLTEXT indexes.
> Hi!
> I have a question regarding "Cardinality" for FULLTEXT-indexes.
>
> I have a table with 9 indexes - the last 3 indexes are FULLTEXT.
> When I run &qu
Hello!
I never got any comment on this one...
Have noone else observed this behavior?
I'm a bit worried to use this in my production site without knowing if it's
and indication of some problem with FULLTEXT indexes... :)
Regards,
Tobias Lind
Hi!
I have a question regarding "C
Hi!
I have a question regarding "Cardinality" for FULLTEXT-indexes.
I have a table with 9 indexes - the last 3 indexes are FULLTEXT.
When I run "show index from ", the last index (FULLTEXT) always shows
cardinality=NULL...
Is this normal? ...everything seems to work ok when I
mply find it frustating that MySQL does
not start its cardinality count automatically when an index is created
at the time the table it created. It has been counting perfectly when I
create the index after data is in the table. There also seems to be a
'isamchk -a' function which is supposed
you do this from scrips pretty easy.
Peter
<^_^>
> -Original Message-
> From: John Hinton [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 29, 2003 7:36 AM
> To: [EMAIL PROTECTED]
> Subject: Cardinality doesn't auto start
>
>
> mysql Ver 11.18
le or apparently even after the table is built but before any data is
written to the table, cardinality is shown as 'None' (using phpMyAdmin)
and futhermore it does not start counting until after data is entered
and the index is recreated.
Seems like when the index is created, if no data
Hi, all
I am using mysql - 3.23.46, Intel, Solaris 8
Some questions:
1) Why cardinality value don't automaticly change when query change set of
unique values in index ( insert or update ) ?
Example:
mysql> create table a ( a int not null, index ( a ) );
Query OK, 0 rows affected (
Hi, all
I am using mysql - 3.23.46, Intel, Solaris 8
Some questions:
1) Why cardinality value don't automaticly change when query change set of
unique values in index ( insert or update ) ?
Example:
mysql> create table a ( a int not null, index ( a ) );
Query OK, 0 rows affected (
25 matches
Mail list logo
