Help me speed up this MySQL query...

[jitspoe]

 UPDATE `dplogin_filehashlog` `hl` LEFT JOIN `dplogin_connectlog` `cl` ON `cl`.`id` = `hl`.`connectid` SET `flags` = (`flags` | 1) WHERE `playerid` = ####;


Query OK, 52026 rows affected (22 min 11.19 sec)
Rows matched: 52026  Changed: 52026  Warnings: 0

[IronFist]

I imagine you've already found something that works for you, but it would be easier to locate potential problems or rewrite queries if we had just the database schema that we could populate with our own dummy data.

e.g. http://snippets.dzone.com/posts/show/360

[f3l1x]

Left join and right join is the same. I think this is faster way..

[jitspoe]

Applicable tables:

Code: [Select]

--
-- Table structure for table `dplogin_filehashlog`
--

DROP TABLE IF EXISTS `dplogin_filehashlog`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `dplogin_filehashlog` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `connectid` int(10) unsigned NOT NULL,
  `filehash` int(10) unsigned NOT NULL,
  `filenameid` smallint(10) unsigned NOT NULL,
  `flags` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `filehash` (`filehash`),
  KEY `flags` (`flags`),
  KEY `filenameid` (`filenameid`)
) ENGINE=MyISAM AUTO_INCREMENT=71897491 DEFAULT CHARSET=latin1 COMMENT='Log of periodic file checks.';
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `dplogin_connectlog`
--

DROP TABLE IF EXISTS `dplogin_connectlog`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `dplogin_connectlog` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `playerid` int(10) unsigned NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `connectinfo` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `connectinfo` (`connectinfo`)
) ENGINE=MyISAM AUTO_INCREMENT=20486498 DEFAULT CHARSET=latin1 COMMENT='Date and connection info log';
SET character_set_client = @saved_cs_client;

Wow, didn't realize playerid wasn't indexed.  That's probably the biggest issue.

Edit: Adding an index didn't seem to help.

Query OK, 73433 rows affected (25 min 26.82 sec)
Rows matched: 73433  Changed: 73433  Warnings: 0

[f3l1x]

Try this:

ALTER TABLE dplogin_connectlog ADD KEY playerid (playerid);

[jitspoe]

Edit: Adding an index didn't seem to help.