top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Optimizing InnoDB tables in MySQL?

+1 vote
530 views

I was trying to optimize the InnoDB tables. I have executed the next query to detect what are the fragmented tables.

SELECT TABLE_SCHEMA,TABLE_NAME
FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND Data_free > 0

After that, I have seen that there are 49 fragmented tables. With one table, I have executed "optimize table table_name;" and "analyze table table_name;". The result is the same, the table continuous fragmented.

Any suggestions? I have followed the mysqltuner recommendations...

posted Jun 24, 2014 by Rameshwar

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote

What's the mysql version you're running?
By the way have you tried to ALTER TABLE x ENGINE=InnoDB?

answer Jun 24, 2014 by Sidharth
I'm running MySQL Percona Server 5.5.30 64Bits. No, I don't have tried to execute
ALTER TABLE (Analyze with InnoDB tables do that, or not?).

Let me give a try, I hope it should work.
commented Jun 24, 2014 by anonymous
Similar Questions
+1 vote
How can I work with Big innodb tables?

I have in my server database some tables that are too much big and produce some slow query, even with correct indexes created.

For my application, it's necessary to have all the data because we make an authentication process with RADIUS users (AAA protocol) to determine if one user can or not navigate in Internet (Depending on the time of all his sessions).

So, with 8GB of data in one table, what are your advises to follow? Fragmentation and sharing discarded because we are working with disk arrays, so not apply. Another option is to delete rows, but in this case, I
can't. For the other hand, maybe the only possible solution is increase the resources (RAM).

Any suggestions?

+1 vote
InnoDB problem on MySQL - table does not exist

I've restored an MySQL backup from our MySQL server into another server. The backup includes InnoDB tables. After the import, MySQL recognized the innodb tables fine but when I try to do a check table it returns that the table doesn't exists.

Permission and owner of the table files (.frm files) are ok, since it recognizes MyISAM tables (they have the same permission). Innodb engine is enabled..

Which can cause the tables to appears as "non existent", as far as they do really exist?

+2 votes
MySQL Archive Engine

I have a customer who is wanting to use the Archive Engine. I have no experience with this engine, other than what I am been reading. Why would I want to use Archive over InnoDB. They are only going to be placing audit information in the table.

+1 vote
Optimizing MySQL query

I have a MySQL Query which has Join statement, left side of the join statement returns 1800 rows and right side is about 1500 after join I jut need top 50 rows. Total operation takes about 5 sec.
Any suggestions?

...