netripper

CREATE TABLE IF NOT EXISTS `log` (
  `idx` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `when` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `how` VARCHAR(50) NOT NULL DEFAULT '',
  `where` VARCHAR(50) NOT NULL DEFAULT '',
  `who` VARCHAR(50) NOT NULL DEFAULT '',
  `what` text NOT NULL,
  PRIMARY KEY  (`idx`),
  KEY `where` (`where`),
  KEY `who` (`who`),
  KEY `how` (`how`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1255578 ;

-- table contains about 2.2mil records at the time of this pastebin

-- this query takes 6.65 sec
SELECT DATE(`when`) FROM log WHERE `where` LIKE '#htc-linux' ORDER BY `when` DESC LIMIT 1;

-- explain plan for this query
id  select_type     TABLE   TYPE    possible_keys   KEY     key_len     REF     ROWS    Extra
1   SIMPLE  log     range   WHERE   WHERE   152     NULL    687818  USING WHERE; USING filesort

-- this query takes 8.8 sec
SELECT DATE(`when`) FROM log WHERE `where` = '#htc-linux' ORDER BY `when` DESC LIMIT 1;

-- explain plan for this query
id  select_type     TABLE   TYPE    possible_keys   KEY     key_len     REF     ROWS    Extra
1   SIMPLE  log     REF     WHERE   WHERE   152     const   687838  USING WHERE; USING filesort

-- the query with = takes longer than the query with LIKE.. i also need case-insensitivity (well, currently, it's adjustable when i fix the script, but i see no point with those results)

-- my.cnf is not optimized (not manually anyway)
-- machine specs:
-- intel xeon quadcore 3ghz
-- 2gb ram
-- 2 disks in mirror raid

-- adding an index on when/who/where doesn't make a difference