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.
Best 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`)
2
Answers
-
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`)
2 -
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
0 -
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?
0 -
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).
0 -
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`)0
Categories
- 10.6K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 474 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 214 Visualize
- 259 Beast Mode
- 2.1K Charting
- 12 Variables
- 19 Automate
- 356 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 249 Distribute
- 65 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 189 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive