On my development server I have a column indexed with a cardinality of 200.
在我的开发服务器上，我有一个索引为基数为200的列。
The table has about 6 million rows give or take and I have confirmed it is an identical row count on the production server.
该表有大约600万行给予或采取，我已经确认它是生产服务器上相同的行数。
However the production servers index has a cardinality of 31938.
但是，生产服务器索引的基数为31938。
They are both mysql 5.5 however my dev server is Ubuntu server 13.10 and the production server is Windows server 2012.
它们都是mysql 5.5但是我的开发服务器是Ubuntu服务器13.10而生产服务器是Windows server 2012。
Any ideas on what would cause such a difference in what should be the exact same data?
关于什么会导致什么应该是完全相同的数据的差异的任何想法？
The data was loaded into the production server from a MySQL dump of the dev server.
数据从开发服务器的MySQL转储加载到生产服务器。
EDIT: Its worth noting that I have queries that take about 15 minutes to run on my dev server that seem to run forever on the production server due to what i believe to be these indexing issues. Different amounts of rows are being pulled within sub-queries.
编辑：值得注意的是，我的查询大约花了15分钟在我的开发服务器上运行，似乎在生产服务器上永远运行，因为我认为这些索引问题。在子查询中拉出不同数量的行。
1 个解决方案
#1
1
Mysql checksums might help you verify that the tables are the same
Mysql校验和可能会帮助您验证表是否相同
-- a table
create table test.t ( id int unsigned not null auto_increment primary key, r float );
-- some data ( 18000 rows or so )
insert into test.t (r) select rand() from mysql.user join mysql.user u2;
-- a duplicate
create table test.t2 select * from test.t;
-- introduce a difference somewhere in there
update test.t2 set r = 0 order by rand() limit 1;
-- and prove the tables are different easily:
mysql> checksum table test.t;
+--------+------------+
| Table | Checksum |
+--------+------------+
| test.t | 2272709826 |
+--------+------------+
1 row in set (0.00 sec)
mysql> checksum table test.t2
-> ;
+---------+-----------+
| Table | Checksum |
+---------+-----------+
| test.t2 | 312923301 |
+---------+-----------+
1 row in set (0.01 sec)
Beware the checksum locks tables.
请注意校验和锁表。
For more advanced functionality, the percona toolkit can both checksum and sync tables (though it's based on master/slave replication scenarios so it might not be perfect for you).
对于更高级的功能，percona工具包可以是校验和表和同步表（尽管它基于主/从复制方案，因此它可能不适合您）。
Beyond checksumming, you might consider looking at REPAIR OR OPTIMIZE.
除了校验和之外，您可以考虑查看REPAIR OR OPTIMIZE。
#1
1
Mysql checksums might help you verify that the tables are the same
Mysql校验和可能会帮助您验证表是否相同
-- a table
create table test.t ( id int unsigned not null auto_increment primary key, r float );
-- some data ( 18000 rows or so )
insert into test.t (r) select rand() from mysql.user join mysql.user u2;
-- a duplicate
create table test.t2 select * from test.t;
-- introduce a difference somewhere in there
update test.t2 set r = 0 order by rand() limit 1;
-- and prove the tables are different easily:
mysql> checksum table test.t;
+--------+------------+
| Table | Checksum |
+--------+------------+
| test.t | 2272709826 |
+--------+------------+
1 row in set (0.00 sec)
mysql> checksum table test.t2
-> ;
+---------+-----------+
| Table | Checksum |
+---------+-----------+
| test.t2 | 312923301 |
+---------+-----------+
1 row in set (0.01 sec)
Beware the checksum locks tables.
请注意校验和锁表。
For more advanced functionality, the percona toolkit can both checksum and sync tables (though it's based on master/slave replication scenarios so it might not be perfect for you).
对于更高级的功能，percona工具包可以是校验和表和同步表（尽管它基于主/从复制方案，因此它可能不适合您）。
Beyond checksumming, you might consider looking at REPAIR OR OPTIMIZE.
除了校验和之外，您可以考虑查看REPAIR OR OPTIMIZE。