Ask a Question
Ask Question Login
Corporate Training
  1. Community
  2. SQL Server
  3. Question
SQL Server

How to speed up mysql update join query between 2 tables?

Asked by David Edmunds Mar 14, 2023 650 views 1 answer
Share

About this question

I have a powerful dedicated server.


Intel  I7-6700K -
64GB DDR4 2400 MHz
1x480GB   SSD
running mysql server along with nginx,php
innodb-ft-min-token-size = 1
innodb-ft-enable-stopword = 0
innodb_buffer_pool_size = 40G
max_connections = 2000
[deploy@ns540545 ~]$ free -h
              total        used        free      shared  buff/cache   available
Mem:            62G         45G         11G        107M        6.4G         16G
Swap:          2.0G        1.4G        640M
it was expensive so i got another dedicated server for cost cutting let's call it not-so powerful dedicated server
Intel  i3-2130  
8GB DDR3 1333 MHz   
2TB 
running mysql server along with nginx,php
innodb-ft-min-token-size = 1
innodb-ft-enable-stopword = 0
innodb_buffer_pool_size = 4G
max_connections = 2000
[root@privateserver deploy]# free -h
              total        used        free      shared  buff/cache   available
Mem:           7.7G        7.5G         73M         24M        150M         79M
Swap:           39G        7.8G         32G

I moved the database from a powerful server to a not-so-powerful server. I can feel slight performance degradation while running simple queries which is fine, but this one query which used to take 2 minutes on a powerful server now it takes around 26.6525 hours and counting on a not-so powerful server.


UPDATE content a JOIN peers_data b ON a.hash = b.hash SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated
More info about tables which are exactly same on both the dedicated server


CREATE TABLE `peers_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hash` char(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `seeders` int(11) NOT NULL DEFAULT '0',
  `leechers` int(11) NOT NULL DEFAULT '0',
  `is_updated` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`hash`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content_id` int(11) unsigned NOT NULL DEFAULT '0',
  `hash` char(40) CHARACTER SET ascii NOT NULL DEFAULT '',
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `tags` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `category` smallint(3) unsigned NOT NULL DEFAULT '0',
  `category_name` varchar(50) CHARACTER SET ascii COLLATE ascii_bin DEFAULT '',
  `sub_category` smallint(3) unsigned NOT NULL DEFAULT '0',
  `sub_category_name` varchar(50) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `size` bigint(20) unsigned NOT NULL DEFAULT '0',
  `seeders` int(11) unsigned NOT NULL DEFAULT '0',
  `leechers` int(11) unsigned NOT NULL DEFAULT '0',
  `upload_date` datetime DEFAULT NULL,
  `uploader` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
  `uploader_level` varchar(10) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
  `comments_count` int(11) unsigned NOT NULL DEFAULT '0',
  `is_updated` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`content_id`),
  UNIQUE KEY `unique` (`id`) USING BTREE,
  KEY `hash` (`hash`),
  KEY `uploader` (`uploader`),
  KEY `sub_category` (`sub_category`),
  KEY `category` (`category`),
  KEY `title_index` (`title`),
  KEY `category_sub_category` (`category`,`sub_category`),
  KEY `seeders` (`seeders`),
  KEY `uploader_sub_category` (`uploader`,`sub_category`),
  KEY `upload_date` (`upload_date`),
  KEY `uploader_upload_date` (`uploader`,`upload_date`),
  KEY `leechers` (`leechers`),
  KEY `size` (`size`),
  KEY `uploader_seeders` (`uploader`,`seeders`),
  KEY `uploader_size` (`uploader`,`size`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `tags` (`tags`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
mysql> explain UPDATE content a JOIN peers_data b ON a.hash = b.hash SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated ;
+----+-------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | UPDATE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 4236260 |   100.00 | NULL        |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 160     | func |       1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
2 rows in set (0.00 sec)
records in peers_data 6,367,417
records in content 4,236,268

How can i speed up the above update join query ? i was expecting about 1 hour on not-so powerful server, but 26 hours+ is too much.

what am I doing wrong ? or missing here ?

I have tried to compensate for RAM on a not-so-powerful server by setting 32 GB + swap space. Is innodb buffer pool 4 gb too much ?

Your answer

1 Answer

More SQL Server discussions

Learn & Explore

Free tutorials and interview questions from industry experts — learn the skill, then get ready to prove it.

Latest SQL Server Blogs

Guides, tips and career advice on SQL Server from JanBask experts.