Magic ETL

Magic ETL

Creating indexes in mysql data flows

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.

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Best Answer

  • 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

  • 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`)

  • 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

  • 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?

  • 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). 

  • 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`)

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In