How to edit UPDATE via SELECT

Question

I have this structure of my db:

CREATE TABLE IF NOT EXISTS `peoples` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; 

For clients.

 CREATE TABLE IF NOT EXISTS `peoplesaddresses` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `people_id` int(10) unsigned NOT NULL, `phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `address` varchar(100) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; 

For your addresses.

 CREATE TABLE IF NOT EXISTS `peoplesphones` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `people_id` int(10) unsigned NOT NULL, `phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `address` varchar(100) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; 

For their phones.

UPD4

 ALTER TABLE peoplesaddresses DISABLE KEYS; ALTER TABLE peoplesphones DISABLE KEYS; ALTER TABLE peoplesaddresses ADD INDEX i_phone (phone); ALTER TABLE peoplesphones ADD INDEX i_phone (phone); ALTER TABLE peoplesaddresses ADD INDEX i_address (address); ALTER TABLE peoplesphones ADD INDEX i_address (address); ALTER TABLE peoplesaddresses ENABLE KEYS; ALTER TABLE peoplesphones ENABLE KEYS; 

END UPD4

 CREATE TABLE IF NOT EXISTS `order` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `people_id` int(10) unsigned NOT NULL, `name` varchar(255) CHARACTER SET utf8 NOT NULL, `phone` varchar(255) CHARACTER SET utf8 NOT NULL, `adress` varchar(255) CHARACTER SET utf8 NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ; INSERT INTO `order` (`id`, `people_id`, `name`, `phone`, `adress`) VALUES (1, 0, 'name1', 'phone1', 'address1'), (2, 0, 'name1_1', 'phone1', 'address1_1'), (3, 0, 'name1_1', 'phone1', 'address1_2'), (4, 0, 'name2', 'phone2', 'address2'), (5, 0, 'name2_1', 'phone2', 'address2_1'), (6, 0, 'name3', 'phone3', 'address3'), (7, 0, 'name4', 'phone4', 'address4'), (8, 0, 'name1_1', 'phone5', 'address1_1'), (9, 0, 'name1_1', 'phone5', 'address1_2'), (11, 0, 'name1', 'phone1', 'address1'), (10, 0, 'name1', 'phone1', 'address1'); 

The production base has over 9000 entries. Is there any way to execute this request 3 updates faster than now (~ 50 minutes on dev machine).

 INSERT INTO peoplesphones( phone, address ) SELECT DISTINCT `order`.phone, `order`.adress FROM `order` GROUP BY `order`.phone; 

Fill in the table of phones with unique phones

 INSERT INTO peoplesaddresses( phone, address ) SELECT DISTINCT `order`.phone, `order`.adress FROM `order` GROUP BY `order`.adress; 

Fill in the table peopleaddresses with a unique address. The following three queries are very slow:

 UPDATE peoplesaddresses, peoplesphones SET peoplesaddresses.people_id = peoplesphones.id WHERE peoplesaddresses.phone = peoplesphones.phone; UPDATE peoplesaddresses, peoplesphones SET peoplesphones.people_id = peoplesaddresses.people_id WHERE peoplesaddresses.address = peoplesphones.address; UPDATE `order`, `peoplesphones` SET `order`.people_id = `peoplesphones`.people_id where `order`.phone = `peoplesphones`.phone; 

Finally, fill out the people table and clear the unnecessary fields.

 INSERT INTO peoples( id, name ) SELECT DISTINCT `order`.people_id, `order`.name FROM `order` GROUP BY `order`.people_id; ALTER TABLE `peoplesphones` DROP `address`; ALTER TABLE `peoplesaddresses` DROP `phone`; 

So again: how can I make these UPDATE queries a little faster? thanks.

UPD: I do not want to say: I need to do this right away, just to transfer phones and addresses to other tables, since one person can have more than one phone and can order pizza not only at home.

UPD2:

UPD3:

Replace slow update requests with this one (without) get nothing.

 UPDATE peoplesaddresses LEFT JOIN peoplesphones ON peoplesaddresses.phone = peoplesphones.phone SET peoplesaddresses.people_id = peoplesphones.id; UPDATE peoplesphones LEFT JOIN `peoplesaddresses` ON `peoplesaddresses`.address = `peoplesphones`.address SET `peoplesphones`.people_id = `peoplesaddresses`.people_id; UPDATE `order` LEFT JOIN `peoplesphones` ON `order`.phone = `peoplesphones`.phone SET `order`.people_id = `peoplesphones`.people_id; 

UPD4 After adding the code at the top (upd4), the script takes a few seconds to execute. But on the request ~ 6.5k it ends with the text: "The system cannot find the specified drive."

Thanks to everyone. Especially for xQbert and Brent Baisley.

Answer 1

50 minutes for 9000 records is a bit ridiculous, an event without indexes. You can also place 9000 records in Excel and do what you need to do. I think there is something else with your dev machine. Perhaps you have mysql configured to use very small memory? Perhaps you can post the results of this "query":

 show variables like "%size%"; 

Just this morning I did an insert (ignore) / select 2 tables (one into the other), both with over 400,000 entries. 126,000 entries were inserted into the second table, which took a total of 2 minutes 13 seconds.

I would say put indexes on any of the fields you join or group in, but this seems like a one-time job. I do not think that the disadvantage of indexes is your problem.

Answer 2

• All write operations are slow in relational databases. In particular, indices make them slow since they need to be recalculated.
• If you use WHERE in your statements, you must put the index in the specified fields.
• GROUP BY always very slow, and therefore DISTINCT , as they must perform many checks that do not scale linearly. Always avoid them.

You may like to choose a different database engine for what you are doing. 9000 records in 50 minutes are very slow. Experiment with several different engines, such as MyISAM and InnoDB. If you use temporary tables a lot, MEMORY is really fast for them.

Refresh . In addition, updating multiple tables in one of the statements should probably not be performed.