Mariadb – Indexes on XtraDB tables

mariadbxtradb

I have old db with myisam tables, using mariadb and want try move to xtradb for row-level locking. Each table have 27 columns, primary index and other 11 indexes. Columns with indexes have many selects with where, group and order. I make some test and I'm little confused.

Table with about 6M rows – disk space (data + keys):
myisam – 915MiB
innodb (xtradb) – 2011MiB

On innodb(xtradb) I try large select with many where, group and order on indexed columns and it takes 63s, but if I delete all other indexes – only primary stay, then it takes 21s.

It looks like if I don't use transaction, foreign keys, joins, etc…on this tables, that primary key is fine and all others is useless. But I think this is very strange :-/

I don't have hardware for better test, are you guys think it's good idea to convert all tables to inno(xtra) and drop all indexes except primary in production environment?

Best Answer

You should index as per usage. If you drop all indexes for the benefit of a small % of your workload you will hit pain elsewhere. Run pt-duplicate-key-checker 1st and remove the redundant keys. Then review your queries with pt-query-digest and weed out the poorly performing queries by reindexing them or rewriting them. You could paste the output of EXPLAIN with your table structure and status for your problematic query for further help tuning on that.