Digital Paint Discussion Board

Development => General Development => Topic started by: jitspoe on March 05, 2010, 07:34:05 PM

Title: Help me speed up this MySQL query...
Post by: jitspoe on March 05, 2010, 07:34:05 PM

 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

Title: Re: Help me speed up this MySQL query...
Post by: IronFist on April 01, 2010, 09:09:48 AM

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

Title: Re: Help me speed up this MySQL query...
Post by: f3l1x on September 26, 2010, 03:50:03 PM

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

Title: Re: Help me speed up this MySQL query...
Post by: jitspoe on October 07, 2010, 06:03:34 PM

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

Title: Re: Help me speed up this MySQL query...
Post by: f3l1x on October 08, 2010, 08:10:35 AM

Try this:

ALTER TABLE dplogin_connectlog ADD KEY playerid (playerid);

Title: Re: Help me speed up this MySQL query...
Post by: jitspoe on November 05, 2010, 05:56:08 PM

Quote

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