List:General Discussion« Previous MessageNext Message »
From:Brian Dunning Date:October 19 2011 7:16pm
Subject:Inefficient query is melting my server!!
View as plain text  
Can someone tell me if what I'm trying to do can be done more efficiently? I just got off
the phone with Rackspace when my server was hung up, and they found a whole bunch of this
one same query was all stacked up with waiting queries and locked.

Here's the query:

$query = "insert ignore into stats (ip,id,type,date,time) values
('".$_SERVER['REMOTE_ADDR']."','$id','listen',NOW(),NOW())";

Here's the table:

CREATE TABLE IF NOT EXISTS `stats` (
  `ip` varchar(20) NOT NULL default '',
  `id` mediumint(9) NOT NULL default '0',
  `type` varchar(20) NOT NULL default '',
  `date` date NOT NULL default '0000-00-00',
  `time` time NOT NULL default '00:00:00',
  PRIMARY KEY  (`ip`,`id`,`type`),
  KEY `report` (`id`,`date`),
  KEY `type_date` (`type`,`date`),
  KEY `id_type` (`id`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

There are about 16.5 million records in the table.

Thanks!

- Brian
Thread
Inefficient query is melting my server!!Brian Dunning19 Oct
  • Re: Inefficient query is melting my server!!Johan De Meersman19 Oct
    • Re: Inefficient query is melting my server!!Basil Daoust20 Oct