Page 1 of 1
Detecting when OPTIMIZE is needed.
Posted: Fri Oct 03, 2003 7:55 pm
by Sevengraff
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.

Posted: Fri Oct 03, 2003 8:25 pm
by JAM
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).
Posted: Fri Oct 03, 2003 8:38 pm
by Sevengraff
Thanks! I'll look into that then.
Posted: Fri Oct 03, 2003 8:45 pm
by JAM
Well, not sure, as i needed to test it myself and found:
Code: Select all
Array
(
їTable] => jam_l.posts
їOp] => analyze
їMsg_type] => status
їMsg_text] => Table is already up to date
)
...where I have the same red overhead as you had. Lets report back here whoever finds a solution.
Posted: Fri Oct 03, 2003 9:27 pm
by JAM
Might have found it...
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
Before "OPTIMIZE TABLE USERS" the above was not 0. Try on your own table and see what happens.
Posted: Fri Oct 03, 2003 9:40 pm
by Sevengraff
I think you've got it. that looks like it's what phpMyAdmin looks for. On tables that it says need optomization, [Data_free] is never 0.
Thanks a bunch man.
Posted: Fri Oct 03, 2003 9:45 pm
by JAM
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...

Posted: Fri Oct 03, 2003 9:58 pm
by Sevengraff
it seems that tables that hold "users online" data need to be optomized rather often, as they are updated every time someone loads a page.
Posted: Fri Oct 03, 2003 10:08 pm
by JAM
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...
Code: Select all
if (date(j H:i:s) == '1 00:00:00') {
function_optimize_tables();
}
Posted: Fri Oct 03, 2003 10:20 pm
by Sevengraff
yeah but then your assuming the script will run exactly that second. better to have something else trigger it. like when the admin logs in, or access a specifc place maybe.
Posted: Fri Oct 03, 2003 11:21 pm
by Cruzado_Mainfrm
if you have unix, u can set a cronjob to execute the file every hour or so
Posted: Sat Oct 04, 2003 9:59 am
by McGruff
Multi-table JOIN queries can be more efficient with optimised tables.
Posted: Sat Oct 04, 2003 3:55 pm
by evilMind
You might want to check the keys in your table. If you modfiy a key over and over the table has to re-index it'self resulting in overhead
* actually every time you modify a key/index/etc the table has to re-index itself *
Posted: Sat Oct 04, 2003 5:18 pm
by jason
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.