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
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 278 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.7K Transform
- 89 SQL DataFlows
- 557 Datasets
- 2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 571 Beast Mode
- 11 App Studio
- 28 Variables
- 579 Automate
- 141 Apps
- 414 APIs & Domo Developer
- 23 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 352 Distribute
- 92 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 92 Manage
- 89 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 88 Community Announcements
- 4.8K Archive