Hello,
I'm trying to optimize a table but not sure what more I can do. I have a
table like the following:
CREATE TABLE `outgoing` (
`rpath` varchar(80) default NULL,
`auth` varchar(80) NOT NULL default '',
`ip` varchar(80) default NULL,
`hfrom` varchar(80) default NULL,
`hto` varchar(80) default NULL,
`subject` varchar(80) default NULL,
`messageid` varchar(80) default NULL,
`timestamp` timestamp(14) NOT NULL,
`rcpts` smallint(5) unsigned default '0',
KEY `at_ind` (`auth`,`timestamp`,`rcpts`)
) TYPE=MyISAM
CREATE TABLE `exempt_users` (
`auth` varchar(80) NOT NULL default '',
`comment` varchar(80) default NULL,
`timestamp` timestamp(14) NOT NULL,
PRIMARY KEY (`auth`)
) TYPE=MyISAM
Now on these tables I am doing the following query:
select count(outgoing.auth),sum(outgoing.rcpts) from outgoing
left join exempt_users on outgoing.auth=exempt_users.auth where
exempt_users.auth is null and outgoing.auth = 'USER'
and timestamp >= (now() - interval 1 day)
USER will be varying. I thought the indexes I have would be enough but I'm
noticing from 'show status':
| Select_full_join | 606616 |
And it is rising. This is the only query I am doing on the database. The MySQL
docs say this should be 0. Is there something wrong my design or is there
anything I can do to improve the speed?
Thanks,
Viraj.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
