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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 755 Beast Mode
- 61 App Studio
- 41 Variables
- 693 Automate
- 178 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive