Linked Questions
12 questions linked to/from How do you remove fragmentation from InnoDB tables?
14
votes
3
answers
25k
views
Will OPTIMIZE TABLE have any impact on my data?
I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables.
But before doing so, I would (of course) like to know if ...
6
votes
3
answers
8k
views
InnoDB vs MyISAM with many indexes
I am not sure if there is a right or wrong answer to this question, as I suppose it severely depends on the situation, but for the sake of a mental exercise, I'll go ahead and ask it anyway:
I have a ...
- 203
5
votes
1
answer
11k
views
How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed
I just switched my Wordpress databases to use the InnoDB engine.
When using MyISAM, I used to run Optimize and Repair table. However, with InnoDB, these don't work.
I manually ran OPTIMIZE TABLE ...
- 153
4
votes
1
answer
12k
views
Move ibdata1, set innodb_data_file_path
I am in a bit of an awkward situation right now. I have a database with a few large InnoDB tables, one of which is about 145 GiB. I have been running a lot of maintenance on this table in particular, ...
- 203
4
votes
2
answers
5k
views
What happens when InnoDB hits its tablespace autoextend max?
The latest version of High Performance MySQL (3rd ed.) recommends that one:
...place an upper limit on the tablespace's size to keep it from growing
very large, because once it grows, it doesn't ...
- 359
4
votes
2
answers
4k
views
Does mysql use deleted rows space in a table?
I am deleting a large data around 60GB from one of my innodb table which is around 70GB.
This is a rapidly growing table so i keep on inserting and deleting the records over a period of time. I ...
- 419
4
votes
1
answer
3k
views
How can I *actually* defragment my InnoDB tables?
After a recommendation by mysqltuner I've run mysqlcheck --optimize --all-databases successfully (all of the tables reported "note : Table does not support optimize, doing recreate + analyze instead" ...
- 403
1
vote
1
answer
3k
views
MySQL dynamically optimize innodb tables without "file per table" setting
We are getting a “too many connections” error once a week the same time a mysql procedure runs. The procedure runs “optimize table” on hundreds of tables and takes nearly ten hours to finish, taking ...
- 13
1
vote
1
answer
2k
views
Removing/overwriting sensitive data located in already deleted records in ibdata files
As far as I know, using InnoDB tables with innodb_file_per_table = off does not prevent deleted data from being visible in the ibdata1 file.
I know this is by-design, and that I could switch the ...
- 111
0
votes
1
answer
550
views
Maintenance in MYSQL when innodb_file_per_table disabled
I have read your post and I completely understand OPTIMIZE TABLE to perform in an environment where innodb_file_per_table is disabled, does not shrink the global ibdata1 tablespace.
But what if I ...
- 1
3
votes
1
answer
515
views
Finding out the exact size of the database after inserting records?
I have this query:
SELECT
table_schema "db.name"
, Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM
information_schema.tables
GROUP BY
table_schema;
Does this query ...
- 31
3
votes
2
answers
70
views
Should I put followers_no & following_no & product_no & last_login and etc in the user table? Won't updates cause fragmentation on the table?
I have a table for users in a social networking site. I want it to be efficient from ground up. If I put mentioned fields on the table then I have to update user's table on each changes. Should I have ...
- 3,646