What does optimizing tables do exactly?

[eluser]Zac G.[/eluser]
I have been perusing the docs and came across this one:

Code:

$this->dbutil->optimize_table('table_name');

I know that I have optimized MySQL tables before, but what exactly does this do?

Thanks!

[eluser]Pascal Kriete[/eluser]
It runs an 'OPTIMIZE TABLE tablename' query. For mysql that reduces fragmentation and cleans up some stuff - mysql docs for details.

[eluser]Milaan[/eluser]
How do i have to use this function if i connect to a db like...

Code:

$live_db = $this->load->database($server);
$this->dbutil->optimize_table('xxx');

[eluser]Zac G.[/eluser]
Thanks! So I doesn't look like something I have to do when emptying a table though, right?

[eluser]AgentPhoenix[/eluser]
I tend to throw this method onto the end of any create, update or delete methods so the database stays un-fragmented. It may cost a little bit more in performance, but at least I know the database isn't getting a lot of overhead. Not sure about when emptying a table though. I guess it's possible that emptying a table could still create some overhead. You'd likely need to empty a table without optimizing and see if any overhead is still around. If there isn't, then no, you shouldn't have to do that.

[eluser]Zac G.[/eluser]
Thanks. does it reset the id count for auto increment?

[eluser]AgentPhoenix[/eluser]
I don't know if emptying a table resets its auto increment value, you'd have to check the MySQL documentation for that, but I know optimizing it doesn't reset that.