CODEWITHSUNDEEP
mysqlsql
Javlon Tulkinov
Javlon Tulkinov

Reputation: 598

Joining 100GB tables on Mysql

I have 2 tables with 100gb data.

First table

+-------------+---------------+
| id          | code          |
+-------------+---------------+
| 10255911    |sample data    |
| 10255912    |sample data    |
| 10255913    |sample data    |
+-------------+---------------+

Second table

+-------------+---------------+
| id          | code2         |
+-------------+---------------+
| 10255911    |sample data    |
| 10255912    |sample data    |
| 10255913    |sample data    |
+-------------+---------------+

I need the fastest and optimal way to join tables for creating view. With function UNION ALL very slooow. Table (VIEW) should be

+-------------+---------------+-------------+
| id          | code          | code2       |
+-------------+---------------+-------------+
| 10255911    |sample data    |sample data  |
| 10255912    |sample data    |sample data  |
| 10255913    |sample data    |sample data  |
+-------------+---------------+-------------+

Upvotes: 0

Views: 405

Answers (2)

Alpesh Panchal
Alpesh Panchal

Reputation: 1713

With 100 GBs of two tables, I don't think join is advisable. To run join query, you will need highest CPU, MEMORY, optimum mysql configuration etc. on your developer machine.

So better you merge these two tables into one. With this advantage, you may have to change your php application code to a bit to insert data into new table accordingly.

e.g. 
+-------------+---------------+-------------+
| id          | code          | code2       |
+-------------+---------------+-------------+
| 10255911    |sample data    |sample data  |
| 10255912    |sample data    |sample data  |
| 10255913    |sample data    |sample data  |
+-------------+---------------+-------------+

and then create index on Id field. 

If code and code2 fields are going to be used into where clause,

create a index for each of them too.

Upvotes: 0

minatverma
minatverma

Reputation: 1099

CREATE INDEX on ID Column of both Tables.

SELECT table_1.id
    , table_1.code 
    , table_2.code2
FROM table_1 INNER JOIN table_2
  ON table_1.id = table_2.id
  ;

Upvotes: 1

Related Questions