Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
$ mysql dbnam -e' optimize table theTable'
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| dbnam.theTable | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| dbnam.theTable | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+

Just called optimize on a table in MariaDB, and got this disturbing message about how the db did a recreate for me.

If I have other tables that have Foreign Keys pointing at this one, are they still doing the right thing? How can I verify this?

share|improve this question
1  
The MySQL documentation says exactly that, InnoDB tables (and XtraDB is a variation of InnoDB) do not support OPTIMIZE and do this instead (recreate + analyze.) Your FKs are fine but with a big table, you may see some locking time while the table is recreated. –  ypercube Sep 12 '13 at 8:50
    
I guess I'm looking for some reassurance that the "recreate" is not the sequence of operations that I as a DBA could do, i.e. 1) drop table 2) create table 3) insert data 4) generate outbound FKs which would leave the inbound FKs in limbo. The docs don't explicitly state that this is a "safe" recreate. –  djsadinoff Sep 12 '13 at 8:58
    
I think it's an ALTER TABLE and it wouldn't hurt to check FKs when it is finished. –  ypercube Sep 12 '13 at 9:01
    
If the child table's FK's were messed up, how could I tell? –  djsadinoff Sep 12 '13 at 10:04
    
SELECT c.* FROM child c WHERE NOT EXISTS (SELECT 1 FROM parent p WHERE p.parent_id = c.parent_id); This should return 0 rows. –  ypercube Sep 12 '13 at 10:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.