Menu
I was reading about refactoring a large slow SQL Query over here, and the current highest response is from Mitch Wheat, who wants to make sure the query uses indexes for the major selects, and mentions:
First thing I would do is check to make sure there is an active index maintenance job being run periodically. If not, get all existing indexs rebuilt or if not possible at least get statistics updated.
I'm only am amateur DBA, and I've made a few programs freelance that are basically Java desktop clients and occasionally a MySQL backend. When I set up the system, I know to create an index on the columns that will be queried by, there's a varchar CaseID and a varchar CustName.
However, I set this system up months ago and left the client operating it, and I believe the indexes should grow as data is entered and I believe everything is still working nicely. I'm worried though that the indexes should be rebuilt periodically, because today i have read that there should be an 'active maintenance job'. The only maintenance job I set on the thing was a nightly backup.
I wanted to ask the community about regular maintenance that a database might require. Is it neccessary to rebuild indexes? Can I trust the MySQL backend to keep going so long as no one messes with it and the data stays under a few gigabytes?
312
Microsoft recommends fixing index fragmentation issues by rebuilding the index if the fragmentation percentage of the index exceeds 30%, where it recommends fixing the index fragmentation issue by reorganizing the index if the index fragmentation percentage exceeds 5% and less than 30%.
When and how often should you Rebuild Indexes? The performance of your indexes, and therefore your database queries, will degrade as you indexes become fragmented. The Rebuild Index task does a very good job of rebuilding indexes to remove logical fragmentation and empty space, and updating statistics.
Reorganize an indexReorganizing an index is less resource intensive than rebuilding an index. For that reason it should be your preferred index maintenance method, unless there is a specific reason to use index rebuild. Reorganize is always an online operation.
Rebuild and Reorganize Index using SQL Server Management Studio (SSMS) Find and expand the table in Object Explorer >> Open Indexes >> Right-click on the target index >> Rebuild or Reorganize.
There is no need to 'rebuild' an index. They are always kept up-to-date. Maybe he was referring to rebuilding the table. Depending on your usage patterns and schema, you can get fragmented pages in InnoDB, and I think in MyISAM also. Rebuilding the table can improve performance by getting rid of fragmentation of your data on disk. I don't use MyISAM tables regularly, but I believe it is recommended to run 'OPTIMIZE TABLE' with certain usage patterns. See the MySQL docs on OPTIMIZE TABLE for some good info on both MyISAM and InnoDB.
I'm not as familiar with MyISAM intricacies, but with InnoDB it is true that statistics can get out of date. The database keeps estimated statistics on how your data is distributed for a given index, and it's possible for those to get out-of-date, but MySQL/InnoDB has some built in functionality to try to keep statistics current. You usually don't have to worry about it.
So if you are using InnoDB, the answer is no, you typically don't need to actively do anything to keep your indexes performing well. I'm not as sure with MyISAM, I think it's more common to need to optimize those tables regularly.
163
It's usually a good idea to set up a cronjob to optimize indexes and check for errors.
See mysqlcheck. A typical cron job looks something like mysqlcheck -Aaos , which checks all tables in all databases for errors, optimizes indexes, and only outputs on error.
11
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
