Creating indexes in mysql data flows

user001977

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.

Find more posts tagged with

Accepted answers

zcameron

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

All comments

zcameron

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

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

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

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

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