Updating large amount of data in mysql

Question

Updating large amount of data in mysql

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have a MyISAM table with more than 200,000 records.

From time to time I need to make a general update on the content. I create a new table and import ALL postings to the 'second' table in phpMyAdmin . So far so good. After that I run a script that renames the tables:

$sql = "RENAME TABLE primeira TO outra,segunda TO primeira;";

That's where the problem is, sometimes it hangs up and you have to restart MySql .

I thought about using alter table , but it seems to me that it's the same thing.

php mysql sql

asked by anonymous 28.11.2016 / 16:39

2 answers

How to use artisan make: migration in a subdirectory with Laravel 5 Adding Hours higher 24 h

score 1 · Answer 1

It seems to me that strategy would be to study exactly the changes in the data that you perform to be optimized without having to change 200,000 records at the same time. But with the information you've been given, perhaps the best approach is to create a view , transpose the data to the other table with the required changes, and change the base table to view . So you can query the data in view without major problems;

DROP VIEW IF EXISTS view_tabela;
CREATE VIEW view_tabela AS SELECT * FROM tabela_1;

... insere e atualiza na tabela_2

DROP VIEW IF EXISTS view_tabela;
CREATE VIEW view_tabela AS SELECT * FROM tabela_2;

score 0 · Answer 2

Hello! I believe you can solve this problem as follows. 1 - Create new table
2 - Import the data from Table 1 (old) to the newly created Table

INSERT INTO TABELA2 (COLUMN1, COLUMN2, ...) SELECT COLUMN1, COLUMN2,... FROM TABELA1 ; Home I hope I have helped in something. Home Att,

:)