List:General Discussion« Previous MessageNext Message »
From:Mike Spreitzer Date:August 11 2010 5:28pm
Subject:Re: idle query
View as plain text  
I finally started trying to optimize along the memory-based lines you 
suggested.  I am surprised to find that the query plan is to enumerate the 
memory-based table and then pick out the hundreds of related rows from the 
much larger MyISAM table.  What's going on here?

`show create table` says this about the relevant tables:

CREATE TABLE `fldsndm` (
  `p` varchar(200) NOT NULL,
  `cd` datetime NOT NULL,
  `cms` smallint(6) NOT NULL,
  `pip` char(15) NOT NULL,
  `pport` smallint(6) NOT NULL,
  `pboot` bigint(20) NOT NULL,
  `msgid` bigint(20) NOT NULL,
  `startgtime` bigint(20) NOT NULL,
  `datalen` int(11) NOT NULL,
  `toself` tinyint(1) DEFAULT NULL,
  `sepoch` bigint(20) NOT NULL DEFAULT '0',
  `c` decimal(11,3) NOT NULL DEFAULT '0.000',
  UNIQUE KEY `pbm` (`p`,`pboot`,`msgid`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1

CREATE TABLE `fldrcv` (
  `p` varchar(200) NOT NULL,
  `cd` datetime NOT NULL,
  `cms` smallint(6) NOT NULL,
  `pip` char(15) NOT NULL,
  `pport` smallint(6) NOT NULL,
  `pboot` bigint(20) DEFAULT NULL,
  `qip` char(15) NOT NULL,
  `qport` smallint(6) NOT NULL,
  `qboot` bigint(20) DEFAULT NULL,
  `msgid` bigint(20) NOT NULL,
  `startgtime` bigint(20) NOT NULL,
  `datalen` int(11) NOT NULL,
  `q` varchar(200) DEFAULT NULL,
  `repoch` bigint(20) NOT NULL DEFAULT '0',
  `c` decimal(11,3) NOT NULL DEFAULT '0.000',
  KEY `c` (`c`),
  KEY `pec` (`p`,`repoch`,`c`),
  KEY `peqms` (`p`,`repoch`,`q`,`msgid`,`startgtime`),
  KEY `qbm` (`q`,`qboot`,`msgid`),
  KEY `pbm` (`p`,`pboot`,`msgid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

And here is the query planning I see:

mysql> explain select * from fldrcv join fldsndm on fldrcv.q=fldsndm.p AND 
fldrcv.qboot=fldsndm.pboot and fldrcv.msgid=fldsndm.msgid;
+----+-------------+---------+------+---------------+------+---------+---------------------------------------------------------------------------------------------+-------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref 
              | rows  | Extra       |
+----+-------------+---------+------+---------------+------+---------+---------------------------------------------------------------------------------------------+-------+-------------+
|  1 | SIMPLE      | fldsndm | ALL  | pbm           | NULL | NULL    | 
NULL                  | 29036 |             | 
|  1 | SIMPLE      | fldrcv  | ref  | qbm           | qbm  | 220     | 
bigCell2906_flood.fldsndm.p,bigCell2906_flood.fldsndm.pboot,bigCell2906_flood.fldsndm.msgid

|   452 | Using where | 
+----+-------------+---------+------+---------------+------+---------+---------------------------------------------------------------------------------------------+-------+-------------+

BTW, here are the table sizes:

mysql> select count(*) from fldrcv;
+----------+
| count(*) |
+----------+
| 13785373 | 
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from fldsndm;
+----------+
| count(*) |
+----------+
|    29036 | 
+----------+

Thanks,
Mike Spreitzer

Thread
idle queryMike Spreitzer27 Jul
  • Re: idle queryDan Nelson27 Jul
    • Re: idle queryMike Spreitzer27 Jul
      • Re: idle queryDan Nelson27 Jul
        • Re: idle queryMike Spreitzer27 Jul
          • Re: idle queryDan Nelson27 Jul
            • Re: idle queryMike Spreitzer27 Jul
              • Re: idle queryDan Nelson27 Jul
            • Re: idle queryDan Nelson27 Jul
              • Re: idle queryMike Spreitzer28 Jul
    • RE: idle queryJerry Schwartz27 Jul
    • Re: idle queryMike Spreitzer11 Aug
      • STRAIGHT JOIN vs. field namesMike Spreitzer11 Aug
        • Re: STRAIGHT JOIN vs. field namesMichael Dykman11 Aug
          • Re: STRAIGHT JOIN vs. field namesMike Spreitzer11 Aug
      • Re: idle queryMike Spreitzer12 Aug
        • Re: idle queryMySQL)18 Aug