Creating indexes in mysql data flows

user001977
user001977 Member
in Magic ETL

When I'm working on a mysql data flow, and create an explicit index on a table such as...

CREATE INDEX mr_table_mti_index ON mr_table(mr_table_id);

Do I then need to include an "ANALYZE TABLE mr_table;" transform or does mysql analyze the table automatically as part of declaring the index?

I've done some tests and it looks like I don't need to include the "ANALYZE TABLE mr_table;" transform, but I would like confirmation because I didn't think that was how indexes work in mysql.

Best Answer

  • zcameron
    zcameron Domo Employee
    Answer ✓

    No, you don't need to run the ANALYZE command.

     

    Alternatively, you can also use the ALTER TABLE syntax like below:

     

    ALTER TABLE mr_table ADD INDEX(`mr_table_id`)

Answers

  • zcameron
    zcameron Domo Employee
    Answer ✓

    No, you don't need to run the ANALYZE command.

     

    Alternatively, you can also use the ALTER TABLE syntax like below:

     

    ALTER TABLE mr_table ADD INDEX(`mr_table_id`)

  • Subir
    Subir Member

    1.Add your datasets

    2.Trans1-you can create a procedure in transformation with no output file

     Create Procedure AddIndexes

    Begin

    --add indexes in the below sample, wherever you required joins with other table.

    ALTER TABLE t1 ADD INDEX('t1.COLUMN') 

    ALTER TABLE t2 ADD iNDEX(t2.COLUMN) 

    End

    3. Trans2-- Call the SP, like CALL AddIndexes, with no output file.

    4.Trans3- Write a select query with joins (where t1.Column=t2.Column), with output file

    5.Output dataset- just write "select * from output-file"

     

    Regards,

     

    Subir

  • sdr
    sdr Member

    Can you put more than one ALTER TABLE commands in a single transform without using the create/call multi transform process as you describe it?

  • zcameron
    zcameron Domo Employee

    No, you can't do more than one ALTER TABLE command in the same transform unless it's inside of a stored procedure (create/call process). 

  • JasonWebber
    JasonWebber Member

    Yes you can. Below is an example. It only works on a single table though, whereas the create procedure version can index multiple tables.

     

    alter table mkto_activity_fill_out_form_extract
    add index(`mktgAssetName`),
    add index(`referrer url`),
    add index(`form fields`)

Categories