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
