show create table
CREATE TABLE `pageKeywords2` (
`id` int(11) NOT NULL auto_increment,
`urlID` int(11) default NULL,
`pageResultID` int(11) default NULL,
`reportNum` smallint(6) default NULL,
`keyword` varchar(100) default NULL,
`type` enum('high value','target','universe') default NULL,
`titleTag` smallint(6) default NULL,
`descriptionTag` smallint(6) default NULL,
`keywordTag` smallint(6) default NULL,
`h1Tag` smallint(6) default NULL,
`copy` smallint(6) default NULL,
`imgAltTitle` smallint(6) default NULL,
`linkText` smallint(6) default NULL,
`fileName` smallint(6) default NULL,
`dateAdded` datetime default NULL,
PRIMARY KEY (`id`),
KEY `keyword` (`keyword`)
) ENGINE=MyISAM AUTO_INCREMENT=19579962 DEFAULT CHARSET=latin1
show table status like
+---------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| pageKeywords2 | MyISAM | 10 | Dynamic | 1131065 | 51 | 57730556 | 281474976710655 | 15326208 | 0 | 19579962 | 2010-03-11 10:34:40 | 2010-03-11 10:34:45 | 2010-03-11 10:34:52 | latin1_swedish_ci | NULL | | |
+---------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
select
select pageKeywords2.keyword, pageKeywords2.type, sum(titleTag) + sum(descriptionTag) + sum(keywordTag) + sum(h1Tag) + sum(copy) + sum(imgAltTitle) + sum(linkText) as megaSum , reportGoogleKeyword.position, reportGoogleKeyword.siteUrl from reportGoogleKeyword, pageKeywords2 where pageKeywords2.dateAdded >= '2010-03-01 07:04:01' and pageKeywords2.dateAdded <= '2010-03-01 07:28:35' and reportGoogleKeyword.clientID = 110 and reportGoogleKeyword.dateAdded >= '2010-03-01 07:00:01' and reportGoogleKeyword.dateAdded <= '2010-03-01 07:34:15' and pageKeywords2.keyword = reportGoogleKeyword.keyword group by position, keyword order by megaSum desc, keyword, position
explain select
+----+-------------+---------------------+------+---------------+---------+---------+---------------------------------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+------+---------------+---------+---------+---------------------------------+---------+----------------------------------------------+
| 1 | SIMPLE | pageKeywords2 | ALL | keyword | NULL | NULL | NULL | 1131065 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | reportGoogleKeyword | ref | keyword | keyword | 103 | searcheye.pageKeywords2.keyword | 7 | Using where |
+----+-------------+---------------------+------+---------------+---------+---------+---------------------------------+---------+----------------------------------------------+
show variables like
+-------------------------------+-----------+| Variable_name | Value |+-------------------------------+-----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 134217728 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
+-------------------------------+-----------+
show create table for second table
CREATE TABLE `reportGoogleKeyword` (
`id` int(11) NOT NULL auto_increment,
`clientID` int(11) default NULL,
`keywordID` int(11) default NULL,
`keyword` varchar(100) default NULL,
`position` tinyint(4) default NULL,
`engineUrl` varchar(255) default NULL,
`title` varchar(255) default NULL,
`description` varchar(255) default NULL,
`siteUrl` varchar(255) default NULL,
`successful` tinyint(4) default NULL,
`dateAdded` datetime default NULL,
PRIMARY KEY (`id`),
KEY `keyword` (`keyword`)
) ENGINE=MyISAM AUTO_INCREMENT=43549 DEFAULT CHARSET=latin1
Show table status like for second table
+---------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| reportGoogleKeyword | MyISAM | 10 | Dynamic | 39510 | 261 | 10334796 | 281474976710655 | 994304 | 0 | 43549 | 2010-02-22 20:32:22 | 2010-03-11 15:52:20 | 2010-02-22 20:32:22 | latin1_swedish_ci | NULL | | |
+---------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+