Support Forum

Simple:Press 6 General Topics

Invalid database query

Page: 1 2

Invalid database query

Yellow Swordfish

Glinton, England

SP Master

Offline

Dec 9, 2014 - 9:20 am

Do you know I have never investigated that and will clearly have to. InnoDB tables are more efficient and lock by row instead of table during operations. It might be that they do not fragment in quite the same way.

I will google it....

YELLOW

SWORDFISH

kvr28

Member

Free Members

Offline

Dec 9, 2014 - 9:25 am

I ggogled it last night after reading this thread and came across this on stack overflow

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index. Beginning with MySQL 5.1.27, this is displayed in the output of OPTIMIZE TABLE when you run it on an InnoDB table, as shown here:

You can make OPTIMIZE TABLE work on other storage engines by starting mysqld with the --skip-new or --safe-mode option. In this case, OPTIMIZE TABLE is just mapped to ALTER TABLE.

http://stackoverflow.com/quest.....nodb-table

but I have no clue what that means lol

Yellow Swordfish

Glinton, England

SP Master

Offline

Dec 9, 2014 - 10:32 am

I have to say that Stack Overflow always seems to me to be either spot on or a long way off! I don't see evidence to back this up on the MySQL documentation website pages I have looked at so far. I have also just tried an optimise run against my own innoDB based database tables with no problems whatsoever.

The one thing it did suggest however is that you really only need to optimise when the database starts to get big although defining big is the same as the length of a piece of string. To quote:

Once your data reaches a stable size, or a growing table has increased by tens or some hundreds of megabytes, consider using the OPTIMIZE TABLE statement to reorganize the table and compact any wasted space. The reorganized tables require less disk I/O to perform full table scans.

so - if you are getting an error back from an optimise attempt I think i would be asking your host why that is to be honest - as it appears there should be no issue doing so.

YELLOW

SWORDFISH

kvr28

Member

Free Members

Offline

Dec 9, 2014 - 1:58 pm

I will pose the question, what version of sql are you using? we have 5.5.4

Yellow Swordfish

Glinton, England

SP Master

Offline