I have an InnoDB table of 21.000.000 rows, and 13 columns. All queries including this table are very long ; a SELECT of an indexed column for example:
SELECT SQL_NO_CACHE COUNT(id) FROM t;
1 row in set (29.86 sec)
I made some searches (e.g. "SELECT COUNT(*)" is slow, even with where clause) , and all the answers from the same issue are : my table is fragmented. But when I look at the informations, it does not like to be so fragmented :
> SELECT * FROM information_schema.tables WHERE TABLE_NAME="t" AND table_schema="s"\G;
DATA_LENGTH: 1726447616
INDEX_LENGTH: 3371892736
DATA_FREE: 8388608
This give me (using http://meinit.nl/optimize-only-fragmented-tables-mysql method) a very low fragmentation value:
8388608/(1726447616+3371892736) = 0.16%
The only solutions I have is to:
- Run an
OPTIMIZE TABLE, but it will lock the table, and this is not acceptable for the moment in production mode (evaluated as 5 hours of lock) - Run
ALTER TABLE t ENGINE=InnoDB(from https://dba.stackexchange.com/questions/16341/how-do-you-remove-fragmentation-from-innodb-tables ), it only lock inread, but not acceptable for same reasons.
Do you have any other solutions before I get the production in maintenance mode ?
