CODEWITHSUNDEEP
mysqlindexing
Kirk Logan
Kirk Logan

Reputation: 783

MySQL - Huge difference in cardinality on what should be a duplicate table

On my development server I have a column indexed with a cardinality of 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.

However the production servers index has a cardinality of 31938.

They are both mysql 5.5 however my dev server is Ubuntu server 13.10 and the production server is 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.

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.

Upvotes: 0

Views: 585

Answers (1)

erik258
erik258

Reputation: 16304

Mysql checksums might help you verify that the tables are the same

-- 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).

Beyond checksumming, you might consider looking at REPAIR OR OPTIMIZE.

Upvotes: 1

Related Questions