| Bug #43660 | SHOW INDEXES/ANALYZE does NOT update cardinality for indexes of InnoDB table | ||
|---|---|---|---|
| Submitted: | 15 Mar 2009 17:49 | Modified: | 18 Jun 2010 22:57 |
| Reporter: | Valeriy Kravchuk | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
| Version: | 4.1.26, 5.0.60, 5.1.33-bzr | OS: | Any |
| Assigned to: | Vasil Dimov | CPU Architecture: | Any |
[15 Mar 2009 22:39]
Peter Laursen
Was it checked if Windows 64 bit server is affected?
[16 Mar 2009 5:45]
Valeriy Kravchuk
Windows x64 is also affected:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.67-community-nt-log MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show indexes from sources\G
*************************** 1. row ***************************
Table: sources
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: source_id
Collation: A
Cardinality: 73
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: sources
Non_unique: 0
Key_name: uniq_short_name
Seq_in_index: 1
Column_name: short_name
Collation: A
Cardinality: 73
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: sources
Non_unique: 1
Key_name: country_id
Seq_in_index: 1
Column_name: country_id
Collation: A
Cardinality: 73
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
3 rows in set (0.03 sec)
mysql> show indexes from sources\G
*************************** 1. row ***************************
Table: sources
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: source_id
Collation: A
Cardinality: 73
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: sources
Non_unique: 0
Key_name: uniq_short_name
Seq_in_index: 1
Column_name: short_name
Collation: A
Cardinality: 73
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: sources
Non_unique: 1
Key_name: country_id
Seq_in_index: 1
Column_name: country_id
Collation: A
Cardinality: 73
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
3 rows in set (0.00 sec)
mysql> analyze table sources;
+--------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| test.sources | analyze | status | OK |
+--------------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> show indexes from sources\G
*************************** 1. row ***************************
Table: sources
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: source_id
Collation: A
Cardinality: 73
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: sources
Non_unique: 0
Key_name: uniq_short_name
Seq_in_index: 1
Column_name: short_name
Collation: A
Cardinality: 73
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: sources
Non_unique: 1
Key_name: country_id
Seq_in_index: 1
Column_name: country_id
Collation: A
Cardinality: 73
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
3 rows in set (0.00 sec)
[16 Mar 2009 15:34]
Mikhail Izioumtchenko
Vasil, could you have a look at it?
[17 Mar 2009 8:58]
MySQL Verification Team
In page0cur.c, page_cur_open_on_rnd_user_rec() function, there is a page_rnd variable that doesn't rollover in 64-bit. I changed the function by adding a line, and it works again: page_rnd += 87584577; page_rnd &= 0xffffffff; rnd = page_rnd % page_get_n_recs(page);
[17 Mar 2009 15:40]
Vasil Dimov
Valeriy, you write: "So, one can execute these commands several times to get proper cardinality estimation and good execution plans for the queries.", this is not true. Each calculation of the stats is independent from the previous stats. Repetitive recalculating of the stats does not approximate/approach the real value. Furthermore stats are calculated on different events (open table, or when the table is changed "too much" for example) that are not controlled by the user. This means that even if one runs ANALYZE until they get the "proper" stats those stats may be recalculated automatically in a few minutes, beyond the control of the user. The stats calculation is done by picking up 8 "random" pages, scanning them and drawing a conclusion, assuming that the rest of the pages are like the 8 that were sampled.
[18 Mar 2009 10:54]
Vasil Dimov
What follows is an explanation why behavior is different on 32 bit and 64 bit machines. 1. InnoDB uses unsigned long numbers for the calculations which are 32 bit on 32 bit machines and 64 bit on 64 bit machines (the variables are page_rnd and rnd, see page_cur_open_on_rnd_user_rec()). 2. A random number is generated using the pattern 976722341 + 87584577*n where n is increased with 1 on every invocation, starting with n=1. 3. In the btree, a random leaf page is chosen to be sampled for calculating the stats using the algorithm (aka the random dives): 3.1 start from the root page 3.2 pick up a random record from the current page using a random number from 2. and calculating the remainder from the division of this random number by the number of records on the page 3.3 if we are at the bottom, return the current page as the random page, end. 3.4 descend to the child page 3.5 go to 3.2 4. In this particular case (from sources_dump.sql) the btree has height 2 and the root page has 3 records. So only one descend is made and one of 3 pages is picked. 5. (A + B*n) % 3 is constant for any n if B % 3 = 0. In our case A = 976722341, B = 87584577 and indeed B is multiple of 3. What the above accumulates to is that no matter how many times the statistics for this index are calculated, the result will always be the same because the same page will always be picked. I.e.: (976722341 + 87584577*1) % 3 = 2 (976722341 + 87584577*2) % 3 = 2 (976722341 + 87584577*3) % 3 = 2 (976722341 + 87584577*4) % 3 = 2 ... (in the root page, always the 2nd record will be chosen and a descend will be made to the relevant leaf page, remember height=2, then that leaf page will be sampled) This explains why the stats "never" change on 64 bit machines. What happens on 32 bit machines is that (976722341 + 87584577*n) wraps around 2^32 when n becomes 38 and then the remainder is no longer 2. I.e.: ((976722341 + 87584577*36) % 2^32) % 3 = 2 ((976722341 + 87584577*37) % 2^32) % 3 = 2 ((976722341 + 87584577*38) % 2^32) % 3 = 1 ((976722341 + 87584577*39) % 2^32) % 3 = 1 Actually it will also wrap on 64 bit machines (and will cause different stats to be returned) but for much higher values of n.
[18 Mar 2009 10:59]
Vasil Dimov
We can change the algorithm that picks up a "random" page to pick a "more random" page, BUT this will cause existing query plans to change (for good or for bad). People have tuned their applications to work with the current implementation. This is one of the things that, if changed will fix someone's problem but may introduce problems for someone else. The safest way is to introduce a config knob that could switch between old and new behavior.
[19 Mar 2009 22:48]
John David Duncan
Vasil -- in what way could people have tuned their applications to work with the current implementation? The only workaround for a bad query plan is to force the optimizer with hints. If you are using hints, a change to this won't have any affect on you; and if you aren't using hints, then you haven't tuned your application.
[20 Mar 2009 6:10]
Vasil Dimov
John, A user may have tuned the behavior by playing with the table (index) size as far as this is possible. E.g. "keep this table small, otherwise that query is very slow" or even "insert some bulk records in this table to speed that query". Sounds strange but in the case from this bug report if the root page contained 4 instead of 3 records then the black magic described wouldn't have happened. Anyway, even if we assume that there are zero people who have "tuned" their databases in such a way, there may be cases where it just works now and stops working after a possible change to the RNG (works = good query plan). Imagine your favorite (and fast) query becoming hell slow when you upgrade from e.g. MySQL 5.1.32 to MySQL 5.1.33. The change is for good generally. That thing is intended to choose random pages but it does not do so very well and even in some cases it picks up always the same page. A fix for this will be developed (to use better RNG) and we will reconsider in which versions to put it. Thanks!
[25 Mar 2009 21:45]
John David Duncan
Here is a patch that I would think is equivalent to Shane's one-line fix, but a little better. -- page0cur.c.orig +++ page0cur.c @@ -16,7 +16,7 @@ #include "log0recv.h" #include "rem0cmp.h" -static ulint page_rnd = 976722341; +static uint32 page_rnd = 976722341;
[1 Apr 2009 19:16]
MySQL Verification Team
Could the root cause of this bug be related to the cause of bug #36513?
[7 Apr 2009 11:35]
Sergey Vojtovich
A patch introducing new innodb_cardinality_algorithm option.
Attachment: innodb_cardinality_algorithm.patch (text/x-patch), 4.38 KiB.
[8 Apr 2009 13:03]
Vasil Dimov
Patch for 5.1
Attachment: bug43660-5.1.diff (application/octet-stream, text), 6.37 KiB.
[8 Apr 2009 13:05]
Vasil Dimov
Hello, here is a patch for MySQL 5.1: http://bugs.mysql.com/file.php?id=11775 for early testing.
[8 Apr 2009 13:13]
Vasil Dimov
To summarize: The original algorithm has a bug that the random numbers that it picks are always of the form 3*k + 2, that is - the remainder from the division by 3 is constant (2). When the "random" counter wraps around then the constant becomes 1 instead of 2. This happens after several 100s iterations on 32 bit machines and practically does not happen on 64 bit machines because much more iterations are needed there. What is observed is that the algo always picks the second page the first several 100s iterations and then starts to pick the first page. Making the 64 bit version wrap like the 32 bit one is in not fixing the bug. The patch that I have attached uses a better PRNG that does not suffer from this problem.
[9 Apr 2009 12:29]
Sergey Vojtovich
A 5.0 backport of patch proposed by Vasil.
Attachment: innodb_cardinality_algorithm.patch (text/x-patch), 7.11 KiB.
[15 Apr 2009 12:42]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/72153 2860 Satya B 2009-04-15 Applying InnoDB snashot 5.1-ss4699, part 3. Fixes BUG#43660 1) BUG#43660 - SHOW INDEXES/ANALYZE does NOT update cardinality for indexes of InnoDB table Detailed revision comments: r4699 | vasil | 2009-04-09 14:01:52 +0300 (Thu, 09 Apr 2009) | 15 lines branches/5.1: Fix Bug#43660 SHOW INDEXES/ANALYZE does NOT update cardinality for indexes of InnoDB table by replacing the PRNG that is used to pick random pages with a better one. This is based on r4670 but also adds a new configuration option and enables the fix only if this option is changed. Please skip the present revision when merging. Approved by: Heikki (via email) modified: storage/innobase/handler/ha_innodb.cc storage/innobase/include/srv0srv.h storage/innobase/page/page0cur.c storage/innobase/srv/srv0srv.c
[20 Apr 2009 6:58]
Satya B
Notes to Docs team: Patch queued to 5.1-bugteam. NOT yet in 6.0 (5.1-snapshot-ss4699 is null merged into 6.0). Please return to "Patch approved" after documenting until 6.0 snapshot is available.
[24 Apr 2009 11:04]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/72768 2729 Satya B 2009-04-24 Fix for BUG#43660- SHOW INDEXES/ANALYZE does NOT update cardinality for indexes of InnoDB table Fixes by replacing the PRNG that is used to pick random pages with a better one. Also adds a configuration option "innodb_use_legacy_cardinality_algorithm" to enable the fix only when the option is set. This patch is from http://bugs.mysql.com/file.php?id=11789 modified: innobase/include/srv0srv.h innobase/page/page0cur.c innobase/srv/srv0srv.c sql/ha_innodb.h sql/mysqld.cc sql/set_var.cc
[24 Apr 2009 11:17]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/72769 2869 Satya B 2009-04-24 [merge] Null merge of BUG#43660 into 5.1 as the bug is already fixed in 5.1
[5 May 2009 18:53]
Bugs System
Pushed into 5.0.82 (revid:davi.arnaut@sun.com-20090505184158-dvmedh8n472y8np5) (version source revid:davi.arnaut@sun.com-20090505184158-dvmedh8n472y8np5) (merge vers: 5.0.82) (pib:6)
[5 May 2009 19:42]
Bugs System
Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)
[6 May 2009 14:07]
Bugs System
Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:satya.bn@sun.com-20090424113253-22ax5r19v28vax5r) (merge vers: 6.0.11-alpha) (pib:6)
[19 May 2009 7:54]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/74452 2889 Satya B 2009-05-19 Applying InnoDB snashot 5.1-ss5024 part 1, Follow up Fix for BUG#43660 Detailed revision comments: r4705 | vasil | 2009-04-14 14:30:13 +0300 (Tue, 14 Apr 2009) | 7 lines branches/5.1: When using the random function, first take the modulus by the number of pages and then typecast to ulint. This is a followup to r4699 - the fix of Bug#43660. modified: storage/innobase/page/page0cur.c
[27 May 2009 20:02]
Paul DuBois
Noted in 5.0.82, 5.1.35 changelogs. InnoDB uses random numbers to generate dives into indexes for calculating index cardinality. However, under certain conditions, the algorithm did not generate random numbers, so ANALYZE TABLE did not update cardinality estimates properly. A new algorithm has been introduced with better randomization properties, together with a system variable, innodb_use_legacy_cardinality_algorithm, that controls which algorithm to use. The default value of the variable is 0 (OFF), to use the original algorithm for compatibility with existing applications. The variable can be set to 1 (ON) to enable the new algorithm with improved randomness.
[28 May 2009 8:15]
Bugs System
Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:satya.bn@sun.com-20090519075219-qkob0n5flp22fl4v) (merge vers: 5.1.36) (pib:6)
[28 May 2009 18:51]
Paul DuBois
Correction. The changelog entry should say: The default value of the variable is 1 (ON), to use the original algorithm for compatibility with existing applications. The variable can be set to 0 (OFF) to use the new algorithm with improved randomness.
[15 Jun 2009 8:28]
Bugs System
Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)
[15 Jun 2009 9:08]
Bugs System
Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)
[15 Jun 2009 9:49]
Bugs System
Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)
[17 Jun 2009 19:22]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:satya.bn@sun.com-20090519083733-iksol2m6mcevrsgy) (merge vers: 6.0.12-alpha) (pib:11)
[26 Jun 2009 6:53]
Vasil Dimov
Paul, This fix will be put in 5.4, 6.0 and the InnoDB Plugin without the config knob. The config knob in 5.0 and 5.1 is because they are GA and frozen for changes that may change query plans (by default).
[26 Jun 2009 18:33]
Paul DuBois
Noted in 5.4.4 changelog.
[12 Aug 2009 22:39]
Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 1:55]
Paul DuBois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:45]
Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46]
Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48]
Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:32]
Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[7 Oct 2009 1:22]
Paul DuBois
The 5.4 fix has been pushed into 5.4.2.
[29 Oct 2009 1:21]
Wes Biggs
This may not be the right place to ask, but does this need to be ported into the InnoDB plugin? I'm running with the plugin and 5.1.40 on 64-bit Linux but do not see this option.
[29 Oct 2009 2:28]
Paul DuBois
Wes, see comment at [26 Jun 8:53]. InnoDB Plugin uses the new algorithm by default, hence no configuration option for enabling it.
[5 May 2010 15:07]
Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 17:53]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 6:07]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:35]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 7:03]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 15:10]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[15 Jun 2010 8:10]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:26]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 12:12]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:59]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:39]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
Description: Normally repeated calls of ANALYZE TABLE/SHOW INDEXES/SHOW TABLE STATUS updates estimated number of rows and, thus, cardinality of PK and unique indexes for InnoDB table (because of "random dives"). Like this: C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 5.1.30-community-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show indexes from sources\G *************************** 1. row *************************** Table: sources Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: source_id Collation: A Cardinality: 535 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: sources Non_unique: 0 Key_name: uniq_short_name Seq_in_index: 1 Column_name: short_name Collation: A Cardinality: 535 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 3. row *************************** Table: sources Non_unique: 1 Key_name: country_id Seq_in_index: 1 Column_name: country_id Collation: A Cardinality: 107 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 3 rows in set (0.03 sec) mysql> show indexes from sources\G *************************** 1. row *************************** Table: sources Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: source_id Collation: A Cardinality: 259 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: sources Non_unique: 0 Key_name: uniq_short_name Seq_in_index: 1 Column_name: short_name Collation: A Cardinality: 259 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 3. row *************************** Table: sources Non_unique: 1 Key_name: country_id Seq_in_index: 1 Column_name: country_id Collation: A Cardinality: 51 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 3 rows in set (0.00 sec) mysql> show indexes from sources\G *************************** 1. row *************************** Table: sources Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: source_id Collation: A Cardinality: 166 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: sources Non_unique: 0 Key_name: uniq_short_name Seq_in_index: 1 Column_name: short_name Collation: A Cardinality: 166 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 3. row *************************** Table: sources Non_unique: 1 Key_name: country_id Seq_in_index: 1 Column_name: country_id Collation: A Cardinality: 33 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 3 rows in set (0.00 sec) mysql> select count(distinct short_name) from sources; +----------------------------+ | count(distinct short_name) | +----------------------------+ | 288 | +----------------------------+ 1 row in set (0.06 sec) So, one can execute these commands several times to get proper cardinality estimation and good execution plans for the queries. But on some 64-bit platforms (FreeBSD 6.3, 7.0, Mac OS X 10.5.6, CentOS 5.2) these commands do NOT change initial (wrong) estimations: valeriy-kravchuks-macbook-pro:5.0 openxs$ bin/mysql -uroot --host=192.168.203.131 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 4.1.25-pro-gpl Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select count(distinct short_name) from sources; +----------------------------+ | count(distinct short_name) | +----------------------------+ | 288 | +----------------------------+ 1 row in set (0.00 sec) mysql> show indexes from sources\G *************************** 1. row *************************** Table: sources Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: source_id Collation: A Cardinality: 337 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: sources Non_unique: 0 Key_name: uniq_short_name Seq_in_index: 1 Column_name: short_name Collation: A Cardinality: 337 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 3. row *************************** Table: sources Non_unique: 1 Key_name: country_id Seq_in_index: 1 Column_name: country_id Collation: A Cardinality: 84 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 3 rows in set (0.00 sec) mysql> analyze table sources; +--------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+---------+----------+----------+ | test.sources | analyze | status | OK | +--------------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> show indexes from sources\G *************************** 1. row *************************** Table: sources Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: source_id Collation: A Cardinality: 337 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: sources Non_unique: 0 Key_name: uniq_short_name Seq_in_index: 1 Column_name: short_name Collation: A Cardinality: 337 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 3. row *************************** Table: sources Non_unique: 1 Key_name: country_id Seq_in_index: 1 Column_name: country_id Collation: A Cardinality: 67 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 3 rows in set (0.00 sec) mysql> analyze table sources; +--------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+---------+----------+----------+ | test.sources | analyze | status | OK | +--------------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> show indexes from sources\G *************************** 1. row *************************** Table: sources Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: source_id Collation: A Cardinality: 337 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: sources Non_unique: 0 Key_name: uniq_short_name Seq_in_index: 1 Column_name: short_name Collation: A Cardinality: 337 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 3. row *************************** Table: sources Non_unique: 1 Key_name: country_id Seq_in_index: 1 Column_name: country_id Collation: A Cardinality: 67 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 3 rows in set (0.00 sec) As a result, optimizer chooses wrong execution plans for the query with join to this table and this lead to serious perfromance problems. There is no other workaround but converting the table to MyISAM so far. How to repeat: On 64-bit FreeBSD, Mac OS X or Linux, using 64-bit(!) MySQL 4.1.24+ (including latest 5.0.77+ and 5.1.33+) create table using sources_dump.sql files uploaded and run: show index status from sources\G analyze table sources; show tables status like 'sources'\G etc. in any combination. Observe no changes in cardinality values for source_id and short_name columns. Then try the same on 32-bit OS and compare the results. Suggested fix: Make "random dives" really random on 64-bit platform or use "select count(*) from t" internally to estimate cardinality of unique indexes in InnoDB.