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
-
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
- 7.3K All Categories
- 13 Getting Started in the Community
- 141 Beastmode & Analytics
- 1.8K Data Platform & Data Science
- 54 Domo Everywhere
- 2K Charting
- 1K Ideas Exchange
- 902 Connectors
- 236 Workbench
- 342 APIs
- 77 Apps
- 19 Governance & Productivity
- 234 Use Cases & Best Practices
- 50 News
- 473 Onboarding
- 572 日本支部