
Detecting when OPTIMIZE is needed.
Moderator: General Moderators
- Sevengraff
- Forum Contributor
- Posts: 232
- Joined: Thu Apr 25, 2002 9:34 pm
- Location: California USA
- Contact:
Detecting when OPTIMIZE is needed.
In phpMyAdmin, it seems to be able to tell when you need to run OPTOMIZE TABLE `blah`. It doesn't seem to pop up a lot, but I was thinking that it would be good if my script could detect when a table needs to be optomized and then run the needed query.


http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html will tell you more about it, or actually the link to ANALYZE TABLE.
Using analyze, you get a msg_type that you can use to determine your action(s).
Using analyze, you get a msg_type that you can use to determine your action(s).
- Sevengraff
- Forum Contributor
- Posts: 232
- Joined: Thu Apr 25, 2002 9:34 pm
- Location: California USA
- Contact:
Well, not sure, as i needed to test it myself and found:
...where I have the same red overhead as you had. Lets report back here whoever finds a solution.
Code: Select all
Array
(
їTable] => jam_l.posts
їOp] => analyze
їMsg_type] => status
їMsg_text] => Table is already up to date
)Might have found it...
Before "OPTIMIZE TABLE USERS" the above was not 0. Try on your own table and see what happens.
Code: Select all
SHOW TABLE STATUS like 'users'
Array
(
їName] => users
їType] => MyISAM
їRow_format] => Dynamic
їRows] => 3
їAvg_row_length] => 162
їData_length] => 488
їMax_data_length] => 4294967295
їIndex_length] => 3072
їData_free] => 0
їAuto_increment] =>
їCreate_time] => 2003-10-04 00:34:55
їUpdate_time] => 2003-10-04 04:25:33
їCheck_time] => 2003-10-04 04:25:33
їCreate_options] =>
їComment] =>
)Code: Select all
-- interesting part of the above:
їData_free] => 0- Sevengraff
- Forum Contributor
- Posts: 232
- Joined: Thu Apr 25, 2002 9:34 pm
- Location: California USA
- Contact:
No, thank you. Interesting subject that I've never thought of.
Just don't add it into a averagely large script as the optimize function locks the table resulting in other users errors. (In the meaning of adding it to a template system or similiar.) Just thought I'd mention it just if anyone else reads it and gets the bright idea...
Just don't add it into a averagely large script as the optimize function locks the table resulting in other users errors. (In the meaning of adding it to a template system or similiar.) Just thought I'd mention it just if anyone else reads it and gets the bright idea...
- Sevengraff
- Forum Contributor
- Posts: 232
- Joined: Thu Apr 25, 2002 9:34 pm
- Location: California USA
- Contact:
The followup question about this would be; what happens in ½-year when you havn't optimized your users_online table once?
Will it begin to display errors or merely take up more space on the servers hdd than it actually requires...
I was thinking that adding an automated function to the script perhaps isn't such a bad idea, as you can make it run on certain days of the month or similiar...
Will it begin to display errors or merely take up more space on the servers hdd than it actually requires...
I was thinking that adding an automated function to the script perhaps isn't such a bad idea, as you can make it run on certain days of the month or similiar...
Code: Select all
if (date(j H:i:s) == '1 00:00:00') {
function_optimize_tables();
}- Sevengraff
- Forum Contributor
- Posts: 232
- Joined: Thu Apr 25, 2002 9:34 pm
- Location: California USA
- Contact:
-
Cruzado_Mainfrm
- Forum Contributor
- Posts: 346
- Joined: Sun Jun 15, 2003 11:22 pm
- Location: Miami, FL
To point out a few things, running optimize table often isn't good. From the manual:
"In most setups you don't have to run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable length rows it's not likely that you need to do this more than once a month/week and only on certain tables."
The key thing to remember is that you should design your tables so that this isn't necessary.
For example, make your tables fixed length rather than variable length. This would mean replacing all your varchar fields with char fields, and what not. However, when you actually look at what you are doing, this is actually easier than you suspect.
Fixed length tables are also quicker to use, and search through. The reason is because to determine the size of the table, MySQL has to do less computations. For example, using an index, if MySQL knows that record 45,000 is what it needs, on a fixed length table, it just needs to go to the 45,000xbytes_per_record and start reading. Otherwise, on a variable lengthed table, it has to read through the database, etc. That's a very unofficial description, but it's basically how it works.
"In most setups you don't have to run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable length rows it's not likely that you need to do this more than once a month/week and only on certain tables."
The key thing to remember is that you should design your tables so that this isn't necessary.
For example, make your tables fixed length rather than variable length. This would mean replacing all your varchar fields with char fields, and what not. However, when you actually look at what you are doing, this is actually easier than you suspect.
Fixed length tables are also quicker to use, and search through. The reason is because to determine the size of the table, MySQL has to do less computations. For example, using an index, if MySQL knows that record 45,000 is what it needs, on a fixed length table, it just needs to go to the 45,000xbytes_per_record and start reading. Otherwise, on a variable lengthed table, it has to read through the database, etc. That's a very unofficial description, but it's basically how it works.