# More efficient MySQL Maintenance logic

**URL:** <https://forums.percona.com/t/more-efficient-mysql-maintenance-logic/1254>\
**Category:** Other MySQL® Questions\
**Created:** [September 6, 2009, 4:44pm UTC](https://forums.percona.com/t/more-efficient-mysql-maintenance-logic/1254 "2009-09-06T16:44:06Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![tomp\_gl](https://avatars.discourse-cdn.com/v4/letter/t/b487fb/32.png) [@tomp\_gl](https://forums.percona.com/u/tomp_gl)\
**Post date:** [September 6, 2009, 4:44pm UTC](https://forums.percona.com/t/more-efficient-mysql-maintenance-logic/1254/1 "2009-09-06T16:44:06Z")

</div>

Hi,

For the last few years I have been using a fairly basic MySQL maintenance script that OPTIMIZE’s, ALTER’s by the id field and ANAYLYZE’s, in that order, every table in my database.

I have been noticing this script is causing some bottlenecks as the loop iterates over large tables and started to research whether and/or how often these queries should be performed on a table.

From what I have read:

- I should only be running OPTIMIZE on tables where the Data\_free variable from SHOW TABLE STATUS LIKE ‘$table’ is greater than 0
- I shouldn’t bother running ANALYZE on InnoDB tables at all as the accuracy of the cardinality is poor.

My question is - are these findings accurate and are there other considerations I should be taking into account?

This is the basic logic of my current script:

[COLOR=#0000BB]\<?php foreach ([COLOR=#0000BB]$tables as [COLOR=#0000BB]$table) { [COLOR=#0000BB]mysql\_query([COLOR=#DD0000]"OPTIMIZE TABLE $table"); [COLOR=#0000BB]mysql\_query([COLOR=#DD0000]"ALTER TABLE $table ORDER BY id"); [COLOR=#0000BB]mysql\_query([COLOR=#DD0000]"ANALYZE TABLE $table"); } [COLOR=#0000BB]?\>

Thanks

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [September 8, 2009, 4:10am UTC](https://forums.percona.com/t/more-efficient-mysql-maintenance-logic/1254/2 "2009-09-08T04:10:36Z")

</div>

Best of all: ALTER TABLE $table ORDER BY id has no influence on InnoDB tables which are always sorted by the primary key.

Personally, I would not run these queries on any table unless that table is totally cleared/rewritten often.