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.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive