Re: Another question on Cardinality??
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. However, I would most likely create a compound index that would include that field and others. Knowing which fields to includes under a single index requires knowing the data. Just index everything is probably and ok policy for many of the databases out there. But when you are adding/updating millions of records a day, performance is a concern. Indexing everything with have a noticeable slow down on inserts, updates and deletes because all the indexes also need to be updated. - Original Message - From: "Philip Mather" <[EMAIL PROTECTED]> To: "Brent Baisley" <[EMAIL PROTECTED]> Cc: "Ratheesh K J" <[EMAIL PROTECTED]>; Sent: Friday, August 04, 2006 1:31 PM Subject: Re: Another question on Cardinality?? 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 answer than a "depend on situation" kind of an answer. Frankly I'd ask "Why shouldn't I be indexing these fields?" not the other way around, you said to consider a table with only a thousand rows right? Unless I'm missing something an index would cost a trivial amount of disk space. If the real case is 10's of millions rows then compared to the data set it's still going to trivial anyway. Just index everything and throw some more hard drives at it. As someone else pointed out however there's plenty of comparative info all over the web let alone MySQL's own site, you could probably find some approximate numbers to play with out there. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another question on Cardinality??
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 answer than a "depend on situation" kind of an answer. Frankly I'd ask "Why shouldn't I be indexing these fields?" not the other way around, you said to consider a table with only a thousand rows right? Unless I'm missing something an index would cost a trivial amount of disk space. If the real case is 10's of millions rows then compared to the data set it's still going to trivial anyway. Just index everything and throw some more hard drives at it. As someone else pointed out however there's plenty of comparative info all over the web let alone MySQL's own site, you could probably find some approximate numbers to play with out there. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another question on Cardinality??
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: 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]
Re: Another question on Cardinality??
Have you considered reading up on basic database management? There are plenty of good material on the web for you to read where you can actually learn how to manage databases, so you don't have to ask others about every single detail. Ratheesh K J wrote: 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]
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
Cardinality
Hello all, Need an explanation for this: I did the following - SELECT DISTINCT COLUMN1 FROM TBL_XXX ; I got the foll result 1 2 3 4 5 7 8 10 11 12 13 14 16 17 18 19 20 21 23 24 25 26 27 28 29 30 -- 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? I also did an ANALYZE TABLE on TBL_XXX. The result is still the same. Thanks, Ratheesh Bhat K J
RE: mysql index cardinality
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 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 update it with analyze table for example.(http://dev.mysql.com/doc/mysql/en/show-index.html) I also tried with KEY (which is supposed to be an alias of index) and after the first insertion it updated the cardinality but not later on. 1/any explanation?is there a kind of random update from time to time? 2/ is that cardinality important to know? I read that big cardinality will ensure that the index is used for joins for example. Does MySQL check the "real" cardinality before querying?Or do I have to run an analyze table from time to time? 3/ a primary key is just a peculiar index, so why is that value updated? Thanks for any explanation _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql index cardinality
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 update it with analyze table for example.(http://dev.mysql.com/doc/mysql/en/show-index.html) I also tried with KEY (which is supposed to be an alias of index) and after the first insertion it updated the cardinality but not later on. 1/any explanation?is there a kind of random update from time to time? 2/ is that cardinality important to know? I read that big cardinality will ensure that the index is used for joins for example. Does MySQL check the "real" cardinality before querying?Or do I have to run an analyze table from time to time? 3/ a primary key is just a peculiar index, so why is that value updated? Thanks for any explanation _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index cardinality lost
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 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 we have not been able to track down when exactly it happens. The table type is MyISAM. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index cardinality lost
> 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 we have not been able to track down when exactly it happens. The table type is MyISAM. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index cardinality lost
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 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 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: sessionID Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *** 2. row *** Table: UserSessions Non_unique: 0 Key_name: PRIMARY Seq_in_index: 2 Column_name: userID Collation: A Cardinality: 164142 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Is this a bug? Additional information - these tables are replicated between two similar servers (Solaris 8, MySQL 4.0.18) and are populated exclusively using INSERT DELAYED SQL statements. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index cardinality lost
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 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: sessionID Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *** 2. row *** Table: UserSessions Non_unique: 0 Key_name: PRIMARY Seq_in_index: 2 Column_name: userID Collation: A Cardinality: 164142 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Is this a bug? Additional information - these tables are replicated between two similar servers (Solaris 8, MySQL 4.0.18) and are populated exclusively using INSERT DELAYED SQL statements. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cardinality Bug ?
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 POSTING ; +--+ | count(distinct date_created) | +--+ | 223284 | +--+ 1 row in set (2.48 sec) mysql> analyze table POSTING ; +---+-+--+--+ | Table | Op | Msg_type | Msg_text | +---+-+--+--+ | TRIBE.POSTING | analyze | status | OK | +---+-+--+--+ 1 row in set (16.74 sec) mysql> show index from POSTING ; +-+++--+ +---+-+--++--++- + | Table | Non_unique | Key_name | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-+++--+ +---+-+--++--++- + | POSTING | 0 | PRIMARY|1 | ID | A | 228383 | NULL | NULL | | BTREE | | | POSTING | 1 | posting_id_idx |1 | ID | A | 228383 | NULL | NULL | | BTREE | | | POSTING | 1 | posting_owner_id_idx |1 | OWNER_ID | A |6010 | NULL | NULL | | BTREE | | | POSTING | 1 | posting_parent_id_idx |1 | PARENT_ID | A | 114191 | NULL | NULL | YES | BTREE | | | POSTING | 1 | posting_thread_id_idx |1 | THREAD_ID | A | 45676 | NULL | NULL | YES | BTREE | | | POSTING | 1 | posting_from_person_id_idx |1 | FROM_PERSON_ID | A |9929 | NULL | NULL | | BTREE | | | POSTING | 1 | posting_date_created |1 | DATE_CREATED | A | 228383 | NULL | NULL | YES | BTREE | | | POSTING | 1 | posting_is_deleted_idx |1 | IS_DELETED | A | 1 | NULL | NULL | | BTREE | | +-+++--+ +---+-+--++--++- + 8 rows in set (0.00 sec) mysql> select count(distinct date_created) from POSTING ; +--+ | count(distinct date_created) | +--+ | 223284 | Thanks, Trevor
RE: cardinality in SHOW INDEX
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 PM -->To: [EMAIL PROTECTED] -->Subject: cardinality in SHOW INDEX --> -->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 -->innodb support OPTIMIZE TABLE? --> -->Thanks! --> -->Hsiu-Hui --> --> -->-- -->MySQL General Mailing List -->For list archives: http://lists.mysql.com/mysql -->To unsubscribe: -->http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
cardinality in SHOW INDEX
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 innodb support OPTIMIZE TABLE? Thanks! Hsiu-Hui -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why is it better to have a composite key with the largest cardinality 1st?
Do you mean that your table has a total of 1 rows? If yes, there is (generally) no need to add column b and/or c to the primary key, as this makes the information redundant (already A is unique). If you mean that you table has 4 rows (so 10K A x 2 B x 2 C) and only the combination of AxBxC makes the record unique, you have to use all 3 for the primary key. As for optimization, in theorie the order should NOT make any difference. If you first search 10K entries (A) and THEN search 2 entries (B) and THEN search 2 entries (C) gives you the same lookup hits than first lookup 2 entries (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 Vance Pattishall [mailto:[EMAIL PROTECTED] Sent: donderdag 10 juli 2003 3:05 To: [EMAIL PROTECTED] Subject: Why is it better to have a composite key with the largest cardinality 1st? Say I have column A with 1 distinct values column B and C with 2 distinct values and for simplicity sake the column type is an int. If I wanted a composite key why should I make the order of the key A,B,C I read someplace that faster lookups happen with the leftmost index, being an index 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why is it better to have a composite key with the largest cardinality 1st?
Say I have column A with 1 distinct values column B and C with 2 distinct values and for simplicity sake the column type is an int. If I wanted a composite key why should I make the order of the key A,B,C I read someplace that faster lookups happen with the leftmost index, being an index 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
Re: Cardinality for FULLTEXT-indexes MySQL 4.0.10
Hi! On Feb 24, Tobias Lind wrote: > 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 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 "show index from ", the last index (FULLTEXT) always shows > cardinality=NULL... > Is this normal? ...everything seems to work ok when I do selects using the > index... > > I ran "analyze table" and got the response "Table is already up to date" > I shut down mysqld, ran "myisamchk -a" and it went througt the first 8 > indexes quickly, but on the last one it did a "check record links", going > through all rows in the table. > After I started mysqld up again and ran "show index from ", I still > got cardinality=NULL for the last index. > Ran "myisamchk -a" again, and it behaved just like the first time - passed > the first 8 indexes quickly, and went through all rows on the last index. > > I'm running MySQL 4.0.10, rpm-binary version. > Red Hat Linux 8.0 > Dual P3400 MHz, 768 Mb RAM. > > On other tables (with less number on indexes), I have always got a > cardinality after running "analyze table" also on fulltext-indexes. > > Could this be a bug? Something to worry about? > > Regards, > Tobias Lind Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ - 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
Cardinality for FULLTEXT-indexes MySQL 4.0.10
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 "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 do selects using the index... I ran "analyze table" and got the response "Table is already up to date" I shut down mysqld, ran "myisamchk -a" and it went througt the first 8 indexes quickly, but on the last one it did a "check record links", going through all rows in the table. After I started mysqld up again and ran "show index from ", I still got cardinality=NULL for the last index. Ran "myisamchk -a" again, and it behaved just like the first time - passed the first 8 indexes quickly, and went through all rows on the last index. I'm running MySQL 4.0.10, rpm-binary version. Red Hat Linux 8.0 Dual P3400 MHz, 768 Mb RAM. On other tables (with less number on indexes), I have always got a cardinality after running "analyze table" also on fulltext-indexes. Could this be a bug? Something to worry about? Regards, Tobias Lind - 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
Cardinality for FULLTEXT-indexes MySQL 4.0.10
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 do selects using the index... I ran "analyze table" and got the response "Table is already up to date" I shut down mysqld, ran "myisamchk -a" and it went througt the first 8 indexes quickly, but on the last one it did a "check record links", going through all rows in the table. After I started mysqld up again and ran "show index from ", I still got cardinality=NULL for the last index. Ran "myisamchk -a" again, and it behaved just like the first time - passed the first 8 indexes quickly, and went through all rows on the last index. I'm running MySQL 4.0.10, rpm-binary version. Red Hat Linux 8.0 Dual P3400 MHz, 768 Mb RAM. On other tables (with less number on indexes), I have always got a cardinality after running "analyze table" also on fulltext-indexes. Could this be a bug? Something to worry about? Regards, Tobias Lind - 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
Re: Cardinality doesn't auto start
"Grigor, Peter" wrote: > > You need to run [analyze table] to create the index stats...do this > reasonable often (once a day for a well-used table). This script will be run on servers around the world by regular people, not sys admins. I will have no control over their databases. > Running [optimize table] on the table recreates index stats AND > reorganizes/defrags/coalesces data pages...do this once a week or so. The one index I have in this database is on a field which contains TO_DAYS(NOW()). Therefore the return is automatically grouped in the database as each day is written in order. Is there any value in optomizing in this situation? Apparently, this index is working pretty well, as the time to execute the queries has halted at about 12 seconds on the test site and the time doesn't seem to change after optimizing. The rows in this database will not be edited. Deletion should always occur only from the beginning as deletion is be month or year and I can't imagine anyone not deleting the oldest months first. Will this operation cause fragmentation? I'm having a bit of trouble between the order in which I see the data onscreen and how the actual file is written. > Look up the mysqlcheck utility--it lets you do this from scrips pretty easy. I see where I can write a script to run these functions, however, getting an end-user to run a script 'later' after the install, we all know is not an easy thing. I simply 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 to jump start the cardinality count. I'm just hoping for a cure to getting this to start by doing something in the install script where there table is created as well as many other config files. Perhaps if cardinality were set to 0 it would begin the count on its own? I can find very little information regarding cardinality and have no idea about how one would set it to 0 if that did work. > > Peter -- John Hinton - Goshen, VA. http://www.ew3d.com Those who dance are considered insane by those who can't hear the music - 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
RE: Cardinality doesn't auto start
You need to run [analyze table] to create the index stats...do this reasonable often (once a day for a well-used table). Running [optimize table] on the table recreates index stats AND reorganizes/defrags/coalesces data pages...do this once a week or so. Look up the mysqlcheck utility--it lets 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 Distrib 3.23.54, for pc-linux (i686) > > INDEXING PROBLEM > > I'm building an application which will have a self install script, > intended for use by many people. One particular area of the program > requires the need for an index. If I create this index when I > build the > table 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 is in the table, it > should be given a default cardinality of 0 and the count continue as > data is entered. > > This need to create/recreate the index after data is written, is tough > to deal with as a programmer having to ask the end user to create this > index after data is gathered. > > Any ideas besides creating an index creater script for the user to run > later? > > Would this be considered a bug? > > SQL to create table with index > > CREATE TABLE somename ( > ndx int(10) NOT NULL auto_increment, > d_now int(7) default NULL, > adate timestamp(14) NOT NULL, > PRIMARY KEY (ndx), > UNIQUE KEY id (ndx), > KEY d_now (d_now) > ) TYPE=MyISAM; > > end SQL --- > > I have also tried it with d_now set to NOT NULL with the same results. > The index just won't start counting with no data entered. > > -- > John Hinton - Goshen, VA. > http://www.ew3d.com > > - > 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 > - 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
Cardinality doesn't auto start
mysql Ver 11.18 Distrib 3.23.54, for pc-linux (i686) INDEXING PROBLEM I'm building an application which will have a self install script, intended for use by many people. One particular area of the program requires the need for an index. If I create this index when I build the table 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 is in the table, it should be given a default cardinality of 0 and the count continue as data is entered. This need to create/recreate the index after data is written, is tough to deal with as a programmer having to ask the end user to create this index after data is gathered. Any ideas besides creating an index creater script for the user to run later? Would this be considered a bug? SQL to create table with index CREATE TABLE somename ( ndx int(10) NOT NULL auto_increment, d_now int(7) default NULL, adate timestamp(14) NOT NULL, PRIMARY KEY (ndx), UNIQUE KEY id (ndx), KEY d_now (d_now) ) TYPE=MyISAM; end SQL --- I have also tried it with d_now set to NOT NULL with the same results. The index just won't start counting with no data entered. -- John Hinton - Goshen, VA. http://www.ew3d.com - 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
Cardinality and index using
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 (0.02 sec) mysql> show index from a; +---++--+--+-+---+-- ---+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++--+--+-+---+-- ---+--++-+ | a | 1 | a|1 | a | A | NULL | NULL | NULL | | +---++--+--+-+---+-- ---+--++-+ 1 row in set (0.00 sec) mysql> insert into a values ( 1 ); Query OK, 1 row affected (0.00 sec) mysql> show index from a; +---++--+--+-+---+-- ---+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++--+--+-+---+-- ---+--++-+ | a | 1 | a|1 | a | A | NULL -??? | NULL | NULL | | +---++--+--+-+---+-- ---+--++-+ 1 row in set (0.00 sec) mysql> optimize table a; +---+--+--+--+ | Table | Op | Msg_type | Msg_text | +---+--+--+--+ | tmp.a | optimize | status | OK | +---+--+--+--+ 1 row in set (0.04 sec) mysql> show index from a; +---++--+--+-+---+-- ---+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++--+--+-+---+-- ---+--++-+ | a | 1 | a|1 | a | A | 1 | NULL | NULL | | +---++--+--+-+---+-- ---+--++-+ 1 row in set (0.00 sec) 2) Why index using in different way when cardinality = 1 and cardinality > 1 ? Example: index in calculated_offlc ( contract_id ) have cardinality = 1 mysql> show index from contract; +--+++--+-+---+- +--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +--+++--+-+---+- +--++-+ | contract | 0 | PRIMARY|1 | id | A | 8520 | NULL | NULL | | | contract | 0 | deleted_id |1 | deleted_id | A | NULL | NULL | NULL | | | contract | 0 | deleted_id |2 | number | A | 8520 | NULL | NULL | | +--+++--+-+---+- +--++-+ 3 rows in set (0.00 sec) mysql> show index from calculated_offlc; +--++-+--+-- -+---+-+--++-+ | Table| Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +--++-+--+-- -+---+-+--++-+ | calculated_offlc | 0 | PRIMARY |1 | processed_data_id | A | 46706 | NULL | NULL | | | calculated_offlc | 1 | contract_id |1 | contract_id | A | 1 - !!! | NULL | NULL | | +--++-+--+-- -+---+-+--++-+ 2 rows in set (0.00 sec) mysql> explain SELECT c.id, IFNULL( SUM( c_offlc.value ), 0 ) FROM contract AS c LEFT JOIN calculated_offlc AS c_offlc ON c_offlc.contract_id = c.id GROUP BY c.id; +-+---+---+-+-+--+---+-- + | table | type | possible_keys | key
Cardinality and index using
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 (0.02 sec) mysql> show index from a; +---++--+--+-+---+-- ---+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++--+--+-+---+-- ---+--++-+ | a | 1 | a|1 | a | A | NULL | NULL | NULL | | +---++--+--+-+---+-- ---+--++-+ 1 row in set (0.00 sec) mysql> insert into a values ( 1 ); Query OK, 1 row affected (0.00 sec) mysql> show index from a; +---++--+--+-+---+-- ---+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++--+--+-+---+-- ---+--++-+ | a | 1 | a|1 | a | A | NULL -??? | NULL | NULL | | +---++--+--+-+---+-- ---+--++-+ 1 row in set (0.00 sec) mysql> optimize table a; +---+--+--+--+ | Table | Op | Msg_type | Msg_text | +---+--+--+--+ | tmp.a | optimize | status | OK | +---+--+--+--+ 1 row in set (0.04 sec) mysql> show index from a; +---++--+--+-+---+-- ---+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++--+--+-+---+-- ---+--++-+ | a | 1 | a|1 | a | A | 1 | NULL | NULL | | +---++--+--+-+---+-- ---+--++-+ 1 row in set (0.00 sec) 2) Why index using in different way when cardinality = 1 and cardinality > 1 ? Example: index in calculated_offlc ( contract_id ) have cardinality = 1 mysql> show index from contract; +--+++--+-+---+- +--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +--+++--+-+---+- +--++-+ | contract | 0 | PRIMARY|1 | id | A | 8520 | NULL | NULL | | | contract | 0 | deleted_id |1 | deleted_id | A | NULL | NULL | NULL | | | contract | 0 | deleted_id |2 | number | A | 8520 | NULL | NULL | | +--+++--+-+---+- +--++-+ 3 rows in set (0.00 sec) mysql> show index from calculated_offlc; +--++-+--+-- -+---+-+--++-+ | Table| Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +--++-+--+-- -+---+-+--++-+ | calculated_offlc | 0 | PRIMARY |1 | processed_data_id | A | 46706 | NULL | NULL | | | calculated_offlc | 1 | contract_id |1 | contract_id | A | 1 - !!! | NULL | NULL | | +--++-+--+-- -+---+-+--++-+ 2 rows in set (0.00 sec) mysql> explain SELECT c.id, IFNULL( SUM( c_offlc.value ), 0 ) FROM contract AS c LEFT JOIN calculated_offlc AS c_offlc ON c_offlc.contract_id = c.id GROUP BY c.id; +-+---+---+-+-+--+---+-- + | table | type | possible_keys | key
