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?