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.

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

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