mysql bulk table script execution

Question

I have 120 tables in my project.

Now I have to migrate MSSQL to MySQL.

So I did all Queries to create those tables that are already worked.

Now my problem is when I execute this script in MSSQL it completes within a second.

But MySQL takes around 4 min to complete its execution.

I want to improve my performance in MySQL. But I don't know how to do that if anyone knows please help me.

Thank you

Here is my sample table Script

MySQL

CREATE TABLE `rb_tbl_bak` (
  `BakPathId` int NOT NULL AUTO_INCREMENT,
  `BakPath` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `BakDate` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`BakPathId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

MSSQL

--Create table and its columns
CREATE TABLE [dbo].[RB_Tbl_Bak] (
    [BakPathId] [int] NOT NULL IDENTITY (1, 1),
    [BakPath] [nvarchar](500) NULL,
    [BakDate] [datetime] NULL);
GO

like this way, I have to complete for 120+ tables

Answer 1

Oh well, In this case, MySQL databases take time. ?

You can turn on profiling to get an idea of what takes so long. An example is given using Mysql's CLI:-

SET profiling = 1;
CREATE TABLE rb_tbl_back (id BIGINT UNSIGNED NOT NULL PRIMARY KEY);
SET profiling = 1;

You should get a response like this:-

mysql> SHOW PROFILES;
| Query_ID | Duration   | Query |
+----------+------------+-------------------------------------------------------------+
|        1 | 0.00913800 | CREATE TABLE rb_tbl_back (id BIGINT UNSIGNED NOT NULL PRIMARY KEY) |
+----------+------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000071 |
| checking permissions | 0.000007 |
| Opening tables       | 0.001698 |
| System lock          | 0.000043 |
| creating table       | 0.007260 |
| After create         | 0.000004 |
| query end            | 0.000004 |
| closing tables       | 0.000015 |
| freeing items        | 0.000031 |
| logging slow query   | 0.000002 |
| cleaning up          | 0.000003 |
+----------------------+----------+
11 rows in set (0.00 sec)

If you read the profiling documentation, there are other flags for showing the profile of the query CPU, BLOCK IO, etc that might help you on the 'creating table' stage.

I got this answer from here

Thanks, HaPpY Coding ?