#1004 (ok 4.2) Create indexes at the end in SQL export

[guvi77]

It will be nice to make an optimized export , so when i'll export a database the indexes of the tables to be created to the bottom of the file (after the tables creations and data inserts/update/replace). In this way when i import the database the time spent will be much smaller.
I saw that a solution for myisam tables is to disable keys and after inserts to enable keys, but for innodb tables i did't find other solutions.

sorry for my poor english.

the export file structure:

1. create tables without indexes
2. insert data
3. create indexes

ps. i spent 4h to import a 30Mb sql file on windows...:((;

thank you.

[Marc Delisle]

Logged In: YES
user_id=210714
Originator: NO

From the manual http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
"ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes."

So for MyISAM, with this option, INSERT still updates unique indexes. We could offer this option on the export interface for MyISAM tables, but I fear that for InnoDB, your other solution of creating indexes only at the end could cause problems with duplicate keys. Or we just display the duplicate key errors?

Note to developers: too bad SHOW CREATE TABLE has no option to avoid generating the indexes.

[Michal Čihař]

Logged In: YES
user_id=192186
Originator: NO

But we might use same hack as we do for foreign keys?

[Viduranga Wijesooriya]

do you have any documentation for the foreign key hack or else that ticket and commit links ?
thanks

[Marc Delisle]

The logic is explained in my message of 2008-09-03 below: collect the KEY information from the CREATE TABLE and apply it later.

[Viduranga Wijesooriya]

'add later' or 'remove first and then add again' because I saw 'drop foreign key' command in the code base

[Marc Delisle]

Could you clarify your question?

[Viduranga Wijesooriya]

It's ok. I got it figured out. There was a 'drop foreign key function. I think that was used to remove the foreign constraints when dumping data. Anyway it's not related to this task

I'm near to implementing the feature. Shall I also do that for primary keys as well as for indexes?

Thanks

[Marc Delisle]

Yes, all indexes.

[Viduranga Wijesooriya]

I have created a pull request. Can you check ?
https://github.com/phpmyadmin/phpmyadmin/pull/841

[Marc Delisle]

Logged In: YES
user_id=210714
Originator: NO

You mean collect the KEY information from the CREATE TABLE and apply it later? Yes it might work.

[Viduranga Wijesooriya]

I will take the responsibility to do this

[Viduranga Wijesooriya]

I think you can close this ticket now

[Marc Delisle]

Thanks for reminding me. I'll mark it as being resolved (we close only when a version is released containing the code).

[Moises]

It would be great if this optimized export could be selected as an option in Export screen, because sometimes it's needed to export tables schemas the old way so they can be easily compared with a Database comparision tool.
This way, in 4.2, always indexes are out of the structure of the table and hard to compare tables.